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

Installation of Oracle SQL Developer 3.2 64-bit on Windows 7 64-bit

Environment: Oracle SQL Developer 3.2.09 64-bit, Java 1.6.0_34 64-bit, Windows 7 64-bit

Oracle recently released version 3.2 of SQL Developer. This new version has some interesting new features, like the possibility to administer the APEX Listener, the option to connect to databases in the cloud, and an improved DB Diff wizard. To read more about the new features, check this page.

There are both 32-bit and 64-bit versions of the software. I will explain here how you can install the 64-bit version on Windows 7 64-bit.

1) Download and install 64-bit Java 6 JDK

The 64-bit version of SQL Developer does not include a Java Development Kit, but you need a Java 6 JDK to run it. If you don’t already have one on your system, you can download one here: http://www.oracle.com/technetwork/java/javasebusiness/downloads/java-archive-downloads-javase6-419409.html#jdk-6u34-oth-JPR. I downloaded and installed jdk-6u34-windows-x64.exe, the 64-bit version of Java 6 Update 34 for Windows 64-bit.

2) Download and install 64-bit SQL Developer

If you have any previous versions of SQL Developer installed, do not remove them! You should only remove them after the new installation has finished successfully.

SQL Developer can be downloaded here: http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html. You need the “Windows 64-bit” version.

After the download, extract the “sqldeveloper64-3.2.09.30-no-jre.zip” ZIP file to a temporary folder and then copy/move the folder “sqldeveloper64-3.2.09.30-no-jre” to C:\Program Files. This is where Windows 7 by default installs its 64-bit programs and I prefer to keep it that way.

Now start SQL Developer for the first time by right-clicking on “sqldeveloper.exe” inside the “sqldeveloper” folder. It will now ask for the path to your 64-bit java.exe. Browse to C:\Program Files\Java\jdk1.6.0_34\bin\java.exe. This is the Java 6 JDK that you installed in step 1.

If you have any previous installations of SQL Developer installed, the new version should now show them and you will be given the possibility to import an old configuration. Might be handy to keep your connections and stuff!

SQL Developer should now start up fine. You can check the version under Help -> About.

When you have verified that everything works fine, you are safe to remove any older versions that you should have.

 

Have fun,

Matthias

 

 

 

How to modify the version of Java used for GlassFish 3.1.2.2

Environment: GlassFish 3.1.2.2 OSE Web Profile, Oracle Linux 6.3, Java 7 Update 7.

Whenever you upgrade your Java installation or install a new version, you have to make sure that your GlassFish installation is using the new version.

1) Download and install the new Java JDK 

New versions of Java can be found here. For GlassFish, you need to download the JDK (so not JRE), and you can use both Java 6 or Java 7.

For example, I downloaded and installed JDK 7 Update 7 to /u01/app/jdk1.7.0_07 and I created a symbolic link “java7” to it:

$ /u01/app/java>tar -zxf jdk-7u7-linux-x64.tar.gz
$ /u01/app/java>ln -sf jdk1.7.0_07 java7
$ /u01/app/java>ls -la
total 93904
drwxr-xr-x   4 oracle oinstall     4096 Sep 10 15:16 .
drwxr-xr-x   7 oracle oinstall     4096 Feb 17  2012 ..
lrwxrwxrwx   1 oracle oinstall       11 Feb  7  2012 java6 -> jdk1.6.0_30
lrwxrwxrwx   1 oracle oinstall       11 Sep 10 15:16 java7 -> jdk1.7.0_07
drwxr-xr-x  10 oracle oinstall     4096 Feb  3  2012 jdk1.6.0_30
drwxr-xr-x   8 oracle oinstall     4096 Aug 29 03:12 jdk1.7.0_07

2) Modify the GlassFish domain config file and JAVA_HOME environment variable

Next, you need to modify the AS_JAVA  variable in the GlassFish config file “asenv.conf”. In my case, this file was stored in the folder /u01/app/glassfish/glassfish/config. I changed it as follows:

AS_JAVA=/u01/app/java/java7

You also need to modify the environment variable JAVA_HOME. This variable can be found in the .bash_profile file of the user which is used to start GlassFish. Run “echo $JAVA_HOME” to see its current value. After the changes, log out and log in again (or run .bash_profile) to reset it.

3) Restart the GlassFish domain server

Restart the GlassFish domain server and any other server instances you might have.

/u01/app/glassfish/bin/asadmin stop-local-instance
/u01/app/glassfish/bin/asadmin stop-domain domain1

/u01/app/glassfish/bin/asadmin start-domain domain1
/u01/app/glassfish/bin/asadmin start-local-instance

 

HTH,

Matthias