By: Martin D'Souza On: July 21, 2016 In: Oracle APEX Comments: 4

Oracle APEX Deployments: You’re Doing It Wrong

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


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?
  • 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


-- Install APEX




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.


    • Natarajan
    • May 18, 2017
    • Reply

    It is a great feature to use. This helped us recently as we wanted to move all applications from the development tier to the testing tier, and we created the individual scripts for the individual application, applied through a master script in the sql plus command window. Awesome.
    However, there are two down sides with this:
    1. It takes more time to import the applications than the manual process.
    2. It causes to loose the template subscriptions to the master templates. This is very bad for us as we dont want to loose it on the testing and production tiers. Any possible fixes for this? Thanks in advance.

      • Martin D'Souza
      • May 18, 2017
      • Reply

      1: Try using set termout off before importing the APEX application to see if it speeds it up from command line. This will disable all the prompt statements
      2: Why do you want subscriptions to master templates linked in Test and Prod? If you did want to keep them I’d suggest ensuring that the workspace IDs are the same across the different environments.

    • datRedHeadedGuy
    • July 21, 2017
    • Reply

    What about the Shared Components? How do we include those with the scripted import?

      • Martin D'Souza
      • August 03, 2017
      • Reply

      Can you please be a bit more specific? Shared Components are included as part of the export and thus the import.

Leave reply:

Your email address will not be published. Required fields are marked *