[an error occurred while processing this directive]

Ref cursor to CSV converter

This is a utility for producing character-delimited output from SQL.

So SQL*Plus 12.2 has a set markup csv option, and SQL Developer has a /* csv */ magic comment, but here is how to do it if you don't have either of those, or can't use them in your batch process and need a pure PL/SQL solution.

Plug any SQL query into the cursor() expression using the table function below, and get back the results in delimited format (default is comma, but you can pass something different as p_separator).

  • The column list is printed as a heading if you pass p_heading => 'Y'. (Default is no heading.)
  • Each data row will be preceded by an optional label if you pass p_label => '<Label Text>'. (Default is no label.)
  • The row count is printed at the end if you pass p_rowcount => 'Y' (Default is no footer.)

Procedure write_file() writes the output to the specified file.

Examples:

select column_value
from   table(csv.report(cursor(
           select * from dept
       )));

COLUMN_VALUE
----------------------------------------------
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON

4 rows selected.

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.

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

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

6 rows selected.

Write the same results to a file named dept.csv:

declare
    l_dataset sys_refcursor;
begin
    open l_dataset for select * from dept;

    csv.write_file
    ( p_dataset => l_dataset
    , p_separator => '|', p_label => 'DEPT', p_heading => 'Y', p_rowcount => 'Y'
    , p_directory => 'DATA_FILE_DIR'
    , p_filename => 'dept.csv' );
end;

Limitations: Requires dbms_sql.to_cursor_number, added in Oracle 11.2. I have only supported the basic string, numeric and datetime types, and just for fun, rowid. (Timestamps are cast to dates.) I may see if I can add intervals, although sadly nobody will ever use those for table columns until Oracle get around to overloading the aggregate functions for them. CLOBs are supported internally, but the function returns a collection of varchar2(4000) which you would need to change if you needed to report longer lines. Then there are things like BLOBs, XMLTYPEs, VARRAYs and user-defined types that would probably take quite a bit more effort, and I am not sure it's worth it.

Dates are formatted YYYY-MM-DD if there is no significant time component (i.e. if the time is 00:00:00), or YYYY-MM-DD HH24:MI:SS if there is (formats are defined as private constants). If you need something different, either change the constants or use to_char() expressions in your SQL.

It'll break if you exceed the limitations of the SQL VARCHAR2 datatype, which is 4000 until Oracle 12.1 where it can be increased up to 32k by setting MAX_STRING_SIZE (though as this is a system-wide setting that isn't easily undone, I suspect few sites will use it).

Here's the code. (There's also a link to csv.pkg at the bottom of the page.)

[an error occurred while processing this directive]

Source code:  

Further reading: oracle-developer.net: method 4 dynamic sql in pl/sql