How do you get nicely formatted results from an Oracle procedure that returns a reference cursor?

Question

In MS SQL Server if I want to check the results from a Stored procedure I might execute the following in Management Studio.

--SQL SERVER WAY
exec sp_GetQuestions('OMG Ponies')

The output in the results pane might look like this.

ID    Title                                             ViewCount   Votes 
----- ------------------------------------------------- ---------- --------
2165  Indexed View vs Indexes on Table                  491         2  
5068  SQL Server equivalent to Oracle’s NULLS FIRST     524         3 
1261  Benefits Of Using SQL Ordinal Position Notation?  377         2 

(3 row(s) affected)

No need to write loops or PRINT statements.

To do the same thing in Oracle I might execute the following anonymous block in SQL Developer

--ORACLE WAY
    DECLARE
        OUTPUT  MYPACKAGE.refcur_question;
        R_OUTPUT MYPACKAGE.r_question;
        USER    VARCHAR2(20);

BEGIN

  dbms_output.enable(10000000);
  USER:= 'OMG Ponies';
  recordCount := 0;



  MYPACKAGE.GETQUESTIONS(p_OUTPUT => OUTPUT, 
  p_USER=> USER, 

  ) ;




  DBMS_OUTPUT.PUT_LINE('ID |  Title | ViewCount | Votes' );

  LOOP 
    FETCH OUTPUT
    INTO R_OUTPUT;

         DBMS_OUTPUT.PUT_LINE(R_OUTPUT.QUESTIONID || '|' || R_OUTPUT.TITLE 
               '|' || R_OUTPUT.VIEWCOUNT '|' || R_OUTPUT.VOTES);
          recordCount := recordCount+1;




 EXIT WHEN OUTPUT % NOTFOUND;  
      END LOOP;
      DBMS_OUTPUT.PUT_LINE('Record Count:'||recordCount);
      CLOSE OUTPUT;


    END;

This outputs like

ID|Title|ViewCount|Votes 
2165|Indexed View vs Indexes on Table|491|2  
5068|SQL Server equivalent to Oracle’s NULLS FIRST|524|3 
1261|Benefits Of Using SQL Ordinal Position Notation?|377|2 
Record Count: 3

So the SQL version has 1 line and the oracle has 18 and the output is ugly. Its exacerbated if there are a lot of columns and/or the data is numeric.

What's odd to me about this is that if I write this statement in either SQL Developer or Management studio...

SELECT 
ID, 
Title, 
ViewCount, 
Votes
FROM votes where user = 'OMG Ponies'  

The results are fairly similar. This makes me feel like I'm either missing a technique or using the wrong tool.

Answer 1

If GetQuestions is a function returning a refcursor, which seems to be what you have in the SQL Server version, then rather you may be able to do something like this:

select * from table(MyPackage.GetQuestions('OMG Ponies'));

Or if you need it in a PL/SQL block then you can use the same select in a cursor.

You can also have the function produce the dbms_output statements instead so they're always available for debugging, although that adds a little overhead.

Edit

Hmmm, not sure it's possible to cast() the returned refcursor to a usable type, unless you're willing to declare your own type (and a table of that type) outside the package. You can do this though, just to dump the results:

create package mypackage as
    function getquestions(user in varchar2) return sys_refcursor;
end mypackage;
/

create package body mypackage as
    function getquestions(user in varchar2) return sys_refcursor as
        r sys_refcursor;
    begin
        open r for
            /* Whatever your real query is */
            select 'Row 1' col1, 'Value 1' col2 from dual
            union
            select 'Row 2', 'Value 2' from dual
            union
            select 'Row 3', 'Value 3' from dual;
            return r;
    end;
end mypackage;
/

var r refcursor;
exec :r := mypackage.getquestions('OMG Ponies');
print r;

And you can use the result of the call in another procedure or function; it's just getting to it outside PL/SQL that seems to be a little tricky.

Edited to add: With this approach, if it's a procedure you can do essentially the same thing:

var r refcursor;
exec mypackage.getquestions(:r, 'OMG Ponies');
print r;

Answer 2

SQL Developer automatically catches the output from running your stored procedures. Running the stored procedure directly from our procedure editor, you can see this behavior detailed in my post here

SQL Developer Tip: Viewing REFCURSOR Output

Now, if you want to run the refcursor as part of an anon block in our SQL Worksheet, you could do something similar to this

var rc refcursor
exec :rc := GET_EMPS(30)
print rc

--where GET_EMPS() would be your sp_GetQuestions('OMG Ponies') call. The PRINT command sends the output from the 'query' which is ran via the stored procedure, and looks like this:

anonymous block completed
RC
-----------------------------------------------------------------------------------------------------
EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DATE                 JOB_ID     SALARY     COMMISSION_PCT MANAGER_ID DEPARTMENT_ID 
----------- -------------------- ------------------------- ------------------------- -------------------- ------------------------- ---------- ---------- -------------- ---------- ------------- 
114         Den                  Raphaely                  DRAPHEAL                  515.127.4561         07-DEC-94 12.00.00        PU_MAN     11000                     100        30            
115         Alexander            Khoo                      AKHOO                     515.127.4562         18-MAY-95 12.00.00        PU_CLERK   3100                      114        30            
116         Shelli               Baida                     SBAIDA                    515.127.4563         24-DEC-97 12.00.00        PU_CLERK   2900                      114        30            
117         Sigal                Tobias                    STOBIAS                   515.127.4564         24-JUL-97 12.00.00        PU_CLERK   2800                      114        30            
118         Guy                  Himuro                    GHIMURO                   515.127.4565         15-NOV-98 12.00.00        PU_CLERK   2600                      114        30            
119         Karen                Colmenares                KCOLMENA                  515.127.4566         10-AUG-99 12.00.00        PU_CLERK   2500                      114        30            

Now, you said 10g. If you're in 12c, we have enhanced the PL/SQL engine to support implicit cursor results. So this gets a bit easier, no more setting up the cursor, you just make a call to get the data, as documented here: http://docs.oracle.com/database/121/DRDAA/migr_tools_feat.htm#DRDAA230

Answer 3

/*
    Create Sample Package in HR Schema
*/

CREATE OR REPLACE PACKAGE PRINT_REF_CURSOR
AS
    PROCEDURE SP_S_EMPLOYEES_BY_DEPT (
        p_DEPARTMENT_ID   IN  INTEGER,
        Out_Cur OUT SYS_REFCURSOR); 

END PRINT_REF_CURSOR;        

CREATE OR REPLACE PACKAGE BODY PRINT_REF_CURSOR
AS

    PROCEDURE SP_S_EMPLOYEES_BY_DEPT (
        p_DEPARTMENT_ID   IN  INTEGER,
        Out_Cur OUT SYS_REFCURSOR)
    AS 
    BEGIN
      OPEN Out_Cur FOR
           SELECT *
             FROM EMPLOYEES
             WHERE DEPARTMENT_ID = p_DEPARTMENT_ID;
    EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         DBMS_OUTPUT.Put_Line('SP_S_EMPLOYEES_BY_DEPT' || ',' || '-20000' || ',' );
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.Put_Line('SP_S_EMPLOYEES_BY_DEPT' || ',' || '-20001' || ',' );    
    END SP_S_EMPLOYEES_BY_DEPT;         

END PRINT_REF_CURSOR;    

/*
    Fetch values using Ref Cursor and display it in grid.
*/

var RC refcursor;

DECLARE 
    p_DEPARTMENT_ID NUMBER;
    OUT_CUR SYS_REFCURSOR;

BEGIN 
  p_DEPARTMENT_ID := 90;
  OUT_CUR := NULL;

  PRINT_REF_CURSOR.SP_S_EMPLOYEES_BY_DEPT ( p_DEPARTMENT_ID, OUT_CUR);
  :RC := OUT_CUR;

END;
/
PRINT RC;  
/************************************************************************/