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


select column_value
from   table(
           select * from dept


4 rows selected.
select column_value
from   table(
           select * from dept
       ), p_separator => '|', p_label => 'DEPT', p_rowcount => 'Y'));


5 rows selected.
select column_value
from   table(
           select * from dept
       ), p_separator => '|', p_label => 'DEPT', p_heading => 'Y', p_rowcount => 'Y'));


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

create or replace package csv as

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

    -- Lines > 4K require a custom collection type to support CLOBs (also grant EXECUTE on the type to suitable roles and/or users):
    -- create or replace type clob_tt as tble of clob;

    -- Returns cursor results in character-delimited format with optional header, data label and rowcount.
    --     select column_value
    --     from   table(
    --                select * from dept
    --            )));
    --     COLUMN_VALUE
    --     ----------------------------------------------
    --     20,RESEARCH,DALLAS
    --     30,SALES,CHICAGO
    --     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.ku$_vcnt  -- generic string array: use your own if you prefer

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
        , clobval      clob
        , 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 nocopy dbms_sql.desc_tab )
        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_clob      clob;
        l_dummy_rowid     rowid;
        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
                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 );
                elsif g_typenames(p_cursor_columns(i).col_type) = 'CLOB' then
                    dbms_sql.define_column(p_cursor_id, i, l_dummy_clob);
                    -- 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;
                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 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 )
        if dbms_sql.is_open(p_cursor_id) then
        end if;
        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 nocopy long )
        k_typename constant varchar2(40) := g_typenames(p_col_type);
        l_column_values column_data;
        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 ||
                    when l_column_values.stringval like '%'||p_separator||'%' then '"'||trim(l_column_values.stringval)||'"'
                    else trim(l_column_values.stringval)

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

        elsif k_typename = 'CLOB' then
            dbms_sql.column_value(p_cursor_id, p_colnum, l_column_values.clobval);
            p_result_row := p_result_row ||
                    when l_column_values.clobval like '%'||p_separator||'%' then '"'||trim(l_column_values.clobval)||'"'
                    else trim(l_column_values.clobval)
            -- 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;

        if length(p_result_row) > 4000 then
            raise_application_error(-20005, 'Output line beginning "' || substr(p_result_row,1,50) ||'" is loo long (max: 4000, actual: ' || length(p_result_row) );
        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.ku$_vcnt
        l_rowcount        integer := 0;
        l_cursor_id       integer;
        l_cursor_columns  dbms_sql.desc_tab;
        l_heading         long;
        l_result_row      long;
        if regexp_like(p_separator,'^[A-Z0-9"]$') then
            raise_application_error(-20003, 'Invalid separator "'||p_separator||'"', false);
        elsif p_label in (k_heading_label, k_footer_label) then
            raise_application_error(-20004, '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;
                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;


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


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

    -- Copied from dbms_types
    -- See also - does not exactly match dbms_types.
    -- We can also test examples using dump e.g. "select dump(dummy) from dual" shows type = 1
    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(112) := 'CLOB';
    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: method 4 dynamic sql in pl/sql