Skip to Main Content
  • Questions
  • How to export output of Stored Procedure returned in refcursor into csv file

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, chetan.

Asked: January 10, 2019 - 11:41 am UTC

Last updated: September 29, 2023 - 5:49 pm UTC

Version: 3.0.04

Viewed 10K+ times! This question is

You Asked

Dear Team,

I have created stored procedure which return output in refcursor.

If I run procedure by "right click => Run", then I can see output in tab "Log => output variables".

But, I can't export output from there.

Then I tried below command:
----------------------------
VARIABLE VAR_CUR SYS_REFCURSOR;
EXECUTE PROCEDURE_NAME('INPUT_PARAMETER', :VAR_CUR );
PRINT VAR_CUR;
----------------------------

With this command I got output in plain text but not in .csv / .xlsx friendly format.

Is there a way in "SQL Developer" Tool to execute the procedure right from editor (without "right click => Run") and export its output returned in refcursor into csv / xlsx file?

In "TOAD" tool, it is real simple. Below command gives output in grid and then we can just "Right click = > export":
--------------------
EXECUTE PROCEDURE_NAME('INPUT_PARAMETER', :VAR_CUR );


Please suggest.

and Connor said...

Sorry, as it currently stands the Function/Run/Output Variables workaround is your best bet. For other viewers new to this option, here's a quick video:



Head to the SQL Developer exchange - there's already been requests along these lines, make sure you stop by and vote them up!


Rating

  (2 ratings)

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

Comments

A reader, May 01, 2023 - 5:31 pm UTC

The answer you provide does not answer the question. I am looking for the same thing and it is 2023. Since 2019 you have been able to correctly answer the question you were asked? If so, do you have a link?
Connor McDonald
May 10, 2023 - 4:34 am UTC

So ... did you even try the process in the video? I mean, its 16 seconds long - was that such a big ask?

I did, and it even works with procedures now as well as function.

But hey... you do you.

George, September 29, 2023 - 2:36 pm UTC

No headers. :(

Yes I can copy from the out put variables log but I cannot get the headers with the data...seems kind of not well thought out. Sorry.
Chris Saxon
September 29, 2023 - 5:49 pm UTC

What exactly did you do? What are you expecting/hoping to happen?

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