Shell script to export all APEX applications as SQL scripts

Environment: Oracle 11gR2 EE, Application Express 4.2.2, Oracle Linux 6.4 64-bit, Java JDK 7u21

The APEX software contains a Java class which can be used to script or schedule the export of one or more APEX applications. I will show you in this post how to do this.

First, I unzipped the APEX software to /u01/app/apex. The Java class file is called “APEXExport.class” and can be found in the folder /u01/app/apex/utilities/oracle/apex.

You also need a Java JDK. I installed Java 7 JDK (update 21) under /u01/app/java/java7.

You might need to unlock (and set a password) for the APEX schema owner (in my case, APEX_040200). I’m not sure if it’s possible to export the applications with another user.

This is how my script looks like:

# script to export all ApEx applications as sql scripts

CLASSPATH=$CLASSPATH:/u01/app/oracle/product/11.2.0/db_1/oui/jlib/classes12.jar; export CLASSPATH

cd /u01/app/apex/utilities

/u01/app/java/java7/bin/java oracle.apex.APEXExport -db localhost:1521:ora11g01 -user apex_040200 -password <password> -instance

mv f*.sql $BACKUPDIR

if [ -f $BACKUPDIR/apex_export.log ]
    cat $BACKUPDIR/apex_export.log|mail -s "APEX export - `hostname -s`"

You need to include the file classes12.jar in your CLASSPATH or the JDBC connection won’t work.

The -instance setting will export all your applications. To limit the number of applications, you can also use -workspaceid or -applicationid. Check the readme.txt file under the utilities folder for more information.

To schedule the script with cron, I added the following line to my crontab file:

00 22 * * * /home/oracle/scripts/ 1>/u05/apex_backup/apex_export.log 2>&1

This will execute the script each night at 22:00 and mail the log file to the mail address inside the script.

Happy scripting! :-)