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:
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
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 :
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 :
To backup your all of applications you can get application id from “select * from apex_applications”.
This example was tested on Oracle 22.214.171.124 and Apex 3.2.