How to get well-formatted results from an Oracle procedure that returns a pointer reference?

Question

In MS SQL Server, if I want to check the results from a stored procedure, I can do the following in Management Studio.

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

The result in the results pane may 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 in Oracle, I could 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; 

It means that

 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 row, while oracle has 18, and the output is ugly. It is compounded if there are many columns and / or the data is numeric.

What is strange to me is that if I write this statement in SQL Developer or Management Studio ...

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

The results are pretty similar. It makes me feel like I either missed a technique or used the wrong tool.

Answer 1

If GetQuestions is a function that returns a refcursor, which is similar to what you have in the version of SQL Server, rather, you can do something like this:

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

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

You can also use the function for dbms_output , so they are always available for debugging, although this adds a bit of overhead.

Edit

Hmmm, I’m not sure that cast() returned refcursor to the type used, unless you want to declare your own type (and a table of this type) outside the package. You can do this, though, 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 just approaches it outside of PL / SQL, which seems a bit complicated.

Edited to add: With this approach, if this is a procedure, you can do almost the same thing:

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

Answer 2

SQL Developer automatically captures the output from the stored procedure run. Starting a stored procedure directly from our procedure editor, you can see this behavior in detail in my post here

SQL Developer Tip: View REFCURSOR Output

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

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

- where GET_EMPS () will be your call to sp_GetQuestions ("OMG Ponies"). The PRINT command sends the result from a query that is launched through a 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 are in 12c, we have improved the PL / SQL engine to support implicit cursor results. This way it gets a little easier, no longer adjusting the cursor, you just make a call to get the data as described 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; /************************************************************************/