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/SL 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 header if you pass p_heading => 'Y'. (Default is no header.)
  • 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. 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
    l_separator varchar2(10) := ',';
    -- Returns cursor results in character-delimited format with optional header, data label and rowcount.
    --     select column_value
    --     from   table(
    --                select 'Demo', sysdate from dual
    --            ), '|', 'LABEL'));
    --     COLUMN_VALUE
    --     -------------------------------
    --     LABEL|Demo|2017-07-11 11:45:57
    --     ROW_COUNT|LABEL|1
    function report
        ( p_dataset    in sys_refcursor
        , p_separator  in varchar2 default l_separator
        , p_label      in varchar2 default null )
        return sys.dbms_debug_vc2coll  -- 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';
    -- Record for parsing a column's value:
    type column_data is record
        ( stringval    long
        , numval       number
        , dateval      date );
    -- 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 )
        l_extract_query   sys_refcursor := p_dataset;
        l_col_count       integer;
        l_dummy_number    number;
        l_dummy_date      date;
        l_dummy_string    varchar2(4000);
        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 g_typenames(p_cursor_columns(i).col_type) like '%CHAR%' then
                    dbms_sql.define_column(p_cursor_id, i, l_dummy_string, 4000 );
                elsif p_cursor_columns(i).col_type = dbms_types.typecode_number then
                    dbms_sql.define_column(p_cursor_id, i, l_dummy_number );
                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 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);
                    -- 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:
                    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 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 l_separator
        , p_result_row  in out long )
        k_typename constant varchar2(40) := g_typenames(p_col_type);
        l_column_values column_data;
        if k_typename like '%CHAR%' 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;
            -- 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 l_separator
        , p_label      in varchar2 default null )
        return sys.dbms_debug_vc2coll
        l_rowcount        integer := 0;
        l_cursor_id       integer;
        l_cursor_columns  dbms_sql.desc_tab;
        l_result_row      long;
        -- Convert the ref cursor into a DBMS_SQL cursor number and parse the columns
        prepare_cursor(p_dataset, l_cursor_id, l_cursor_columns);
        -- Fetch and process 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 l_rowcount = 0 then
            -- If no rows, return a row with null values e.g. 'THELABEL|||'
            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;
        pipe row('ROW_COUNT|'|| case when p_label is not null then p_label || p_separator end || l_rowcount);
        -- 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.
    -- 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(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(12)  := 'DATE';
    g_supported_types(13)  := 'DATE';
    g_supported_types(96)  := 'CHAR';
    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