APEX

A couple of days ago I found a blog that nicely documented how to set up the ability to export Apex application definitions from the command line. The author mentioned that it would be nice to set up a script that performed the exports automatically and checked for changes. However, he left that as an exercise for the user.

I really wanted this type of capability to help ensure I never lose my apps, and incidentally have a backout option if I were to ever make a change to one of my apps that really trashed it. Ergo I wrote a unix shell script the exports all of the applications in my workspace. It then compares each of the resulting SQL files with copies on the NAS drive where I keep my database exports, RMAN backups (and now Apex exports). If any of the files are different, it takes the existing copy in the main Apex directory on the NAS drive, appends the current date to it, and then moves it down one level into the 'archive' directory and gzips it to save space. The current SQL file for the application is then copied into the main directory on the NAS drive. I have a cron job that runs this every morning Monday through Saturday.

Anyway -- the resulting script is generic enough that I thought someone else might like to use it. Change the paths in the following script (plus the Apex username/password/workspaceid) and it should work for anyone:

#!/bin/bash
###########################################################
#
# Export APEX Application definitions to NAS drive.
# Compare to previous exports of the given app. If there
# are differences, then archive a dates copy and store
# the new copy. Delete any unchanged exports.
#
###########################################################

# Set up environment
PATH=$PATH:$HOME/bin
ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
ORACLE_SID=XE
export ORACLE_HOME
export ORACLE_SID
export PATH=$ORACLE_HOME/bin:$PATH
export CLASSPATH=.:${ORACLE_HOME}/jdbc/lib/classes12.zip:${ORACLE_HOME}/jdbc/lib/ojdbc14.jar

# Export all of the APEX Application definitions
cd /usr/lib/oracle/xe/utilities/
java oracle.apex.APEXExport -db oracledev.csc-orlando.com:1521:XE -user [USERNAME]
-password [PASSWORD] -workspaceid [really long number]


# Compare the new exports with the copies on the NAS drive
# If there are any differences, date and archive the NAS version
find /usr/lib/oracle/xe/utilities/f*.sql -print | \
while read file
do
FILE_NAME=`basename $file`
file2=/home/oracle_backup/development/apex/"`basename $file`"

grep -v "-- Date and Time:" "$file" > file1.tmp
grep -v "-- Date and Time:" "$file2" > file2.tmp


FILE_DIFF=`diff --brief file1.tmp file2.tmp`

if test "$FILE_DIFF" != ""; then
NEW_FILE=$FILE_NAME.`date +%d_%b_%Y`
mv $file2 /home/oracle_backup/development/apex/archive/$NEW_FILE
mv "$file" "$file2"
fi
done


# Delete all the exports just made
rm f1*.sql
rm fil*.tmp