Home > Oracle, PL / SQL > Writing an sql query output to a File in PLSQL Package

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 ).

Advertisements
Categories: Oracle, PL / SQL Tags: , , ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: