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 126.96.36.199 and Apex 3.2.
Interactive Reports in Apex, presents too many features that will be performed on reports. You can apply specified column filtering, highlighted specified values in a column, use aggregation functions, ajax paginations are some of these features. To use these features you can create a report region as Interactive report ( In a page attributes, Create Region > Report > Interactive Report ).
While creating Interactive Reports, apex wants from you an sql word. It must be pure sql unlike varchar2 variable that stores sql ( in standart reports you can use a varchar2 variable that stores sql. For example lv_dynamic_sql := ‘SELECT EMP_NO FROM EMPLOYEE’; return lv_dynamic_sql; ). So in normally you can’t use dynamic sql in interactive report. I said “in normally” 🙂
By using PIPELINED FUNCTIONS you can use dynamic sql in Apex Interactive Reports.
In Interactive Reports, you will specify sql as “SELECT * FROM TABLE(table_function);”
After that you should design your “table_function()” function.
First of all you should create an object type that matches a report row fields.
Your report fields should match these object type fields.
After that you should design your “table_function” function.
You can check this function accuracy:
Anymore, there is no need to do anything to use this sql in interactive report.
If you get an uniquely related error you can specify a unique column ( a_rowid in this example ) in Report Attributes > Link Column section.
Edited on 05.10.2010 :
There is a better solution to handle more dynamic sql. Which means that you can prepare dynamic interactive report for any sql. Those sqls do not need to has same number of columns or same column names. It is demonstrated at http://www.oracleapplicationexpress.com/tutorials/71 .
For detail information, examples and to do download this api, visit http://jquery.com.
If you want to import and use this api in your Apex Application you will have to do some steps.
1 ) Download latest version of jQuery Api from http://jquery.com or customizable version at : http://jqueryui.com/download
2 ) Install those files to $ORACLE_HOME/apex/images/jquery/ folder. ( jQuery folder must be created ).
3 ) Then, you should edit the $ORACLE_HOME/apex/images/imagelist.xml file.
This file an xml file and you should add those under the directories node.
And, you should add file paths under the files node. ( Those file names should be changed if you downloaded another version instead of 1.6 Personalized JQuery )
4 ) After that, you should run apex_epg_config.sql ( in $ORACLE_HOME/apex directory ) and enter $ORACLE_HOME whichever it is, to refresh file paths.
5 ) So far, you imported and installed the jquery to your Apex environment. Now, you can use jquery in your apex pages. Let’s do an example, create a page and create a region and we will create a draggable div place.
( There are many examples at http://jquery.com )
6 ) Then test your page, click div place and drag it to anywhere and release, it will return its first position.