Shell script to export all APEX applications as SQL scripts
August 7, 2013 1 Comment
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:
#!/bin/sh # 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 BACKUPDIR=/u05/apex_backup 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 ] then cat $BACKUPDIR/apex_export.log|mail -s "APEX export - `hostname -s`" firstname.lastname@example.org fi
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/apex_export.sh 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! :-)