| Ref cursor results to CSV? [message #176809] |
Fri, 09 June 2006 14:29  |
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 #176812 is a reply to message #176811] |
Fri, 09 June 2006 14:54  |
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 #176817 is a reply to message #176815] |
Fri, 09 June 2006 16:57 |
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 #176862 is a reply to message #176861] |
Sat, 10 June 2006 12:11 |
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 #176864 is a reply to message #176863] |
Sat, 10 June 2006 12:58 |
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 |
William Robertson
Messages: 1643
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
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
