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

Automatically Backuping Apex Applications

Automatically Backuping Apex Applications

We can export our apex applications as an sql file in “Home>Application Builder>Application $ID>Export / Import>Export” menu. You can do this as manually, so if you want to backup your apex application(s) you should do this for each time for each application.

As you guess this operation runs a plsql command in background to export application.

“wwv_flow_utilities” public package of apex, includes “export_application_to_clob” function that returns clob variable that containts sql statements. These sql statements those are application metadata that will be imported your workspace. Spec part of function is below :

function export_application_to_clob (
p_application_id in number,
p_export_saved_reports in varchar2 default ‘N’)
return clob
;

Application ID is a number that user specified or automatically got from apex. That is on right of f?p= parameter of url string. You should specify application id to export application. Another parameter is p_export_saved_reports is optional, if you want to export saved report you can set this as ‘Y’.

You can write a dbms_scheduler job that runs this plsql for every specified interval time. Or you can write a unix shell script that produces an sql file, put this shell script to crontab to run this backup operations for every specified interval time. Or anything else…

Also you can export only a page of an application, this function is under wwv_flow_utilities package too, spec part of this :

function export_page_to_clob (
p_application_id in number,
p_page_id in number)
return CLOB
;

Example :

CREATE TABLE EXPORT_CLOB
(
APP_EXPORT CLOB
);

BEGIN

INSERT INTO EXPORT_CLOB
VALUES (WWV_FLOW_UTILITIES.EXPORT_APPLICATION_TO_CLOB(’107′));

END;

SELECT length(app_export) FROM EXPORT_CLOB;

954956

To backup your all of applications you can get application id from “select * from apex_applications”.

This example was tested on Oracle 11.1.0.6 and Apex 3.2.