Extraído de https://forums.oracle.com/message/4080249
Recientemente tuve la necesidad de crear un Stored Procedure para crear
archivos de texto delimitados, como resultados de varias consultas a la Base de
Datos. Les dejo la rutina que invoca y la función que realiza el proceso:
/***************************************************/
/*************** BLOQUE
ORIGINANTE *****************/
/***************************************************/
DECLARE
RetVal NUMBER;
BEGIN
RetVal :=
schDBA.dump_csv ('SELECT * FROM SIEBEL.S_OPTY',
'SIEBEL_EIM_DIR',
'S_OPTY.CSV',
'|',
true,
false,
32000,
'W'
);
END;
/*****************************************************/
/********* PROCESAMIENTO DE
LA CONSULTA **************/
/*****************************************************/
CREATE OR REPLACE FUNCTION dump_csv (
p_query IN VARCHAR2,
p_dir IN VARCHAR2,
p_filename IN VARCHAR2,
p_separator IN VARCHAR2,
p_headers IN BOOLEAN DEFAULT FALSE,
p_trailing_separator IN BOOLEAN DEFAULT FALSE,
p_max_linesize IN NUMBER DEFAULT 32000,
p_mode IN VARCHAR2 DEFAULT 'w'
)
RETURN NUMBER
IS
l_output UTL_FILE.FILE_TYPE;
l_thecursor INTEGER DEFAULT DBMS_SQL.open_cursor;
l_columnvalue VARCHAR2 (4000);
l_status INTEGER;
l_colcnt NUMBER
DEFAULT 0;
l_cnt NUMBER
DEFAULT 0;
l_separator VARCHAR2 (10)
DEFAULT '';
l_line LONG;
l_desctbl DBMS_SQL.desc_tab;
v_sqlerrm VARCHAR2 (32000);
l_mode CHAR (1)
:= 'w';
BEGIN
IF p_mode NOT IN ('w', 'a')
THEN
l_mode := 'w';
ELSE
l_mode := p_mode;
END IF;
l_output := UTL_FILE.fopen (p_dir, p_filename, l_mode, p_max_linesize);
DBMS_SQL.parse (l_thecursor, p_query, DBMS_SQL.native);
DBMS_SQL.describe_columns (l_thecursor, l_colcnt, l_desctbl);
FOR i IN 1 .. l_colcnt
LOOP
DBMS_SQL.define_column (l_thecursor, i, l_columnvalue, 4000);
IF (l_desctbl (i).col_type = 2)
/* number type */
THEN
l_desctbl (i).col_max_len := l_desctbl (i).col_precision + 2;
ELSIF (l_desctbl (i).col_type = 12)
/* date type */
THEN
/* length of my date format
*/
l_desctbl (i).col_max_len := 20;
ELSIF (l_desctbl (i).col_type = 8)
/* LONG type */
THEN
l_desctbl (i).col_max_len := 2000;
END IF;
IF p_headers
THEN
UTL_FILE.put (l_output, l_separator || l_desctbl (i).col_name);
l_separator := p_separator;
END IF;
END LOOP;
IF p_trailing_separator
THEN
UTL_FILE.put (l_output, l_separator);
END IF;
IF p_headers
THEN
UTL_FILE.new_line (l_output);
END IF;
l_status := DBMS_SQL.EXECUTE (l_thecursor);
LOOP
EXIT WHEN (DBMS_SQL.fetch_rows (l_thecursor) <= 0);
l_line := NULL;
l_separator := '';
FOR i IN 1 .. l_colcnt
LOOP
DBMS_SQL.COLUMN_VALUE (l_thecursor, i, l_columnvalue);
IF NVL (INSTR (l_columnvalue, ','), 0) = 0
THEN
NULL;
ELSE
l_columnvalue := '"' || l_columnvalue || '"';
END IF;
UTL_FILE.put (l_output, l_separator || l_columnvalue);
l_separator := p_separator;
END LOOP;
IF p_trailing_separator
THEN
UTL_FILE.put (l_output, l_separator);
END IF;
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;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('NO_DATA_FOUND');
UTL_FILE.fclose (l_output);
RETURN l_cnt;
WHEN UTL_FILE.invalid_path
THEN
DBMS_OUTPUT.put_line ('UTL_FILE.INVALID_PATH');
UTL_FILE.fclose (l_output);
RETURN l_cnt;
WHEN UTL_FILE.read_error
THEN
DBMS_OUTPUT.put_line ('UTL_FILE.READ_ERROR');
UTL_FILE.fclose (l_output);
RETURN l_cnt;
WHEN UTL_FILE.write_error
THEN
DBMS_OUTPUT.put_line ('UTL_FILE.WRITE_ERROR');
UTL_FILE.fclose (l_output);
RETURN l_cnt;
WHEN UTL_FILE.invalid_mode
THEN
DBMS_OUTPUT.put_line ('UTL_FILE.INVALID_MODE');
UTL_FILE.fclose (l_output);
RETURN l_cnt;
WHEN UTL_FILE.invalid_filehandle
THEN
DBMS_OUTPUT.put_line ('UTL_FILE.INVALID_FILEHANDLE');
UTL_FILE.fclose (l_output);
RETURN l_cnt;
WHEN UTL_FILE.invalid_operation
THEN
DBMS_OUTPUT.put_line ('UTL_FILE.INVALID_OPERATION');
UTL_FILE.fclose (l_output);
RETURN l_cnt;
WHEN UTL_FILE.internal_error
THEN
DBMS_OUTPUT.put_line ('UTL_FILE.INTERNAL_ERROR');
UTL_FILE.fclose (l_output);
RETURN l_cnt;
WHEN UTL_FILE.invalid_maxlinesize
THEN
DBMS_OUTPUT.put_line ('UTL_FILE.INVALID_MAXLINESIZE');
UTL_FILE.fclose (l_output);
RETURN l_cnt;
WHEN VALUE_ERROR
THEN
DBMS_OUTPUT.put_line ('UTL_FILE.VALUE_ERROR');
UTL_FILE.fclose (l_output);
RETURN l_cnt;
WHEN OTHERS
THEN
--hum_do.default_exception ('ERROR in dump_csv : ');
DBMS_OUTPUT.put_line ('ERROR in dump_csv : ');
UTL_FILE.fclose (l_output);
RETURN l_cnt;
END dump_csv;
