21Jul
Apex Deployment Cow
By: Martin D'Souza On: July 21, 2016 In: Oracle APEX Comments: 9

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

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

9 Comments:

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

    • Fritz
    • September 10, 2017
    • Reply

    Is it also possible to split the export file with sqlcl?

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

      Hi Fritz, to my knowledge you can’t split APEX files via SQLcl. I think this is a good idea and have suggested it to the team. Thanks.

    • Jeff
    • November 07, 2017
    • Reply

    Another downside I just ran into: If you have interactive reports where users saved public named report definitions, those do not get included in the sqlcl export. (neither do private saved report definitions but I expected that). So I guess back to the java ApexExport for now unless someone knows of a setting that can make that happen in SQLcl.

    • David
    • November 09, 2017
    • Reply

    Greetings,
    Tried to export my Apex app using SQLcl and encountered the following error. I should mention the application exists in a schema I created in my Oracle instance, and then created a new APEX workspace that points to it. The application runs fine, but for some reason I cannot seem to connect or login to it using the same credentials I used to get into the workspace and run the application with. The only username that seems to let me connect is SYSTEM, but then that does not find my app and export it; although the export command runs the resultant SQL file I spool to is empty. Any help is appreciated.

    C:\Users\CNOMNQ\Documents\TechSW\OraSQLcl\sqlcl-17.3.0.271.1943-no-jre\sqlcl\bin
    >sql
    SQLcl: Release 17.3.0 Production on Tue Nov 07 16:39:56 2017
    Copyright (c) 1982, 2017, Oracle. All rights reserved.
    Username? (”?) ESCUSER
    Password? (**********?) *********
    USER = ESCUSER
    URL = jdbc:oracle:oci8:@
    Error Message = no ocijdbc12 in java.library.path
    USER = ESCUSER
    URL = jdbc:oracle:thin:@localhost:1521/orcl
    Error Message = Listener refused the connection with the following error:
    ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
    USER = ESCUSER
    URL = jdbc:oracle:thin:@localhost:1521/xe
    Error Message = ORA-01017: invalid username/password; logon denied
    Username? (RETRYING) (‘ESCUSER/*********’?) SYSTEM
    Password? (RETRYING) (**********?) *********
    Connected to:
    Oracle Database 11g Express Edition Release 11.2.0.2.0 – 64bit Production
    SQL> set termout off
    SQL> spool escbkup.sql
    SQL> apex export 106
    SQL> spool off
    SQL> exit
    Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 – 64bit
    Production

Leave reply:

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