Returning a REF CURSOR

How to Return a REF CURSOR from a Procedure (PL/SQL)?

We have already seen How to Return a Table from a Function on the below Post.

Click here to Read How To Return Table From a Function?

Now, In this Post, we will Define and Open a REF CURSOR Variable and then pass it to a Stored Procedure.

The Cursor Variable is specified as an IN OUT parameter so that the result set is made available to the caller of the Procedure. Below procedure will Take Location as an Input and return Employees working under that location.

EMPLOYEE Table Data:

SQL> SELECT * From EMP_TEST;

    EMP_NO ENAME          DEPTNO HIREDATE  WORK_LOCATION
---------- ---------- ---------- --------- --------------------
      1001 RAVI               10 19-AUG-16 HYDERABAD
      1002 SURYA              20 26-DEC-16 KOCHI
      1003 ANKIT              30 12-DEC-16 HYDERABAD
      1004 NIKHIL             40 12-DEC-10 DELHI
      1005 PRITESH            50 19-AUG-16 HYDERABAD
      1006 RAJAN              20 16-AUG-10 DELHI
      1007 MANU               20 16-AUG-10 HYDERABAD
      1008 KARAN              20 16-AUG-10 KOLKATA
      1009 GAURAV             50 19-MAR-17 HYDERABAD
      1010 SHAHRUKH           40 11-MAR-17 KOCHI
      1011 KHAN               30 11-MAR-16 HYDERABAD

Procedure Definition:

SQL> CREATE OR REPLACE PROCEDURE EMPLOYEES_BY_LOCATION
  2  (V_LOC VARCHAR2, EMP_REF_CUR IN OUT SYS_REFCURSOR)
  3  IS
  4  BEGIN
  5  OPEN EMP_REF_CUR FOR SELECT EMP_NO, ENAME, HIREDATE FROM EMP_TEST
  6  WHERE WORK_LOCATION = V_LOC;
  7  END;
  8  /

Procedure created.

The EMPLOYEES_BY_LOCATION procedure is invoked in the following anonymous block by assigning the procedure's IN OUT parameter to a cursor variable that was declared in the anonymous block's Declaration section. The Result set is fetched using this cursor variable.

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2      V_EMPNO         EMP_TEST.EMP_NO%TYPE;
  3      V_ENAME         EMP_TEST.ENAME%TYPE;
  4      V_HDATE         EMP_TEST.HIREDATE%TYPE;
  5      V_WLOC          EMP_TEST.WORK_LOCATION%TYPE := 'HYDERABAD';
  6      V_EMP_REFCUR    SYS_REFCURSOR;
  7  BEGIN
  8      DBMS_OUTPUT.PUT_LINE('EMPLOYEES Working at ' || V_WLOC);
  9      DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME    HIREDATE');
 10      DBMS_OUTPUT.PUT_LINE('-----   --------  --------');
 11      EMPLOYEES_BY_LOCATION(V_WLOC,V_EMP_REFCUR);
 12      LOOP
 13          FETCH V_EMP_REFCUR INTO V_EMPNO,V_ENAME,V_HDATE;
 14          EXIT WHEN V_EMP_REFCUR%NOTFOUND;
 15          DBMS_OUTPUT.PUT_LINE(V_EMPNO||'     '||V_ENAME||'     '||V_HDATE);
 16      END LOOP;
 17      CLOSE V_EMP_REFCUR;
 18  END;

Following Output will be Displayed by Executing above PL/SQL Block.

EMPLOYEES Working at HYDERABAD
EMPNO    ENAME    HIREDATE
--------------  ---------------  
1001     RAVI     19-AUG-16
1003     ANKIT    12-DEC-16
1005     PRITESH  19-AUG-16
1007     MANU     16-AUG-10
1009     GAURAV   19-MAR-17
1011     KHAN     11-MAR-16