Getting Resultset of a sys_refcursor on PL/SQL

Question

I have a function that returns dynamic SQL Query sys_refcursor so columns are dynamically returned by this cursor. I want to query that Cursor's sql after executing my function

Select tryit('MyTable_Name',' condition = conditionParameter and  condition2=Parameter2') retCursor 
from dual

It returns a cursor on PL/SQL SQL Windw as a one column and one row. After clicking on three dots, it returns resultset. How can I get resultset without clicking any dots.

When I cast the function to xmltype and by passing its columns and casting as xmltable, it shows resultset but I need the columns to be created dynamically according to passed parameters.

Select * from  xmltable('/ROWSET/ROW'
PASSING xmltype(tryit('MyTable_Name','condition = conditionParameter and  condition2=Parameter2'))
columns
Col1  PATH 'Col1',
Col2  PATH 'Col2',
Col3  PATH 'Col3' ,
Col4  PATH 'Col4')

Note: Oracle 11g, PL/SQL Developer 8

Ref cursor is a pointer which is passed to a client application. The application is responsible for handling the result set. PL/SQL Developer is a developer's tool (the clue is in the name). So fetching and displaying the ref cursor requires a manual action. — APC, May 06 '16 at 07:32
[custom pipelined function](http://stackoverflow.com/questions/14155844/return-resultset-from-function) — Arkadiusz Łukasiewicz, May 06 '16 at 07:33

score 0 · Answer 1 · answered May 06 '16 at 08:38

In SQL Developer (and I assume there is something similar in PL/SQL Developer), you do not need the SQL query to wrap the function; instead you can run a PL/SQL script which will output the cursor:

VARIABLE cur REFCURSOR;

BEGIN
  :cur := tryit(
           'MyTable_Name',
           ' condition = conditionParameter and  condition2=Parameter2'
         );
END;
/

PRINT cur;

Getting Resultset of a sys_refcursor on PL/SQL

1 Answers1