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.
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.