Automating APEX Export for SVN

The Problem with Manual Exports

A traditional manual export of an APEX application performed through the Application Builder isn’t arduous but it can be a bit of a hassle when it comes to tying into your version control system.  For me the biggest problems are ensuring consistent export settings, and the fact that the export is monolithic — identifying changes can be a real challenge.  So my short term goal right now is automating the export and split process, making our application a first class citizen in for version control and change management.

Automated Export & Split

In an effort to avoid false positives, I wanted to create an easy to execute batch wrapper for the Oracle APEX Exporter and APEX Splitter.  So my broad approach was to add to required JAR files directly into our repository, then create a few small batch scripts to handle the actual execution of the export.  I’m not familiar with Oracle’s precise re-distribution terms for the APEX Listener’s JARs or for the OJDBC6.  Our repository is only used internally, so I’m choosing to not view including the JARs in the repository as a re-distribution.

The advantage of placing the JARs in the repository is not having to manage the CLASSPATH on my co-worker’s machines.

Getting the JARs

For this attempt I’m using the APEX Listener 2.0 EA release, but this should work with the final release as well.  Download the listener from OTN, then you can extract the apex.war file, rename it to apex.zip, then extract the apex.war (apex.zip) contents into your repository.  In my case the APEX part of the repository looks like:

So, I’ve extracted the WAR file to APEX (the directory off the trunk to store exports) / APEX Export JARs.  This means I can run batch scripts from ./APEX with a CLASSPATH containing ./APEX Export JARs and know that Java will be able to find the required classes to run.

You’ll also need the ojdbc6.jar file from OTN

Common Batch Script

In our environment we have development and test (two instances on one server), and production (one instance on its own server).  I want to be able to use the same export command in the trunk and in the branches, so this common script sets up environment variables, and switches the server based on the instance provided (lines 16-18).

@ECHO OFF
REM So, the classpath is relative to the current directory
set CLASSPATH=.;.\APEX Export JARs\apex;.\APEX Export JARs\ojdbc6.jar
REM IF you know java.exe is in your path use
REM set javaexe="java"
set javaexe="C:\Program Files\Java\jre6\bin\java"
set devHost=yourdev.example.com
set prodHost=yourprod.example.com
REM On Windows 7 Java can default to using IPV6 and look like a firewall
REM problem Force IPV4
set ipv4=-Djava.net.preferIPv4STack=true
set prog=oracle.dbtools.apex.utilities.APEXExport

set/p db="Which DB Instance (dhr,thr,hr):"
echo db is :%db%:
if %db% == hr set "host=%prodHost%"
if %db% == thr set "host=%devHost%"
if %db% == dhr set "host=%devHost%"

echo Using host: %host%
REM The password will show up in your command history. Need better way
set/p password="Enter db password for %username%@%db%:"

echo Attempting export of app %app% (%alias%) from %db% as user %username%
set cmd=%javaexe% %prog% -db "%host%:1521:%db%" -user %username% 
set cmd=%cmd% -password %password% -application %app%
set cmd=%cmd% -split -outDir %alias% -skipExportDate -expSavedReports
REM Execute the command string
%cmd%
REM If run from Explorer, let user see output before exiting
pause

Specific Batch Script

This batch script provides a double clickable file, which sets up the application ID, parsing schema, and export directory before executing the dumpCommon.bat file shown above

@ECHO OFF
set username=hrdata
set app=1600
REM The name of the directory to store the export files in
set alias="HRER"

REM Setup environment variables, and perform instance->host mapping, 
REM run the java program
call dumpCommon.bat

Usability of the Split Up Export

For my team, the ability for any of us to create a branch, edit the application, and quickly perform an export that clearly shows where the changes were has been hugely beneficial.

Unfortunately, there appears to be a bug in this version of the ApexSplitter component.  If we attempt to install from Toad using the automatically created install.sql file (which @includes the individual files) there are problems where a split file may not be a complete block.  Right now I’m definitely seeing this in the last file in application_items which contains the start of the application_computations, and a begin without an end.

The export is usable in SQL*Plus, so that implies that Toad loads and executes each @include in sequence and independently, while SQL*Plus combines the files into a single stream before executing – so more like the C preprocessor.

Last File in $app_id/application/shared_components/application_items/

--application/shared_components/logic/application_items/pick_date_format_mask

begin

wwv_flow_api.create_flow_item(
  p_id=> 36212924296045411 + wwv_flow_api.g_id_offset,
  p_flow_id=> wwv_flow.g_flow_id,
  p_name=> 'PICK_DATE_FORMAT_MASK',
  p_data_type=> 'VARCHAR',
  p_is_persistent=> 'Y',
  p_protection_level=> 'S',
  p_item_comment=> '');

null;

end;
/

prompt  ...application level computations
--

begin

We haven’t yet experimented with selectively merging application export changes across branches. We just rely on the branch->trunk merge where we either merge the entire application, or back out.

Next Time

So, this double click to the repository is very nice, but there’s still a lot to be desired for integrating APEX into version control.  In my next post I’ll be talking about the formatting of long code blocks and the problems these present for version control in the APEX environment.