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:

#!/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`" matthiash@mydomain.com
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! :-)

Matthias

Advertisements

APEX 4.2.2 released!

Oracle released a new minor version of Application Express: 4.2.2, or to be complete: 4.2.2.00.11.

Those who are already on version 4.2.x can download a patch from My Oracle Support (if you have a support contract). The patch number is 16277995. Other users can download the full version here.

Interesting documents to read are the known issues and the list of bugs fixed with this release. Unfortunately, the problem with APEX_LDAP.AUTHENTICATE and accented user names (see my earlier blog post) is not mentioned in the list of bugs.

 

Matthias