SQL query results to CSV with PL/SQL

This post was originally published in 2015 and may contain outdated information.

In this post I’ll show you how to get Oracle SQL query results to CSV format into a CLOB only by calling stored procedures. This CLOB can be stored later in a table row or in a file using UTL_FILE.

There are two stored procedures:

The first one, cursor_to_csv, takes a cursor as parameter
The second one, query_to_csv, takes a VARCHAR2

Usage

Using a cursor:

DECLARE
    v_items_cursor SYS_REFCURSOR;
    v_csv          CLOB;
BEGIN
    OPEN v_items_cursor FOR
        SELECT * FROM items;
    
    v_csv := cursor_to_csv(v_items_cursor);
END;

Using a raw SQL query:

DECLARE
    v_csv CLOB;
BEGIN
    SELECT query_to_csv('SELECT * FROM items') INTO v_csv FROM dual;
END;

For both calls the result is the same:

ITEM_ID;NAME;PRICE
1;mi;328
2;Duis gravida praesent;5
3;ac mattis semper;353
4;volutpat nunc sit;431
5;pede blandit;433
6;eros;452
7;ac orci;243
8;felis ullamcorper viverra;250
9;lorem, luctus ut;494
10;scelerisque scelerisque dui;263

Warning: theses procedures are made for quite small results. If you want to generate export a lot of data, please consider using another solution such as SQL*Plus. On my database, exporting a 1000 x ~300 characters result took 2 seconds.

The code

FUNCTION cursor_to_csv (
    p_cursor     IN OUT SYS_REFCURSOR
)
RETURN CLOB
IS
    l_cursor_id     INTEGER DEFAULT dbms_sql.open_cursor;
    l_colval        VARCHAR2 (2096);
    l_buffer        VARCHAR2 (32767) DEFAULT '';
    l_status        INTEGER;
    i_colcount      NUMBER DEFAULT 0;
    l_separator     VARCHAR2 (10) DEFAULT '';
    l_file          CLOB;
    l_eol           VARCHAR(2) DEFAULT CHR (10);
    l_colsdescr     dbms_sql.desc_tab;
    l_lines_cnt     NUMBER DEFAULT 1;
    l_max_lines_cnt NUMBER DEFAULT 16392;
BEGIN

    l_cursor_id := dbms_sql.to_cursor_number(p_cursor);
    dbms_sql.describe_columns(l_cursor_id, i_colcount, l_colsdescr);

    FOR i IN 1 .. i_colcount
    LOOP
        dbms_sql.define_column (l_cursor_id, i, l_colval, 2000);
        l_buffer := l_buffer || l_separator || l_colsdescr(i).col_name;
        l_separator := ';';
    END LOOP;

    dbms_lob.createtemporary(l_file, FALSE, dbms_lob.call);
    dbms_lob.open(l_file, dbms_lob.lob_readwrite);

    l_buffer := l_buffer || l_eol;
    dbms_lob.write( l_file, LENGTH(l_buffer), 1, l_buffer);

    LOOP
        EXIT WHEN (
            dbms_sql.fetch_rows (l_cursor_id) <= 0 OR l_lines_cnt >= l_max_lines_cnt
        );

        l_separator := '';
        l_buffer := '';

        FOR i IN 1 .. i_colcount
        LOOP
            dbms_sql.column_value (l_cursor_id, i, l_colval);

            IF (l_colval IS NOT NULL AND l_colval LIKE '%;%')
            THEN
                IF (l_colval LIKE '%"%')
                THEN
                    l_colval := REPLACE(l_colval, '"', '""');
                END IF;

                l_colval := '"' || l_colval || '"';
            END IF;

            l_buffer := l_buffer || l_separator || l_colval;
            l_separator := ';';
        END LOOP;

        l_buffer := l_buffer || l_eol;
        l_lines_cnt := l_lines_cnt + 1;

        dbms_lob.writeappend( l_file, LENGTH(l_buffer), l_buffer);

    END LOOP;

    dbms_sql.close_cursor (l_cursor_id);
    dbms_lob.close(l_file);

    RETURN l_file;

EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line(SQLERRM);

        IF dbms_sql.is_open (l_cursor_id) THEN
            dbms_sql.close_cursor (l_cursor_id);
        END IF;
END cursor_to_csv;

FUNCTION query_to_csv (
    p_query IN  VARCHAR2
)
RETURN CLOB
IS
    l_cursor SYS_REFCURSOR;
BEGIN
    OPEN l_cursor FOR p_query;
    RETURN adm_umd_sysadm.toolkit.cursor_to_csv (l_cursor);
EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line(SQLERRM);

        IF l_cursor%ISOPEN THEN
            CLOSE l_cursor;
        END IF;
END query_to_csv;