Using Scriptable DevOps to Save Time

While developing an APEX app, I check my latest changes into a Git repository after reaching a feature milestone. In this article I’ll explain how I took advantage of APEX’s scriptable devops features to automate multiple steps in my daily workflow that I had previously been doing manually. The result are two simplified commands:

  • apex2git – exports an APEX app to a Git work area, staging changed files
  • git2apex – imports an app from a Git work area to an APEX workspace

In case the scripts could be useful to you to study or use, I’ve included a link to download the source at the end of the article along with a short video that illustrates how I use these commands in my daily work.

My Workflow Before Optimization

When adding a new feature to an existing app, I used to do all of the following steps one by one:

  • pull the latest app version from remote Git repo to my local work area
  • create a Git branch named after the feature ticket I’m working on
  • import the app from my Git work area into my APEX dev workspace
  • implement the new feature using the APEX App Builder
  • export the enhanced app to a temporary staging directory (e.g. /tmp/f1234_stage):
    1. as a single SQL file (e.g. f1234.sql), and also
    2. as individual component SQL files organized into directories, and also
    3. as readable YAML for easy diffing
  • copy new f1234.sql file to Git work area as my-app-name.sql
  • review the diffs between temp dir files and those in the Git work area
  • cherry-pick changed files from the temp dir and copy to Git work area
  • add the changed files to the Git staging area
  • commit the staged changes to the local Git repo
  • push the changes up to the remote team Git repo
  • initiate a merge request for my branch’s changes

That’s a lot of steps. Luckily I was able to simplify some of them as I learned a few new tricks.

Git Automatically Ignores Unchanged Files

The first optimization opportunity I discovered was a happy accident. Prior to this revelation, I was using a tool — Beyond Compare on Windows or Kaleidoscope on Mac — to compare the APEX export files in the temporary directory with corresponding files in the Git work area to identify which ones my feature work had changed. Then I would carefully copy only the changed files over to the Git work area.

Once, by mistake, I copied all of the files from the temp directory to the work area, and even mistakenly added them all to the Git staging area. After doing this unintentional blunder, I typed git status . To my surprise, despite all the files having a later timestamp, Git had automatically ignored adding to the staging area any file whose logical contents had not actually changed! This productivity gift meant I could simply bulk copy all the exported files to my work area without manually cherry-picking the changed files myself. Bravo, Git!

Using SQLcl to Export APEX Apps

While exporting and importing APEX apps in the browser is straightforward, I researched how I could do it from the command line instead. This felt like an enabling ingredient to automating more of my common workflow. I learned that SQLcl supports the apex export command that looked promising.

Through a series of trials, I converged on the following command that, given an application’s numeric id (e.g. 1234), would export it as a single SQL file into a target directory (e.g. /tmp/f1234_stage), including supporting objects, using original component ids, and skipping the inclusion of the export date. While the command is intended to be entered as a single line, I’ve formatted it below for better readability: 

$ sql appuser/password@host:port/servicename
SQL> apex export -applicationid 1234
                 -dir /tmp/f1234_stage
                 -skipExportDate
                 -expOriginalIds
                 -expSupportingObjects Y 
                 -expType APPLICATION_SOURCE

The result of running the apex export command above is the single file f1234.sql containing the entire application source for application 1234, and this file gets created in the /tmp/f1234_stage directory.

The option to include supporting objects is self-documenting, but it’s important to understand the other two. Excluding the export date ensures component files with no other changes don’t differ from the existing version only due to the date on which they were exported. Using original ids maintains internal id numbers of components as teammates import the app and export it again from other APEX workspaces. Failure to use either of these two latter options could lead to unnecessary differences in files. Of course it’s also important to follow the best practice of using the same workspace id and be running the same APEX release (including patch set!) across different environments like Dev, Test, Prod, and private development. Failure to follow this best practice can cause uninteresting differences in the component files due only to the different workspace id values or different patchset levels.

While the single SQL file is useful for one-click installation, I also want to retrieve the individual application component SQL files organized into a directory hierarchy. This makes it easier to understand what components have been added, changed, or removed with each new feature. This requires adding the -split command line option. So the command to do an application export split into separate component SQL files is:

SQL> apex export -applicationid 1234
                 -dir /tmp/f1234_stage
                 -skipExportDate
                 -expOriginalIds
                 -expSupportingObjects Y 
                 -expType APPLICATION_SOURCE
                 -split

This results in a directory named f1234 containing all the split SQL component files organized into subdirectories, and the f1234 directory gets created inside the /tmp/f1234_stage directory.

Starting with APEX 22.1, the readable YAML export option allows much easier code reviews since it presents the application component metadata in an easy-to-understand text outline with property names and values that match what you see as a developer in the APEX builder. To export the readable YAML version of an app, we can use the new READABLE_YAML export type:

apex export -applicationid 1234
            -dir /tmp/f1234_stage
            -skipExportDate
            -expOriginalIds
            -expSupportingObjects Y 
            -expType READABLE_YAML

This results in a directory named readable containing readable YAML files for application artifacts organized into subdirectories, and the readable directory gets created inside the /tmp/f1234_stage directory.

Using SQLcl 22.1 against an APEX 22.1 workspace schema, it’s possible to include multiple export types in the same export command by using a comma-separated value for the -expType command line option. So I was able to combine the export of the “split” application component SQL and the readable YAML into a single command like this:

apex export -applicationid 1234
            -dir /tmp/f1234_stage
            -skipExportDate
            -expOriginalIds
            -expSupportingObjects Y 
            -expType APPLICATION_SOURCE,READABLE_YAML
            -split

This results in a directory named f1234 containing both the readable directory of the readable YAML files as well as all the split SQL component files organized into appropriately-named subdirectories, and the f1234 directory gets created inside the /tmp/f1234_stage directory.

Automating APEX to Git Export

The recipe I identified to export an APEX app with id 1234 into the Git work area for repository my-app in my home directory includes these steps:

  1. Create a temporary staging directory /tmp/f1234_stage
  2. Use SQLcl to apex export app 1234 to this staging directory
  3. Copy /tmp/f1234_stage/f1234.sql to ~/my-app/my-app.sql
  4. Deep copy /tmp/f1234_stage/f1234 to ~/my-app
  5. Add all files to Git staging area (Git includes only ones that really changed)

Since I was already familiar with Apache Ant for build automation, I used it to implement the recipe above and created an apex2git shell script and Windows batch file that simply invoke the Ant build script, passing along the command line arguments for application id, repo directory name (assumed relative to my home directory), and database connection string:

$ apex2git 1234 my-app appuser/password@host:port/servicename

Since it is common for a larger APEX application to be assembled out of several smaller applications, I implemented the Ant build script to accept the name of the Git repo directory or a subdirectory. So, for example, if you were implementing an application called MediPay that was broken down into a backoffice application and a mobile application, you might export the two distinct applications into distinct subdirectories of a medipay Git repo directory like this:

$ apex2git 1001 medipay/backoffice $MEDIPAY_CONNECTION
$ apex2git 1002 medipay/mobile     $MEDIPAY_CONNECTION

In this case involving a subdirectory, the Ant build script first converts the directory separator characters \ or / to a hyphen to end up exporting the more meaningful application export SQL scripts into:

~/medipay/backoffice/medipay-backoffice.sql
~/medipay/mobile/medipay-mobile.sql

Using SQLcl to Import an APEX App

Importing an APEX application from the command line involves calling the apex_application_install package to set the target workspace name, the target application id, and preference related to the installation of supporting objects, followed by running the application’s single-file SQL script.

So, for example, to import application 1001 from the medipay/backoffice Git repo directory into workspace medipay , we can using SQLcl’s sql or sqlplus to run the following PL/SQL block followed by the application’s SQL script:

begin
   apex_application_install.set_workspace('medipay');
   apex_application_install.set_application_id(1001);
   apex_application_install.set_auto_install_sup_obj(
                               p_auto_install_sup_obj => true );
end;
/
@/home/smuench/medipay/backoffice/medipay-backoffice.sql

Automating Git to APEX

The recipe I identified to import an APEX app with id 1234 from Git work area my-app in my home directory into workspace target-wksp includes these steps:

  1. Create a temporary staging directory /tmp/f1234_stage
  2. Copy ~/my-app/my-app.sql to directory /tmp/f1234_stage/f1234.sql
  3. Use SQLcl to set install options, then run /tmp/f1234_stage/f1234.sql

Again I employed an Ant build script to carry out these steps. Then I created a git2apex shell script and Windows batch script that invokes the Ant build script, passing along the command line arguments for application id, repo directory name (assumed relative to my home directory), target workspace name, and database connection string:

$ git2apex 1234 my-app target-wksp appuser/password@host:port/servicename

As before, to accommodate solutions comprising multiple smaller APEX apps, I implemented the Ant build script to accept the name of the Git repo directory or a subdirectory. So, for the MediPay application comprised of a backoffice application and a mobile application, you might import the two distinct applications from distinct subdirectories of a medipay Git repo directory into a workspace named medipay like this: 

$ git2apex 1001 medipay/backoffice medipay $MEDIPAY_CONNECTION
$ git2apex 1002 medipay/mobile     medipay $MEDIPAY_CONNECTION

Ant: Using Templatized SQL Scripts

The apex2git command runs a apex2git.xml Ant build script that copies an apex2git.sql template SQL script to the temporary staging directory, substituting @APPID@ and @DIR@ tokens in the process using the values of the application id and repository directory parameters passed in:

REM apex2git.sql - v1.0
apex export -applicationid @APPID@ -dir @DIR@ -skipExportDate -expOriginalIds -expSupportingObjects Y -expType APPLICATION_SOURCE
apex export -applicationid @APPID@ -dir @DIR@ -skipExportDate -expOriginalIds -expSupportingObjects Y -expType APPLICATION_SOURCE,READABLE_YAML -split

Then it runs the resulting SQL script using SQLcl’s sql command against the provided database connection to produce the single SQL file, multiple component SQL file, and YAML exports in the temporary staging directory. It then proceeds to copy the exported files to the Git work area and add them all to the Git staging area.

Similarly, the git2apex command runs a git2apex.xml Ant script that substitutes command-line parameters into the following git2apex.sql template SQL script and then uses sqlto run it against the provided database connection.

REM git2apex.sql - v1.0
REM First setup the import options
begin
   apex_application_install.set_workspace('@WORKSPACE@');
   apex_application_install.set_application_id(@APPID@);
   apex_application_install.set_auto_install_sup_obj( p_auto_install_sup_obj => true );
end;
/
REM Then install the application by running its SQL script
@@DIR@/f@APPID@.sql

My Streamlined Workflow

Using the apex2git and git2apex scripts, I’ve streamlined my development workflows. When working alone on an APEX app, I tend to eschew branches and just push my changes directly to the repository, accompanied by a descriptive commit message that explains what new feature I’ve implemented:

  • pull the latest app version from remote Git repo to my local work area
  • import the app to my private dev workspace using git2apex
  • implement the new feature using the APEX App Builder
  • export the enhanced app to Git using apex2git
  • commit the staged changes to local Git repo mentioning feature I finished
  • push the changes up to the remote Git repo

In contrast, when working with others, I use feature branches and Git pull requests to facilitate code review by my peers:

  • pull the latest app version from remote Git repo to my local work area
  • create a Git branch named after the feature ticket I’m working on
  • import the app to my private dev workspace using git2apex
  • implement the new feature using the APEX App Builder
  • export the enhanced app to Git using apex2git
  • commit the staged changes to the local Git repo
  • push the changes up to the remote team Git repo
  • initiate a merge request for my branch’s changes for peer review

Checking Out the Scripts

In case you want to explore the scripts to see how they work, you can download them from here. Unzip the archive in your home directory, and make sure that the ./utils subdirectory it creates is in your system path. To keep things simple, the scripts currently rely on having all the apex2git* and git2apex* files in this utils subdirectory of the currently logged-in user’s home directory. Of course, you’ll also need to have SQLcl 22.1 and Apache Ant installed and have both sql and ant commands be available in your system path.

The simple usage documentation for the scripts is:

git2apex appId repoDirectory workspaceName connectionString

apex2git appId repoDirectory connectionString

The repository directory is interpreted relative to your home directory. You can see the commands in action in the short example video below.

Example of git2apex & apex2git in Action

In this short video, I show using git2apex and apex2git without using a feature branch for simplicity. This is the process I use when working alone on an APEX app that I source-control in a private Git repository up on GitHub. For example, the donor-tracking system I built for a local Italian non-profit and several personal productivity apps follow this approach. When working on sample apps as part of my day job at Oracle, we use an internal Git repository instead of GitHub for that. The git2apex and apex2git scripts work the same regardless of whether the git repo is only on your machine or whether it’s remote.

Next Stop: CI/CD Pipelines

APEX’s scriptable devops features let you create custom solutions to automate frequent tasks in your app development lifecycle. We’ve seen a simple example of their use in this article with apex2git and git2apex commands. My colleague Carsten has also documented a technique for exporting and importing APEX applications using a REST service in scenarios where that approach proves useful.

These options make it straightforward to incorporate APEX application export and import into more involved “pipelines” of continuous integration and continuous delivery. These automations are often triggered automatically by the merging of new code to the main branch of your Git repository, and can include steps that setup test data, execute unit tests and integration tests, and finally install a validated build of your application into a target environment if all tests pass. In future releases of Oracle APEX, we aim to make doing some of these steps even simpler from directly inside the builder. However, it’s good to know that in the meanwhile any kind of build automation is already possible to achieve with the ingredients APEX provides today.

%d bloggers like this: