dump_csv

CREATE OR REPLACE FUNCTION RMVER721.dump_csv (
p_query IN VARCHAR2,
p_filename IN VARCHAR2 DEFAULT 'TEMP',
p_separator IN VARCHAR2 DEFAULT ',',
p_dir IN VARCHAR2 DEFAULT 'XLS_DIR'
)
RETURN NUMBER
IS
l_output UTL_FILE.file_type;
l_thecursor PLS_INTEGER DEFAULT DBMS_SQL.open_cursor;
l_columnvalue VARCHAR2 (4000);
l_status PLS_INTEGER;
l_colcnt NUMBER (10) DEFAULT 0;
l_separator VARCHAR2 (10) DEFAULT '';
l_cnt NUMBER (10) DEFAULT 0;
l_sht NUMBER (5) DEFAULT 0;
l_tot NUMBER (10) DEFAULT 0;
rec_tab DBMS_SQL.desc_tab;
BEGIN
l_output := UTL_FILE.fopen (p_dir, p_filename || l_sht || '.xls', 'w');
DBMS_SQL.parse (l_thecursor, p_query, DBMS_SQL.native);
DBMS_SQL.describe_columns (l_thecursor, l_colcnt, rec_tab);

FOR i IN rec_tab.FIRST .. rec_tab.LAST
LOOP
UTL_FILE.put (l_output, l_separator || rec_tab (i).col_name);
l_separator := CHR (9);
END LOOP;

UTL_FILE.new_line (l_output);

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 := CHR (9);
END LOOP;

UTL_FILE.new_line (l_output);
l_cnt := l_cnt + 1;
l_tot := l_tot + 1;

IF l_cnt >= 65000
THEN
l_cnt := 1;
l_sht := l_sht + 1;
UTL_FILE.fclose (l_output);
l_output :=
UTL_FILE.fopen (p_dir, p_filename || l_sht || '.xls', 'w');
l_separator := '';

FOR i IN rec_tab.FIRST .. rec_tab.LAST
LOOP
UTL_FILE.put (l_output, l_separator || rec_tab (i).col_name);
l_separator := CHR (9);
END LOOP;

UTL_FILE.new_line (l_output);
END IF;
END LOOP;

DBMS_SQL.close_cursor (l_thecursor);
UTL_FILE.fclose (l_output);
RETURN l_tot;
END dump_csv;
/