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;
/

Squamation

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;

Hoogh

The problem is that the Stored Procedure returns a ref cursor as the result of a join between two tables. How to I reference a return type for a join table? – Squamation

You need to know what's in your cursor in terms of types and sizes in order to make sense of it. Define a record with the fields that are in your join, and fetch the rows into the record. – Hoogh

thanks @Hoogh but if I have to read only one column from every row then what I can do. Do I need to create the whole structure? – Carlenacarlene

You must create the record type to mirror what data you selected. – Hoogh

just a note, that you need to add this line on top of the declare in SQL Developer: SET SERVEROUTPUT ON – Toddler

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;

Tamper

Do you know how can I apply this same logic inside a procedure? i need it for a ETL tool (Pentaho Data Integration) – Mayotte

@YorfrankBastidas Did you look at the answer above - https://mcmap.net/q/387424/-pl-sql-print-out-ref-cursor-returned-by-a-stored-procedure? That discusses using a refcursor in PL/SQL code. – Tamper

Answer 3

If you want to print all the columns in your select clause you can go with the autoprint command.

CREATE OR REPLACE PROCEDURE sps_detail_dtest(v_refcur OUT sys_refcursor)
AS
BEGIN
  OPEN v_refcur FOR 'select * from dummy_table';
END;

SET autoprint on;

--calling the procedure
VAR vcur refcursor;
DECLARE 
BEGIN
  sps_detail_dtest(vrefcur=>:vcur);
END;

Hope this gives you an alternate solution

Bagwig

Answer 4

More easier option is to use DBMS_SQL.return_result();

Lets say your package / procedure / cursor spec is as below.

    create or replace PACKAGE my_package IS
    
    TYPE my_ref_cursor_type IS REF CURSOR;
    
    PROCEDURE my_procedure (
        p_in_param1     IN     VARCHAR2, 
        p_in_param2     IN     VARCHAR2, 
        p_in_param3     IN     VARCHAR2, 
        p_my_ref_cursor OUT    my_ref_cursor_type,
        p_err_code      OUT    NUMBER,
        p_err_msg       OUT    VARCHAR2   
        );
    
    END my_package;

Try this to invoke the procedure from your sql developer WORKSHEET

SET SERVEROUTPUT ON;

DECLARE

  P_MY_REF_CURSOR my_schema.my_package.my_ref_cursor_type;
  P_ERR_CODE NUMBER;
  P_ERR_MSG VARCHAR2(200);
  
BEGIN

  my_package.my_procedure(
    'VALUE1',
    'VALUE2',
    'VALUE3',
    P_MY_REF_CURSOR => P_MY_REF_CURSOR,
    P_ERR_CODE => P_ERR_CODE,
    P_ERR_MSG => P_ERR_MSG
  );
    DBMS_OUTPUT.PUT_LINE(P_ERR_MSG); 
    DBMS_OUTPUT.PUT_LINE(P_ERR_CODE); 
    DBMS_SQL.return_result(P_MY_REF_CURSOR);

END;

Hope this helps !

Rightness

Answer 5

There are many ways for displaying the sys_refcursor result set and one of them that is so easy is using SQL Developer to fetch sys_refcursor and print output which are:

1. Create a test function to print its result
2. Execute the function
3. View the output
4. Verify the result set

Protease