a development team dedicated to Oracle APEX

APEX Deployment Script

with 2 comments

We are used to working in more formal coding environments than our in-house Oracle/Apex development setup, for which we have a dedicated Oracle server and no full-time DBA. When we had to start making periodic application releases to a remote live server, we expected to be able to write a script that would do the release automatically. It proved a little more difficult than we thought it would – we needed a PL/SQL package, a PERL script, and an APEX command line utility. If you’re faced with the same problem, here’s how we did it.

We used Windows servers for this project, so the final product was two Windows batch files – however it wouldn’t be difficult to write PERL or shell scripts using the same methods.

Of course you will need to customise the batch files and possibly the PL/SQL package for your own environment, and you should test the deployment process to destruction before you let it loose on a live server.

Why did we need an Apex deployment script?

We were coming to the end of a large Apex project developed and to be run on Microsoft Windows Servers. The data was in a single dedicated Oracle workspace but there were three separate Apex applications and 17 PL/SQL packages amounting to nearly 30,000 lines of code.

There were three dedicated Windows Oracle servers – a development server at our office and staging and live servers at a remote co-location facility. We were making formal code and Apex applications releases from the development server to the staging server for user acceptance testing, then releasing from the staging to the live server when the system was approved. Each version of the project had to be retained so that releases could be rolled back if there was any problem.

The manual process
We started off doing both deployments (development->stage and stage->live) manually.
For development->stage:

  1. Export each of the 3 Apex applications from the development server to a text file with a name that represents the new version number, then import it into the staging server.
  2. Copy each PL/SQL package from the development to the staging server, taking care not to overwrite sections of code that are site-specific; that includes SMTP server names and addresses, sender email addresses, and database links. We did this using cut and paste (carefully) between PL/SQL developer windows.
  3. Save the current state of the PL/SQL packages under the new version name – we used Subversion for this.
  4. Copy the style sheet and images from a directory on the development server to one on the staging server. It was easy to forget this step.

This process was complicated, time-consuming, and inherently risky – after several Apex versions had been generated and saved on disk it was easy to pick the wrong one for import. It was also essential to know how to edit PL/SQL code so that the site-specific code sections were retained – which meant that only a programmer could make a deployment.

The automated solution
Our goal was a single script (ie a Windows batch file) to run on the originating server that would export a project version in the form of SQL scripts to the target server, and a single script to run on the target server to import the new version.

A sample export batch file can be found here. It relies on a PL/SQL package, a PERL script, and the APEX utility APEXExport.class, which is in the standard APEX 3.1 distribution under apex\utilities\oracle\apex.

To run this, you’d save it as a Windows batch file (.bat or .cmd extension) on the source Oracle server then invoke it from the Windows command line with the version number as the only parameter. The source and destination file locations, and the numbers of the Apex applications to be deployed are hard-coded in the batch file. The names of the packages, functions, and procedures to be deployed are package-level constants in the PL/SQL package. The name of the Oracle directory for output PL/SQL code is also a package-level constant. The directory name and its physical path on disk needs to be set up (once) from Oracle – see below.

Here’s a corresponding sample import batch file. You’d save it as a Windows batch file on the target Oracle server then run it with the same version number as was given to the export batch file.

And here’s some more detail about how it works:

1. Exporting PL/SQL packages, functions and procedures (your code)

Where to write the export file containing your code
Oracle can write to external files by means of the UTL_FILE package. In order to use this, we needed to define an external directory on the Oracle server:

Where to get your code from
The standard Oracle view USER_SOURCE contains all the package, function, and procedure code visible to the current user. To turn the code into executable SQL scripts we just needed to surround it with “CREATE OR REPLACE” and “/”.

2. Site-specific code

We used a simple mechanism for identifying conditional code sections. Each such section was given a start and finish label on a separate Oracle commented line. We arbitrarily chose ‘!~’ and ‘~!’ as begin and end delimiters for the labels because those strings didn’t occur anywhere else in our code.

The labels consisted of two parts separated by an underscore(_): an identifier that specifies the server for which the code section should be live, and the word BEGIN or END. In the exported code the relevant section for the target server is uncommented and all other sections remain commented out.

Thus a package specification could contain the following on a development server:
smtp_host CONSTANT VARCHAR2(256) := 'smtp.developmentdomain.com';
smtp_host CONSTANT VARCHAR2(256) := 'smtp.livedomain.com';
Which would be rendered on a live server as:
smtp_host CONSTANT VARCHAR2(256) := 'smtp.developmentdomain.com';
smtp_host CONSTANT VARCHAR2(256) := 'smtp.livedomain.com';

3. Exporting Apex Applications

Apex comes with a command-line export tool written in Java. This produces a SQL script that can be directly invoked from SQL*Plus to reimport the application. It was not easy to make the export tool run, but after some trial and error, we found that (at least in our setup) the CLASSPATH environment variable needed to point to both the Oracle Java library and the location of the Apex export script.

To import an exported application into a different server from the one that exported it, the security group id needs to be changed to that of the target server. That involves rewriting the security group id in the following line of code:

We did this by processing the output Apex script with a simple PERL script. Instead of using a literal security group ID to replace SomeBigNumber, we rewrote this line as
wwv_flow_api.set_security_group_id(p_security_group_id=> apex_util.find_security_group_id(‘MY_WORKSPACE’));

That allowed the import to work on any server with the correct workspace name.
Of course you can use any other text substitution tool to do the replacement instead of a PERL script, as long as it can be invoked from the command line.

4. Versions

This should work on any recent version of Oracle and Apex, but it has been tested only on:
Apex 3.1.2

Additional Software

Java is likely to be already installed on your Windows server, but PERL may not be.

We used ActiveState ActivePerl

32-bit Windws download:

64-bit Windws download:

Java for Windows (currently Version 6 Update 11) download is available from:

Written by Roger

11 February, 2009 at 5:18 pm

Posted in Uncategorized

Tagged with , , ,

2 Responses

Subscribe to comments with RSS.

  1. I’m surprised that you don’t build your releases from a version control system – how do you cope with new development, bug-fixes, etc.?


    9 August, 2014 at 11:14 pm

    • Yes – if we were starting from scratch now that’s what we’d do. We do use Subversion to keep a record of the PL/SQL code in each release. But to release all aspects of an APEX application into a version control system you need to save the application, supporting objects, and all the images and JavaScript files. It would be a lot of extra work to write scripts to a) save all that into version control and b) release to a target system from the saved version control files. In our case we had a team of 2 developers, and I was both the only PL/SQL developer and the development and test DBA. It made more sense in the time available for the release process to be “create a target system that is a clone of the development system”, which is what our process does. Each release we make results in a version numbered directory containing the installation scripts, so there is some built-in version control as well as a rollback mechanism.

      only one of whom wowrites PL/SQL
      correctly you need to commit
      You need to do quite a lot of extra work
      but we’re a tea have a tiny with only 2 APEX developers and one PL/SQL


      11 August, 2014 at 10:46 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: