Long time ago, farmers had to manually milk cows with their bare hands. This was a tedious job that took a long time. Today, some systems are so sophisticated that cows come in, like a drive-thru, and get milked with no human intervention. How does this relate to Oracle APEX deployments? Unfortunately, a lot of organizations still manually export and deploy APEX applications when it can all be automated. This article will cover how to automatically export and import APEX applications via scripts rather than a manual process.

By far, the most common mistake I see with APEX deployments is the manual process. For most organizations, the process looks like the following:

• Once the development cycle is complete and tested, a developer will manually export the APEX application and check into a version control system.
• DBA then takes that file and manually imports the application into the Test, UAT, and Prod environments.

At first glance, this doesn’t seem too painful, and it really isn’t. Until you’ve done it many times and/or had to deploy to many environments. Ask any developer or DBA about their dislikes about APEX and this process is going to be somewhere at the top of the list.

Thankfully there are multiple solutions for this problem that most people don’t know about. The rest of this article will cover one of these solutions and will mention other options throughout the steps.

The Export

There are various ways to export the application via command line:

• SQLcl: This is Oracle’s replacement of SQL*Plus and is the easiest option as it doesn’t require any additional configuration or libraries.
• APEX Export Java Exporter: This is a Java class that is bundled as part of the APEX download. This option is a bit more complicated but does provide some additional options that SQLcl does not. For the purpose of performing releases, most people usually don’t need these additional features.

This article won’t cover the Java APEX Export option, however there are some articles by Nick Buytaert and Martin Giffy D’Souza that cover it. There’s even an open source Github project to help simplify the process.

Using the SQLcl option, run the following script (in SQLcl) and it will export App 100 into f100.sql:

set termout off

spool f100.sql

apex export 100

spool off

exit

If you bundle the above code into a script it can be easily added as part of an existing build script. It will export a the APEX application into the file f100.sql. This file will be referenced in the import process below.

The Import

Now all you need to do is connect to the database via SQLcl or SQL*Plus and run f100.sql, all from the command line. This makes it really easy to automate and include as part of your release process.

Before running f100.sql, there are considerations that must be addressed:

• Is your Workspace ID the same across all your environments?
  • This is important for preserving saved Interactive Reports (IR).
• Is your Application ID the same across all environments?

If you answered No to either of the above questions, then you must run an additional script before running f100.sql to compensate for the differences. In the past, this used to be complicated, but now there’s an API (APEX_APPLICATION_INSTALL) and some excellent examples that the APEX team has provided. Please review the documentation and modify according to your needs.

Overall, your release SQL script will look like this:

-- Optional

-- This is if you answered No to any of the questions above

@pre_release.sql

-- Install APEX

@f100.sql

Summary

Using the above information you can now export and import an APEX application from scripts and thus automate them. This process can be included as part of your existing automated build and release process. Have questions? Leave a comment below or Contact Us.