Skip to Main Content
  • Questions

  • Return resultset from a function that returns REFCURSOR

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Raj.

Asked: May 02, 2012 - 8:23 am UTC

Last updated: May 03, 2012 - 9:05 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Dear Tom,

We were given a function that returns a REFCURSOR. Function is in a package.

When I execute the following statement, I am getting entire resultset in a single row. How can I return a resultset in a grid in oracle sql developer.

SELECT mypackage.get_report('01-Apr-12') FROM dual;

Note: I dont have any permission to create temp table or function in the database. I have just read only access.

How can I return a result set from the above function?

thanks
RajExpert

and Tom said...

variable y refcursor
begin :y := mypackage.get_report; end;
/
print y



select that and run it as a block and you'll get a report of the ref cursor contents.


Alternatively, you can use this method:

http://www.thatjeffsmith.com/archive/2011/12/sql-developer-tip-viewing-refcursor-output/


In short - you would

a) find the function in the object tree
b) click on it so you can see it
c) hit the green "execute" arrow
d) then click on the output variables tab at the bottom



Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

RAJVJA, May 03, 2012 - 5:42 am UTC

Hi,

Thanks for the reply. But, its not useful.

DECLARE v_RETURN SYS_REFCURSOR;
BEGIN
v_RETURN := mylive.REPORT_PKG.get_report('01-Mar-12');
:v_RETURN := v_RETURN;

END;

I tried this code but I am getting error "Bind variable :v_RETURN" not declared.

The main purpose is I need to get this result set into my sql server. I want to do this from SSIS script task.
I can't see the actual function body as it is in the package and I dont have permission to see.

How can I return a resultset from the function.

thanks
Tom Kyte
May 03, 2012 - 9:05 am UTC

umm, I think perhaps you did not actually read what I wrote.

You do not write that block - you 1) read the link 2) do the a,b,c,d steps.


I have no idea what "SSIS" is or does.

I provided you TWO ways to do this - 

variable y refcursor 
begin :y := mypackage.get_report; end; 
/ 
print y


just put that in a script and run it, you'll get your output in the output window


or if you want the result set in a grid to interact with - use the a,b,c,d approach clearly listed above.

NO WHERE did anyone tell you to code the block you posted.

SQL Developer export to excel

A reader, May 11, 2012 - 2:31 am UTC

I am using sql developer 1.5.5 after executing following script the output is displayed in script out. There is no option to save this in excel sheet. How can i achieve it. Is it possible to write above as to execute above as execute statement and get results in grid.

print :ret_cursor
/

Excel Out Put

A reader, May 11, 2012 - 2:34 am UTC

Please note that i cant make function or procedure as there are multiple queries like these which will be executing.

Excel Out Put

A reader, May 11, 2012 - 2:35 am UTC

we are not allowed to create function. Have to handle these in queries
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library