Monthly Archives: December 2006

Backing up your applications

There have been a few posts on the Application Express forums lately from people who want to either export lots of applications or are looking for a way to automate the task of exporting applications.

Most (if not all) APEX developers are familiar with the application export functionality from within the Application Builder interface itself:

Application Export

However you may not be aware that tucked away in the Application Express install that you downloaded are a couple of utilities that you can also use to export your applications, infact the utilities can do much more than that, you can use them to export all the applications within a workspace or even all the applications within the instance.

If you have already deleted the Application Express installation files after successfully installing it, then you’ll need to download it again (available from here) because I don’t believe these files are available individually anywhere else.

The files can be found in the utilities subdirectory of the root installation directory, note that I’m using a Unix machine here (well actually an Apple powerbook), however due to the ‘portability’ of Java applications they will also work quite happily on other operating system, assuming of course that you follow the directions in the readme.txt file to setup your Java environment which if you already have Java installed should only involve setting up your Java CLASSPATH environment variable like this –

export CLASSPATH=.:${ORACLE_HOME}/jdbc/lib/classes12.zip

Obviously however you should take the time to read the full readme.txt file.
Root directory

There are two java utilities available for you to use APEXExport and APEXExportSplitter which both reside in the oracle/apex subdirectory.
APEXExport files

Executing the APEXExport program without any additional parameters will show you some default usage help.
APEXExport Command

As you can see, the usage is pretty intuitive, if I wanted to export application 103, all I would need to do is –

Exporting app 103

If we now look in the current working directory, you will find a file called f103.sql which you may recognise as being the same type of file which is produced by the export functionality in the Application Builder, in other words you can import this f103.sql file by using the Application Builder tools. You can also import the application by running the SQL script file whilst connected as your FLOWS user if you prefer to perform the import at the command line (or perhaps as part of a batch import).

We can also export all of the applications for a particular workspace, unfortunately we need to do a little bit of work here because the APEXExport utility requires the workspace id rather than the workspace name (it would be a nice feature if we could just pass in the workspace name instead), however it’s quite simple to get your workspace id by running the query

select v(‘WORKSPACE_ID’) from dual

in SQL Workshop whilst connected to your Workspace, this query will return a numeric id which you can then use with the APEXExport command, like this –
Exporting workspace apps
As you can see, it took around 2 seconds to export all 5 applications from my workspace, which is certainly quicker than doing it via the Application Builder interface. Also note that each application is exported into its own seperate file, rather than a single file.

All application exports

Lastly, you can also export all the applications in the entire instance, which I won’t show here since it should now be pretty obvious how that works.

You can do so many different things with this sort of commandline export functionality, for example you could quite easily create a Unix cron job to export your applications each night at midnight as an extra backup. Or, if you’re more adventurous you could schedule a job to export the applications, see if they’ve changed (using the Unix ‘diff’ command perhaps) since the last export and if they have then check them into your source control repository, you could also automate an export of your applications and email them automatically to a remote offsite location.

This kind of commandline scripting and control is why I love using Unix so much, with just a few simple commands I can automate so many of the manual tasks to make my life easier and it also means that I can be confident of also having a recent backup of my applications nearby if I should ever need them.

Next post I’ll show the APEXExportSplitter in action.