Return any table/query as CSV data - dynamically (DBMS_SQL)

The below code allows you to query any table and get it's data back as a csv. Nothing is hard coded here so it works well for most any table you can throw at it.

I have a MUCH faster solution now, here


CREATE OR REPLACE PROCEDURE GET_CSV_FROM_SYSREFCURSOR
(
IN_CUR IN OUT SYS_REFCURSOR
,OUT_CSV OUT CLOB
) AS

V_OUT_CSV CLOB;
V_OUT_ROW_DATA CLOB;
COL_VAL VARCHAR2(32767);

H NUMBER;
NUM_COLS NUMBER;
RET DBMS_SQL.DESC_TAB;

BEGIN

H := DBMS_SQL.TO_CURSOR_NUMBER(IN_CUR);

DBMS_SQL.DESCRIBE_COLUMNS(H
,NUM_COLS
,RET);

FOR I IN 1 .. RET.COUNT
LOOP
DBMS_SQL.DEFINE_COLUMN(H
,I
,COL_VAL
,32767);
END LOOP;

WHILE DBMS_SQL.FETCH_ROWS(H) > 0
LOOP

FOR NBR_COLS IN 1 .. NUM_COLS
LOOP
DBMS_SQL.COLUMN_VALUE(H
,NBR_COLS
,COL_VAL);

V_OUT_ROW_DATA := V_OUT_ROW_DATA || '"' || COL_VAL || '"' || ', ';
END LOOP;

V_OUT_CSV := V_OUT_CSV || TRIM(TRAILING ', ' FROM V_OUT_ROW_DATA) || CHR(13);
V_OUT_ROW_DATA := NULL;

END LOOP;

DBMS_SQL.CLOSE_CURSOR(H);

OUT_CSV := V_OUT_CSV;

/*
Example call

DECLARE
my_cur SYS_REFCURSOR;
data_clob CLOB;

BEGIN

OPEN my_cur FOR SELECT * FROM user_tables;
get_csv_from_sysrefcursor(my_cur, data_clob);
dbms_output.put_line(substr(data_clob,1,4000));

END;

*/

END;