In this tutorial, I am giving an example to return resultset from a stored procedure in Oracle using sys_refcursor.
Follow these steps to return resultset from a stored procedure in Oracle.
1. Create a Stored Procedure in Oracle
The following stored procedure (p_dept) will return department names in sys_refcursor (p_result) from the department table where the department number is less than or equal to the parameter value.
CREATE OR REPLACE PROCEDURE p_dept (p_deptno IN dept.deptno%TYPE, p_result OUT SYS_REFCURSOR) IS BEGIN OPEN p_result FOR SELECT dname FROM dept WHERE deptno <= p_deptno; END; /
2. Get The Returned ResultSet and Process
The following PL/SQL block will get the resultset from the stored procedure (p_dept) and will print the department names.
SET SERVEROUTPUT ON; DECLARE v_result SYS_REFCURSOR; v_dname VARCHAR2 (100); BEGIN /* call the procedure by passing department numbner and sys_refcursor for resultset */ p_dept (30, v_result); LOOP FETCH v_result INTO v_dname; EXIT WHEN v_result%NOTFOUND; DBMS_OUTPUT.put_line (v_dname); END LOOP; END; /
Output:
ACCOUNTING RESEARCH SALES PL/SQL procedure successfully completed.
See also:
- Create a PDF file using PL/SQL
- Get Current Local Time of any Country in PL/SQL
