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

User · Answer

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

User · Answer

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  gt  vcur   END    Hope this gives you an alternate solution

User · Answer

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

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

Examples related to oracle

Examples related to plsql

Examples related to cursor

Examples related to ref