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.)

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
----------------------------------------------
DEPTNO|DNAME|LOC
DEPT|10|ACCOUNTING|NEW YORK
DEPT|20|RESEARCH|DALLAS
DEPT|30|SALES|CHICAGO
DEPT|40|OPERATIONS|BOSTON
ROW_COUNT|4

6 rows selected.

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. Then there are things like BLOBs, CLOBs, 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.)

create or replace package csv as

    g_dflt_separator   varchar2(10) := ',';
    g_label_in_header  boolean := false;
    g_label_in_footer  boolean := false;

    -- Returns cursor results in character-delimited format with optional header, data label and rowcount.
    --
    --     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.

    function report
        ( p_dataset    in sys_refcursor
        , p_separator  in varchar2 default g_dflt_separator
        , p_label      in varchar2 default null
        , p_heading    in varchar2 default 'N'
        , p_rowcount   in varchar2 default 'N' )
        return sys.dbms_debug_vc2coll  -- generic string array: use your own if you prefer
        pipelined;

end csv;
/
create or replace package body csv as
    g_typenames        dbms_utility.name_array;
    g_supported_types  dbms_utility.name_array;

    k_format_dateonly  constant varchar2(10) := 'YYYY-MM-DD';
    k_format_datetime  constant varchar2(21) := 'YYYY-MM-DD HH24:MI:SS';

    k_heading_label     constant varchar2(50) := 'HEADING';
    k_footer_label      constant varchar2(50) := 'ROW_COUNT';

    -- Record for parsing a column's value:
    type column_data is record
        ( stringval    long
        , numval       number
        , dateval      date
        , rowidval     rowid );


    -- Utility for parsing ref cursor. Pass in ref cursor, get back DBMS_SQL cursor number, column list.
    procedure prepare_cursor
        ( p_dataset         in  sys_refcursor
        , p_cursor_id       out integer
        , p_cursor_columns  out dbms_sql.desc_tab )
    is
        l_extract_query   sys_refcursor := p_dataset;
        l_col_count       integer;

        l_dummy_number    number;
        l_dummy_date      date;
        l_dummy_string    varchar2(4000);
        l_dummy_rowid     rowid;
    begin
        p_cursor_id := dbms_sql.to_cursor_number(l_extract_query);
        dbms_sql.describe_columns(p_cursor_id, l_col_count, p_cursor_columns);

        -- Define columns to be selected from the cursor
        -- (third parameter passed to dbms_sql.define_column is just to provide datatype - no actual value is used here)
        for i in 1 .. p_cursor_columns.count loop
            begin
                if not g_supported_types.exists(p_cursor_columns(i).col_type) then
                    -- Datatype is known but will not work in this extract e.g. BLOB, BFILE:
                    raise_application_error(-20001, 'Unsupported datatype ' || g_typenames(p_cursor_columns(i).col_type) || ' for column ' || p_cursor_columns(i).col_name, false);
                elsif p_cursor_columns(i).col_type = dbms_types.typecode_number then
                    dbms_sql.define_column(p_cursor_id, i, l_dummy_number );
                elsif g_typenames(p_cursor_columns(i).col_type) like '%CHAR%' then
                    dbms_sql.define_column(p_cursor_id, i, l_dummy_string, 4000 );
                elsif g_typenames(p_cursor_columns(i).col_type) like '%ROWID' then
                    dbms_sql.define_column_rowid(p_cursor_id, i, l_dummy_rowid);
                elsif regexp_like(g_typenames(p_cursor_columns(i).col_type), '^(DATE|TIME)') then
                    dbms_sql.define_column(p_cursor_id, i, l_dummy_date );
                else
                    -- if l_typenames(l_cursor_columns(i).col_type) like '%CHAR%' then
                    -- Attempt default implicit string conversioon for anything else:
                    dbms_sql.define_column(p_cursor_id, i, l_dummy_string, 4000 );
                end if;
            exception
                when no_data_found then
                    -- Datatype is not in list - we should probably add it, but will need to test whether DBMS_SQL handles it:
                    raise_application_error(-20002, 'Unknown datatype ' || p_cursor_columns(i).col_type || ' for column ' || p_cursor_columns(i).col_name, false);
            end;
        end loop;
    end prepare_cursor;


    -- Close DBMS_SQL cursor by cursor ID, suppressing any 'invalid cursor' exception.
    procedure close_dbms_sql_cursor
        ( p_cursor_id in out integer )
    is
    begin
        if dbms_sql.is_open(p_cursor_id) then
            dbms_sql.close_cursor(p_cursor_id);
        end if;
    exception
        when invalid_cursor then null;
    end close_dbms_sql_cursor;


    -- Append one column value from dbms_sql.column_value to result according to its datatype:
    procedure append_result
        ( p_cursor_id   in pls_integer
        , p_colnum      in pls_integer
        , p_col_type    in pls_integer
        , p_separator   in varchar2 default g_dflt_separator
        , p_result_row  in out long )
    is
        k_typename constant varchar2(40) := g_typenames(p_col_type);
        l_column_values column_data;
    begin
        if k_typename like '%CHAR%' or k_typename like '%ROWID' then
            dbms_sql.column_value(p_cursor_id, p_colnum, l_column_values.stringval);
            p_result_row := p_result_row ||
                case
                    when l_column_values.stringval like '%'||p_separator||'%' then '"'||trim(l_column_values.stringval)||'"'
                    else trim(l_column_values.stringval)
                end;

        elsif k_typename = 'NUMBER' then
            dbms_sql.column_value(p_cursor_id, p_colnum, l_column_values.numval);
            p_result_row := p_result_row || rtrim(to_char(l_column_values.numval,'fm9999999999999999999999999990.999999999999'),'.');

        elsif regexp_like(k_typename, '^(DATE|TIME)') then
            dbms_sql.column_value(p_cursor_id, p_colnum, l_column_values.dateval);
            if l_column_values.dateval = trunc(l_column_values.dateval) then
                p_result_row := p_result_row || to_char(l_column_values.dateval,k_format_dateonly);
            else
                p_result_row := p_result_row || to_char(l_column_values.dateval,k_format_datetime);
            end if;

        elsif k_typename = 'ROWID' then
            dbms_sql.column_value(p_cursor_id, p_colnum, l_column_values.rowidval);
            p_result_row := p_result_row || rowidtochar(l_column_values.rowidval);

        else
            -- For anything else attempt to dump into a long string:
            dbms_sql.column_value(p_cursor_id, p_colnum, l_column_values.stringval);
            p_result_row := p_result_row ||l_column_values.stringval;
        end if;

    end append_result;


    -- Reusable extract function for REF extracts -
    -- performs delimiting and date formatting automatically, just pass in a normal cursor
    function report
        ( p_dataset    in sys_refcursor
        , p_separator  in varchar2 default g_dflt_separator
        , p_label      in varchar2 default null
        , p_heading    in varchar2 default 'N'
        , p_rowcount   in varchar2 default 'N' )
        return sys.dbms_debug_vc2coll
        pipelined
    is
        l_rowcount        integer := 0;
        l_cursor_id       integer;
        l_cursor_columns  dbms_sql.desc_tab;
        l_heading         long;
        l_result_row      long;
    begin
        if regexp_like(p_separator,'^[A-Z0-9"]$') then
            raise_application_error(-20000, 'Invalid separator "'||p_separator||'"', false);
        elsif p_label in (k_heading_label, k_footer_label) then
            raise_application_error(-20001, 'Data label cannot be same as header label ("'||k_heading_label||'") or footer label ("'||k_footer_label||'")', false);
        end if;

        -- Convert the ref cursor into a DBMS_SQL cursor number and parse the columns
        prepare_cursor(p_dataset, l_cursor_id, l_cursor_columns);

        -- Print header row
        if regexp_like(p_heading,'^Y(ES)?$', 'i') then
            for i in 1 .. l_cursor_columns.count loop
                l_heading := l_heading || l_cursor_columns(i).col_name || p_separator;
            end loop;

            l_heading := rtrim(l_heading,p_separator);

            if p_label is not null and g_label_in_header then
                l_heading := k_heading_label || p_separator || p_label || p_separator || l_heading;
            end if;

            pipe row(l_heading);
        end if;

        -- Fetch and print the rows:
        while dbms_sql.fetch_rows(l_cursor_id) > 0 loop
            if p_label is not null then
                l_result_row := p_label || p_separator;
            else
                l_result_row := null;
            end if;

            for i in 1 .. l_cursor_columns.count loop

                append_result(l_cursor_id, i, l_cursor_columns(i).col_type, p_separator, l_result_row );

                if i < l_cursor_columns.count then
                    l_result_row := l_result_row || p_separator;
                end if;
            end loop;

            l_rowcount := l_rowcount +1;

            pipe row(l_result_row);
        end loop;

        close_dbms_sql_cursor(l_cursor_id);

        -- If no rows, return a row with null values e.g. 'THELABEL|||'
        if l_rowcount = 0 then
            if p_label is not null then
                pipe row(p_label || rpad(p_separator, l_cursor_columns.count, p_separator));
            else
                pipe row(rpad(p_separator, l_cursor_columns.count -1, p_separator));
            end if;
        end if;

        -- Print footer
        if regexp_like(p_rowcount,'^Y(ES)?$', 'i') then
            pipe row(k_footer_label|| p_separator || case when p_label is not null and g_label_in_footer then p_label || p_separator end || l_rowcount);
        end if;

        return;

    exception
        -- Logger captures error details so you don't have to
        when no_data_needed then
            -- Fetching was cancelled before end of cursor
            close_dbms_sql_cursor(l_cursor_id);
            return;
    end report;

begin
    -- Copied from dbms_types
    -- (See also https://docs.oracle.com/database/121/LNOCI/oci03typ.htm#LNOCI16266 - does not exactly match dbms_types.
    -- Can also test examples using dump e.g. "select dump(dummy) from dual" shows type = 1
    -- though
    g_typenames(1)   := 'VARCHAR2';
    g_typenames(2)   := 'NUMBER';
    g_typenames(9)   := 'VARCHAR2';
    g_typenames(11)  := 'ROWID';  -- undocumented
    g_typenames(12)  := 'DATE';  -- Stored date
    g_typenames(13)  := 'DATE';  -- Generated date e.g. SYSDATE
    g_typenames(58)  := 'OPAQUE';
    g_typenames(95)  := 'RAW';
    g_typenames(96)  := 'CHAR';
    g_typenames(100) := 'BFLOAT';
    g_typenames(101) := 'BDOUBLE';
    g_typenames(104) := 'UROWID';
    g_typenames(105) := 'MLSLABEL';
    g_typenames(108) := 'OBJECT';
    g_typenames(110) := 'REF';
    g_typenames(112) := 'CLOB';
    g_typenames(113) := 'BLOB';
    g_typenames(114) := 'BFILE';
    g_typenames(115) := 'CFILE';
    g_typenames(122) := 'NAMEDCOLLECTION';
    g_typenames(180) := 'TIMESTAMP';  -- Stored timestamp
    g_typenames(181) := 'TIMESTAMP';  -- Stored timestamp with timezone
    g_typenames(182) := 'INTERVAL YM';
    g_typenames(182) := 'INTERVAL DS';
    g_typenames(183) := 'INTERVAL';
    g_typenames(187) := 'TIMESTAMP';
    g_typenames(188) := 'TIMESTAMP_TZ';  -- Generated timestamp with timezone e.g. SYSTIMESTAMP
    g_typenames(189) := 'INTERVAL_YM';
    g_typenames(190) := 'INTERVAL_DS';
    g_typenames(232) := 'TIMESTAMP_LTZ';
    g_typenames(247) := 'VARRAY';
    g_typenames(248) := 'TABLE';
    g_typenames(286) := 'NCHAR';
    g_typenames(287) := 'NVARCHAR2';
    g_typenames(288) := 'NCLOB';

    -- The types from the above list we can handle in REF extract:
    -- (only checked after processing '%CHAR%' etc - this list is to exclude the more exotic datatypes)
    g_supported_types(1)   := 'VARCHAR2';
    g_supported_types(2)   := 'NUMBER';
    g_supported_types(9)   := 'VARCHAR2';
    g_supported_types(11)  := 'ROWID';  -- undocumented
    g_supported_types(12)  := 'DATE';
    g_supported_types(13)  := 'DATE';
    g_supported_types(96)  := 'CHAR';
    g_supported_types(104) := 'UROWID';
    g_supported_types(180) := 'TIMESTAMP';
    g_supported_types(181) := 'TIMESTAMP_TZ';
    g_supported_types(187) := 'TIMESTAMP';
    g_supported_types(188) := 'TIMESTAMP_TZ';
    g_supported_types(286) := 'NCHAR';
    g_supported_types(287) := 'NVARCHAR2';

end csv;
/

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