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…

Accepted Answer

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

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

Solution

Problem