Tip#10 Dump to text file using UTL_FILE

Dump the data from table/sql to text file (CSV or PIPE delimated etc) on the server using UTL_FILE

Note :
A) Specify the accessible directories for the UTL_FILE functions in the initialization file using the UTL_FILE_DIR parameter. e.g. : UTL_FILE_DIR = /app/oracle/my_dir
FYI, UTL_FILE_DIR = * means turn off directory access checking, and it makes any directory accessible to the UTL_FILE (NOT RECOMMANDED).

EDIT: if you are using > 9i , you can use DIRECTORY instead of UTL_FILE_DIR.

B) Since we have data with special character, we open file with FOPEN_NCHAR to make sure we don’t loose special characters in the text dump.

C) I have just tested it on Redhat Linux - Oracle 9.2.0.6

This is the Function which I use (adapted to my requirements but thanks to Tom Kyte to get me started)

CREATE OR REPLACE FUNCTION dump_to_file (
p_query IN VARCHAR2,
p_separator IN VARCHAR2 DEFAULT '|',
p_dir IN VARCHAR2,
p_filename IN VARCHAR2
)
RETURN NUMBER
IS
l_filehandle UTL_FILE.file_type;
l_thecursor INTEGER DEFAULT DBMS_SQL.open_cursor;
l_columnvalue VARCHAR2 (32767);
l_col_hdr VARCHAR2 (32767);
l_status INTEGER;
l_colcnt NUMBER DEFAULT 0;
l_separator VARCHAR2 (10) DEFAULT '';
l_cnt NUMBER DEFAULT 0;
col_cnt PLS_INTEGER;
rec_tab DBMS_SQL.desc_tab;
col_num NUMBER;
BEGIN
-- open a writable file in the UTL_FILE_DIR directory
l_filehandle := UTL_FILE.fopen_nchar (p_dir, p_filename, 'w', 32767);

-- parse the sql we got
DBMS_SQL.parse (l_thecursor, p_query, DBMS_SQL.native);

-- execute the sql cursor
l_status := DBMS_SQL.EXECUTE (l_thecursor);

-- get the column information l_colcnt OUT param to get us number of columns
-- and rec_tab is plsql table with all column related information like name, length,schema, precision, scale etc
DBMS_SQL.describe_columns (l_thecursor, l_colcnt, rec_tab);

-- we need to print the column names as the first line
l_separator := '';
l_col_hdr := '';


FOR i IN 1 .. l_colcnt
LOOP
-- we define which columns we need in the fetch
DBMS_SQL.define_column (l_thecursor, i, l_columnvalue, 4000);
-- print the column name in the file
l_col_hdr := l_col_hdr || l_separator || rec_tab (i).col_name;
l_separator := p_separator;
END LOOP;

UTL_FILE.put_nchar (l_filehandle, l_col_hdr);
UTL_FILE.new_line (l_filehandle);

-- lets print the data now
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_nchar (l_filehandle, l_separator || l_columnvalue); l_separator := p_separator; END LOOP; UTL_FILE.new_line (l_filehandle); l_cnt := l_cnt + 1; END LOOP; DBMS_SQL.close_cursor (l_thecursor); UTL_FILE.fclose (l_filehandle); RETURN l_cnt; EXCEPTION WHEN UTL_FILE.invalid_operation THEN UTL_FILE.fclose (l_filehandle); raise_application_error (-20061, 'Dump To File Error: Invalid Operation'); WHEN UTL_FILE.invalid_filehandle THEN UTL_FILE.fclose (l_filehandle); raise_application_error (-20062, 'Dump To File Error: Invalid File Handle'); WHEN UTL_FILE.write_error THEN UTL_FILE.fclose (l_filehandle); raise_application_error (-20063, 'Dump To File Error: Write Error'); WHEN OTHERS THEN UTL_FILE.fclose (l_filehandle); RAISE; END dump_to_file;

How to Use above function ?

To generate pipe (‘ | ’) delimated file called emp.txt in /app/oracle/my_dir for my query, I execute something like following

DECLARE
l_rows NUMBER := 0;
l_dir VARCHAR2 (100) := '/app/oracle/my_dir';
BEGIN
l_rows := dump_to_file ('select * FROM scott.emp ', '|', l_dir, 'emp.txt');
DBMS_OUTPUT.put_line ('Number of rows dumped to emp.txt : ' || l_rows);
END;

EDIT:
if you want to use DIRECTORY instead of UTL_FILE_DIR, just create one like following,

create or replace directory my_dir as '/app/oracle/my_dir';

then use above code with 'my_dir' instead of l_dir.