Home » SQL & PL/SQL » SQL & PL/SQL » Ref cursor results to CSV?
Ref cursor results to CSV? [message #176809] Fri, 09 June 2006 14:29 Go to next message
hmoulding
Messages: 7
Registered: June 2006
Junior Member
I thought this ought to be fairly simple, but maybe I'm missing some key information.

I did manage to write a quickie procedure that goes a little like this:

declare 
output_cursor ref cursor ;
output_file utl_file.file_type ;
cursor_row cursor_view%rowtype ;
begin
  get_cursor_results(output_cursor) ;
  output_file := utl_file.fopen('FILE_DATA_DIR', 'output.csv') ;
  utl_file.put_line(output_file, '"HEADER1","HEADER2","HEADER3"') ;
  get_cursor_results(output_cursor) ; -- uses cursor_view internally
  loop
    fetch output_cursor into cursor_row ;
    exit when output_cursor%notfound ;
    utl_file.put_line(output_file, '"' || cursor_row.col1 || '","' || cursor_row.col2 || '","' || cursor_row.col3 || '"') ;
  end loop ;
  close output_cursor ;
  utl_file.fflush(output_file) ;
  utl_file.fclose(output_file) ;
end ;


It works fine, but it's all very specific to this one result set, and has several maintenance problems.

I'd like a more generic way of doing that, ideally something like:

declare output_cursor ref cursor ;
begin 
  get_cursor_results(output_cursor) ;
  write_cursor('file_name1.csv', output_cursor) ;
  get_some_other_results(output_cursor) ;
  write_cursor('file_name2.csv', output_cursor) ;
end ;


How would I go about writing a procedure like write_cursor?

[Updated on: Fri, 09 June 2006 14:37]

Report message to a moderator

Re: Ref cursor results to CSV? [message #176811 is a reply to message #176809] Fri, 09 June 2006 14:47 Go to previous messageGo to next message
BlackSwan
Messages: 25751
Registered: January 2009
Location: SoCal
Senior Member
stop reinventing the wheel.
visit http://asktom.oracle.com
do a keyword search on
csv output file
Make use of the code Tom has provided.
Re: Ref cursor results to CSV? [message #176812 is a reply to message #176811] Fri, 09 June 2006 14:54 Go to previous messageGo to next message
hmoulding
Messages: 7
Registered: June 2006
Junior Member
anacedent wrote on Fri, 09 June 2006 13:47

stop reinventing the wheel.
visit http://asktom.oracle.com
do a keyword search on
csv output file
Make use of the code Tom has provided.


I'd love to stop reinventing the wheel. I've posted here only after searching fruitlessly for some time. Tom has some posts that are similar to my question, but nothing that addresses this specific question that I was able to find. If you know of a post I've missed then please point me to it. Thanks.
Re: Ref cursor results to CSV? [message #176815 is a reply to message #176812] Fri, 09 June 2006 16:34 Go to previous messageGo to next message
Art Metzer
Messages: 2478
Registered: December 2002
Senior Member
It looks like what you want to do isn't possible.
Re: Ref cursor results to CSV? [message #176817 is a reply to message #176815] Fri, 09 June 2006 16:57 Go to previous messageGo to next message
hmoulding
Messages: 7
Registered: June 2006
Junior Member
Art Metzer wrote on Fri, 09 June 2006 15:34

It looks like what you want to do isn't possible.


Arrgh! Well, shoot. That was three years ago, and no one has come up with a way? And how do tools like Crystal Reports or PLSQL Developer do it? It's clearly not impossible, just apparently not possible with PLSQL three years ago. Maybe there's a compiled package that can do it?
Re: Ref cursor results to CSV? [message #176819 is a reply to message #176809] Fri, 09 June 2006 17:09 Go to previous messageGo to next message
BlackSwan
Messages: 25751
Registered: January 2009
Location: SoCal
Senior Member
"IT" is trivial - for cases where IT=(Table_name dumped to CSV file).
Re: Ref cursor results to CSV? [message #176820 is a reply to message #176819] Fri, 09 June 2006 17:23 Go to previous messageGo to next message
hmoulding
Messages: 7
Registered: June 2006
Junior Member
anacedent wrote on Fri, 09 June 2006 16:09

"IT" is trivial - for cases where IT=(Table_name dumped to CSV file).


Sure. But I'm not asking something trivial. I'm asking something that apparently 3 years ago Tom said wasn't possible. Wink

Now I'm hoping that the intervening 3 years have come up with something that makes it possible.
Re: Ref cursor results to CSV? [message #176861 is a reply to message #176820] Sat, 10 June 2006 11:39 Go to previous messageGo to next message
Art Metzer
Messages: 2478
Registered: December 2002
Senior Member
Nope, even now, three years later, a ref cursor still has no self-contained "intelligence" about its result set.

For that, you'd have to resort to JDBC or DBMS_SQL.
Re: Ref cursor results to CSV? [message #176862 is a reply to message #176861] Sat, 10 June 2006 12:11 Go to previous messageGo to next message
hmoulding
Messages: 7
Registered: June 2006
Junior Member
Art Metzer wrote on Sat, 10 June 2006 10:39

Nope, even now, three years later, a ref cursor still has no self-contained "intelligence" about its result set.

For that, you'd have to resort to JDBC or DBMS_SQL.


Well, shoot.

As far as I can tell there's no way for DBMS_SQL to do what I need, unless there's a way to associate a DBMS_SQL "cursor" with a ref cursor.

So how does a tool like Crystal Reports find out what's in a ref cursor? How can SQL Developer list out the contents of a ref cursor? Is there an Oracle API for ref cursors that's accessible via JDBC (I looked and didn't see anything) or C? If so why hasn't anyone written a PLSQL callable package using JDBC or C?
Re: Ref cursor results to CSV? [message #176863 is a reply to message #176809] Sat, 10 June 2006 12:36 Go to previous messageGo to next message
BlackSwan
Messages: 25751
Registered: January 2009
Location: SoCal
Senior Member
>If so why hasn't anyone written a PLSQL callable package using JDBC or C?
Nothing is impossible for the person who does not have to do it.
Re: Ref cursor results to CSV? [message #176864 is a reply to message #176863] Sat, 10 June 2006 12:58 Go to previous messageGo to next message
hmoulding
Messages: 7
Registered: June 2006
Junior Member
anacedent wrote on Sat, 10 June 2006 11:36

>If so why hasn't anyone written a PLSQL callable package using JDBC or C?
Nothing is impossible for the person who does not have to do it.


But it can't be impossible because, as I've pointed out repeatedly, Crystal Reports and PLSQL Developer can do it. That implies that there's at least an external API for that. It doesn't make sense to me that there should be an external API for this that's not available in some way to PLSQL. I'm sorry, but just pointing out that I'm ignorant on this matter, which I already know, isn't much help.
Re: Ref cursor results to CSV? [message #664576 is a reply to message #176864] Sun, 23 July 2017 07:54 Go to previous messageGo to next message
William Robertson
Messages: 1642
Registered: August 2003
Location: London, UK
Senior Member
Ancient post, but as it came up in the top Google results for "oracle cursor to csv" and posters were wondering if there were any new features whereby DBMS_SQL could process a ref cursor, the answer is that you need Oracle 11.1 (2007). I wrote a handly utility to use it:

select column_value
from   table(csv.report(cursor(
           select * from dept
       ), p_separator => '|', p_label => 'DEPT', p_rowcount => 'Y'));

COLUMN_VALUE
----------------------------------------------
DEPT|10|ACCOUNTING|NEW YORK
DEPT|20|RESEARCH|DALLAS
DEPT|30|SALES|CHICAGO
DEPT|40|OPERATIONS|BOSTON
ROW_COUNT|DEPT|4

5 rows selected.

http://www.williamrobertson.net/documents/refcursor-to-csv.shtml
Re: Ref cursor results to CSV? [message #664603 is a reply to message #664576] Mon, 24 July 2017 08:15 Go to previous messageGo to next message
Bill B
Messages: 1701
Registered: December 2004
Senior Member
Tom has a utility that will create a CSV file for any supplied query. See the following link. The name of the utility is DUMP_CSV

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:68212348056

cut and compile the code in the body of the message. I have used it and it works just fine

[Updated on: Mon, 24 July 2017 08:16]

Report message to a moderator

Re: Ref cursor results to CSV? [message #664607 is a reply to message #664603] Mon, 24 July 2017 08:39 Go to previous messageGo to next message
William Robertson
Messages: 1642
Registered: August 2003
Location: London, UK
Senior Member
True but as discussed earlier in this thread it only takes a dynamic string and not a ref cursor, because it was written before Oracle 11.1 made this possible.
icon14.gif  Re: Ref cursor results to CSV? [message #664614 is a reply to message #664576] Mon, 24 July 2017 12:42 Go to previous message
hmoulding
Messages: 7
Registered: June 2006
Junior Member
Thanks for that! Smile
Previous Topic: Weeks, Quarters, and Counts
Next Topic: How to find out Last Modification Time of a database?
Goto Forum:
  


Current Time: Sun Dec 17 08:58:20 CST 2017

Total time taken to generate the page: 0.00716 seconds