How do you get nicely formatted results from an Oracle procedure that returns a reference cursor?
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.
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;
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