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;