Export Oracle dump query result to CSV format

Here the simple procedure to do it,

CREATE OR REPLACE FUNCTION DUMP_CSV (
P_QUERY IN VARCHAR2,
P_SEPARATOR IN VARCHAR2 DEFAULT ‘,’,
P_DIR IN VARCHAR2,
P_FILENAME IN VARCHAR2
) RETURN NUMBER
IS

L_OUTPUT UTL_FILE.FILE_TYPE;
L_THECURSOR INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
L_COLUMNVALUE VARCHAR2 (2000);
L_STATUS INTEGER;
L_COLCNT NUMBER DEFAULT 0;
L_SEPARATOR VARCHAR2 (10) DEFAULT ”;
L_CNT NUMBER DEFAULT 0;

BEGIN
L_OUTPUT := UTL_FILE.FOPEN (P_DIR, P_FILENAME, ‘w’);
DBMS_SQL.PARSE (L_THECURSOR, P_QUERY, DBMS_SQL.NATIVE);
FOR I IN 1 .. 255 LOOP
BEGIN
DBMS_SQL.DEFINE_COLUMN (L_THECURSOR, I, L_COLUMNVALUE, 2000);
L_COLCNT := I;
EXCEPTION WHEN OTHERS THEN
IF (SQLCODE = -1007) THEN
EXIT;
ELSE
RAISE;
END IF;
END;
END LOOP;

DBMS_SQL.DEFINE_COLUMN (L_THECURSOR, 1, L_COLUMNVALUE, 2000);
L_STATUS := DBMS_SQL.EXECUTE (L_THECURSOR);

LOOP EXIT WHEN (DBMS_SQL.FETCH_ROWS (L_THECURSOR) <= 0);
L_SEPARATOR := ”;
FOR I IN 1 .. L_COLCNT LOOP
DBMS_SQL.COLUMN_VALUE (L_THECURSOR, I, L_COLUMNVALUE);
UTL_FILE.PUT (L_OUTPUT, L_SEPARATOR || L_COLUMNVALUE);
L_SEPARATOR := P_SEPARATOR;
END LOOP;

UTL_FILE.NEW_LINE (L_OUTPUT);
L_CNT := L_CNT + 1;
END LOOP;

DBMS_SQL.CLOSE_CURSOR (L_THECURSOR);
UTL_FILE.FCLOSE (L_OUTPUT);

RETURN L_CNT;

END DUMP_CSV;
/

To test it, you can simply run

SELECT dump_csv (‘SELECT * FROM EMPLOYEES’, ‘,’, ‘WORK_DIR’, ‘test.csv’) FROM DUAL;
– P_QUERY            = ‘SELECT * FROM EMPLOYEES’
– P_SEPARATOR  = ‘,’
– P_DIR                   = ‘WORK_DIR’
– P_FILENAME     = ‘test.csv’

WORK_DIR is oracle directory, that create through this script,

CREATE OR REPLACE DIRECTORY WORK_DIR AS ‘C:\csv’;