View results of refcursor out parameter

A lot of times, pl/sql developers may need to check the results of refcursor cursor 
which was passed in a stored procedure as a out parameter for the debugging purpose. 

Lets say we have following oracle stored procedure:

CREATE OR REPLACE PROCEDURE MYPROC (CR OUT SYS_REFCURSOR)
IS
BEGIN
 OPEN CR FOR 
  SELECT EMPNO, ENAME, DNAME, SAL 
  FROM SCOTT.EMP E, SCOTT.DEPT D 
  WHERE E.DEPTNO = D.DEPTNO ORDER BY SAL;
END;
/


Solution 1: The results of refcursor can be easily viewed by making an anonymous 
block as following

SQL> SET SERVEROUTPUT ON

SQL> DECLARE
  2     C SYS_REFCURSOR;
  3     ENAME VARCHAR(50);
  4     EMPNO NUMBER;
  5     DNAME VARCHAR2(50);
  6     SAL NUMBER;
  7  BEGIN
  8     MYPROC(C);
  9     LOOP
 10       FETCH C INTO EMPNO, ENAME, DNAME, SAL;
 11       EXIT WHEN C%NOTFOUND;
 12       DBMS_OUTPUT.PUT_LINE(EMPNO || ' ' || ENAME || ' ' || DNAME || ' ' || SAL);
 13     END LOOP;
 14  END;
 15  /
 
7934 MILLER OPERATIONS 0
7369 SMITH RESEARCH 800
7900 JAMES SALES 950
7876 ADAMS RESEARCH 1100
7521 WARD SALES 1250
7654 MARTIN SALES 1250
7844 TURNER SALES 1500
7499 ALLEN SALES 1600
7782 CLARK ACCOUNTING 2450
7698 BLAKE SALES 2850
7566 JONES RESEARCH 2975
7788 SCOTT RESEARCH 3000
7902 FORD RESEARCH 3000
7839 KING ACCOUNTING 5000

PL/SQL procedure successfully completed.


Solution 2: But the nice and simple way to get the output of refcursor returned 
by an oracle stored procedure is as following:

SQL> VAR C REFCURSOR

SQL> EXECUTE MYPROC(:C);

PL/SQL procedure successfully completed.

SQL> PRINT C

     EMPNO ENAME      DNAME                 SAL
---------- ---------- -------------- ----------
      7934 MILLER     OPERATIONS              0
      7369 SMITH      RESEARCH              800
      7900 JAMES      SALES                 950
      7876 ADAMS      RESEARCH             1100
      7521 WARD       SALES                1250
      7654 MARTIN     SALES                1250
      7844 TURNER     SALES                1500
      7499 ALLEN      SALES                1600
      7782 CLARK      ACCOUNTING           2450
      7698 BLAKE      SALES                2850
      7566 JONES      RESEARCH             2975
      7788 SCOTT      RESEARCH             3000
      7902 FORD       RESEARCH             3000
      7839 KING       ACCOUNTING           5000

14 rows selected.

Related Links
- Cursor with Parameters in Oracle
- Ref Cursor: Strongly Typed VS Weakly Typed
- Difference Between Cursor And Ref cursor
- ORA-01422: exact fetch returns more than requested number of rows
Get Results from REFCURSOR with unknown number of columns in PLSQL