SQL query results to CSV source code

oracle-sql-to-csv-cursor-src.sql

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;

oracle-sql-to-csv-raw-queries-src.sql

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;