In the root of the Oracle APEX installation files, you will find a utilities folder containing a readme.txt file. The file provides detailed instructions on how to set up and use the APEXExport utility.

Pre-requites for the utility include installation of the Java Development Kit (JDK) of version 1.5 or greater, and the inclusion of classes12.jar in the CLASSPATH.

I wrote earlier that we should store "everything to do with the project" in our SVN repository, so that with a single checkout we could do a full build of the system. Also raised was the possibility of exceptions to this rule for things that are large, or complicated to install and stable, for example, the database and IDE tools such as JDeveloper.

Let's make an exception by assuming the JDK is already installed on your computer—either as a standalone installation, or as part of an IDE for example in SQL Developer or JDeveloper. If you didn't want to make this exception, add SQL Developer (which includes the JDK) into your SVN repository.

The classes12.jar file is the Oracle JDBC library for Oracle database 10g, found in the %ORACLE_HOME%\jdbc\lib directory. For Oracle database 11g, the equivalent file is ojdbc5.jar for Java 5 or ojdbc6.jar for Java 6. Generally for Oracle database 11g, you should use ojdbc6.jar.

Because developers may have very different setups on their computers, even within a small team, the easiest way to manage the location of the JDBC library is by using the Oracle Instant Client. The Instant Client allows you to run your applications without the standard Oracle client, and includes additional libraries for SQL*Plus.

Instant Client comes in two versions—Basic and Basic Lite. Both versions are suitable for Oracle APEX: Basic Lite is a smaller version of the Basic, with only English error messages and supporting only specific character sets, including AL32UTF8, used by Oracle APEX. Installation is simply extracting the Instant Client files into a directory. Download a copy of Instant Client for your database version and operating system from http://www.oracle.com/technology/tech/oci/instantclient/index.html.

Also download the SQL*Plus Instant Client, which is installed by extracting it into the same directory as the Instant Client.

Before we go any further, let's take a look at the intended layout of the SVN repository.

The preceding screenshot shows the layout of my SVN repository. You can see that within the trunk folder of the apex-solutions/playpen project, I've created a series of folders to partition my application logically. The bin folder holds my batch scripts, including the backup_apex.bat script, detailed shortly. Also, note the oracle folder, which contains Oracle Instant Client software for database releases 10.2 and 11.1. And finally, the utilities folder to which I've copied the APEXExport.class and APEXExportSplitter.class from the Oracle APEX installation.

Let's look at the code for backup_apex.bat:

@echo off
setlocal
REM Set BASE to parent directory of this scripts location.
set HOME=%CD%
cd /d %~dp0
cd ..
set BASE=%CD%
cd /d %HOME%

The setlocal command ensures any environment changes are localized to the batch script. Setting the HOME variable allows the script to return to the execution start directory.

Next, navigate to the script location using the convoluted expression cd /d %~dp0, and in turn to its parent directory to set our BASE variable to be the "root" folder of our repository. For example, my repository path to the backup_apex.bat script is C:\playpen\bin\backup_apex.bat, so my BASE variable becomes C:\playpen. Your repository path could be a completely different, but provided your script finishes with \bin\backup_apex.bat, the BASE variable will be set correctly.

REM ----------------------------------------------------------
REM Database 11g specific
REM ----------------------------------------------------------
set CLASSPATH=%CLASSPATH%;%BASE%\oracle\instantclient_11_1\ojdbc6.jar
set CLASSPATH=%CLASSPATH%;%BASE%\oracle\utilities
REM ----------------------------------------------------------
REM Database 10g specific
REM ----------------------------------------------------------
REM set CLASSPATH=%CLASSPATH%;%BASE%\oracle\instantclient_10_2\classes12.jar
REM set CLASSPATH=%CLASSPATH%;%BASE%\oracle\utilities

Next, we set the CLASS_PATH to our JDBC drivers and export utilities. I'm using the Oracle JDBC library for Oracle database 11g, with the equivalent Oracle 10g version commented out.

cd /d %BASE%\database\apex
REM Make sure our local copy is up to date
if not ("%SVN_HOME%") == () "%SVN_HOME%"\svn update

We change folders, as the APEXExport utility exports files into the current working directory.

Before running the export, we update our repository, refreshing the current directory and subdirectories using the command-line version of SVN. In this case, I'm referencing an externally defined environment variable SVN_HOME to identify the home directory of the SVN command line client. SVN_HOME is usually defined as a Windows environment variable when the SVN command line client is installed.

Using conditional logic, allows this step to be skipped if the variable has not been set for your computer.

java oracle.apex.APEXExport -db mark-pc:1521:XE -user playpen -password playpen -workspaceid 1038420889063720 -skipExportDate

APEXExport allows you to either export an individual application, a workspace, or the entire Oracle APEX instance. The utility is run through Java using a JDBC connection to the database; for complete syntax details refer to the readme.txt file included in the utilities folder.

In this batch script here, I am exporting a workspace. To find out the workspace ID for your environment, you can run the following query in SQL Workshop within Oracle APEX:

select v('WORKSPACE_ID') from dual

Exporting a workspace will create a separate script for each application in your workspace. So for application 150, a file named f150.sql will be created.

REM Check if SVN_HOME has been set
if ("%SVN_HOME%") == () goto :no_svn_home
"%SVN_HOME%"\svn add *.sql --force
"%SVN_HOME%"\svn commit -m "Automated backup and check in."
goto exit
:no_svn_home
echo ERROR
echo ERROR: SVN_HOME environment variable is not set, no automated SVN check in.
echo ERROR
goto exit
:exit
endlocal

Once the APEX application exports have completed, we check the export files into SVN. We start by seeing if the SVN_HOME environment variable is set; if not, skipping the check-in and raising an error message. If set, the svn add command is used to ensure that when any new applications are added to the repository, then the svn commit command is executed, with a required check-in message.

SVN detects whether or not files have been modified as part of the check-in process, so if no changes have been made to an application, it won't be checked in. The following screenshot shows the output of the batch script, where five applications are exported from the database, but only one had been modified, so only that application was transmitted to the SVN repository.

Now that we have our batch script set up, the last step is to schedule the script to be run automatically every day. Ideally, this would be configured to run on the SVN server; however, you could run the script from team members' computers, because as we've just seen in the previous screenshot, only modified applications are committed to the SVN repository.

Scheduling the batch file to run automatically each day is simply a matter of calling the batch script, using the Windows built-in scheduler to define when and how often you want the batch file to run, as shown in the following screenshot:

As developers, we work to automate processes for end-users; yet, many of us overlook opportunities to automate our own development processes. Using a single source repository for all your application assets and using simple scripts as we have here to schedule repetitive tasks are the first steps in a journey towards continuous integration.

Version control systems, including Subversion, include hooks, so that the act of checking a file into the repository triggers an event to execute your hook program or batch file. So in an APEX application context, you could automatically FTP your web assets to your web-server. Or checking in a JavaScript file may trigger a process to minify and combine your JavaScript files into a single application JavaScript file, something we will cover in Chapter 11, Performance tuning your JavaScript.

Oracle SQL Developer 2.1 now includes a unit testing framework that allows you to build a set of sequential steps to create test cases for testing your PL/SQL code. Along with providing a GUI interface within SQL Developer to run unit tests and suites, a command line interface is provided for both Windows and Linux. Here, you could schedule a process to run your unit tests overnight, and the next morning check a unit testing report to verify the results.

Other opportunities for automation include generating documentation, website pages, statistics, and distribution files.

Automating your build process can use sophisticated Continuous Integration tools, which may require significant initial setup, or grow organically starting with small and humble beginnings using command scripts as we've done here.