PL/SQL print out ref cursor returned by a stored procedure

Question

How can I fetch from a ref cursor that is returned from a stored procedure (OUT variable) and print the resulting rows to STDOUT in SQL*PLUS?

ORACLE stored procedure:

PROCEDURE GetGrantListByPI(p_firstname IN VARCHAR2, p_lastname IN VARCHAR2, p_orderby IN VARCHAR2, p_cursor OUT grantcur); 

PL/SQL:

SET SERVEROUTPUT ON;  DECLARE   TYPE r_cursor IS REF CURSOR;   refCursor r_cursor;    CURSOR grantCursor IS     SELECT last_name, first_name     FROM ten_year_pis     WHERE year_added = 2010;    last_name VARCHAR2(100);   first_name VARCHAR2(100);  BEGIN   OPEN grantCursor;   FETCH grantCursor INTO last_name, first_name;    WHILE grantCursor%FOUND LOOP     PMAWEB_PKG.GetGrantListByPI(last_name, first_name, 'last_name', refCursor);      --HOW DO I LOOP THROUGH THE RETURNED REF CURSOR (refCursor)     --AND PRINT THE RESULTING ROWS TO STDOUT?      FETCH grantCursor into last_name, first_name;   END LOOP;   CLOSE grantCursor; END; / 

Answer 1

Note: This code is untested

Define a record for your refCursor return type, call it rec. For example:

TYPE MyRec IS RECORD (col1 VARCHAR2(10), col2 VARCHAR2(20), ...);  --define the record rec MyRec;        -- instantiate the record 

Once you have the refcursor returned from your procedure, you can add the following code where your comments are now:

LOOP   FETCH refCursor INTO rec;   EXIT WHEN refCursor%NOTFOUND;   dbms_output.put_line(rec.col1||','||rec.col2||','||...); END LOOP; 

Answer 2

You can use a bind variable at the SQLPlus level to do this. Of course you have little control over the formatting of the output.

VAR x REFCURSOR; EXEC GetGrantListByPI(args, :x); PRINT x;