Writing an sql query output to a File in PLSQL Package

We are developing ETL projects and sometimes we need to write output of a table or view query to a file. The problem is that for each project it should be written another package or procedure to write table or query data to a file. This is a needless operation and I decided to write a package to overcome this problem.

The package that I coded is PKG_QUERY_TO_FILE that has two public procedure to write a query’s output to a file. Example calling of the procedures are listed below:

BEGIN 

PKG_QUERY_TO_FILE.WRITE(
pv_sql_query => ‘SELECT * FROM ODS_ETL.TTA01EXTLOAD_DEFINITIONS WHERE SOURCE_SYSTEM = ”BSCS”’,
pv_disk_directory => ‘/ETLfs’,
pv_file_name => ‘extload.txt’,
pb_include_column_header => TRUE,
pv_column_header_encloser => ‘”‘,
pv_column_header_seperator => ‘;’,
pv_file_seperator => ‘,’,
pb_append_mode => FALSE,
pv_null_replacer => ‘NULL’
);

PKG_QUERY_TO_FILE.WRITE(
pv_sql_query => ‘SELECT * FROM ODS_ETL.TTA01EXTLOAD_DEFINITIONS WHERE SOURCE_SYSTEM = ”BSCS”’,
pv_disk_directory => ‘/ETLfs’,
pv_file_name => ‘extload2.txt’
);

EXECUTE IMMEDIATE ‘CREATE OR REPLACE DIRECTORY SIL_TMPDIR AS ”/ETLfs/fuat”’;
PKG_QUERY_TO_FILE.WRITE_SIMPLE(
pv_sql_query => ‘SELECT * FROM ODS_ETL.TTA01EXTLOAD_DEFINITIONS WHERE SOURCE_SYSTEM = ”BSCS”’,
pv_directory_object => ‘SIL_TMPDIR’,
pv_file_name => ‘extload3.txt’
);
EXECUTE IMMEDIATE ‘DROP DIRECTORY SIL_TMPDIR’;
END;

You can analyse, download and use free the code of PKG_QUERY_TO_FILE package, from here. You can add your log system instead DBMS_OUTPUT.

It may have some bugs if you specify that I will fix it or you can create your own generic package by using my package.

Also Thomas Kyte has written a package about this problem ( http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:95212348059 ).