This blog will get you started with APEX CI/CD aligned to the APEX Technical Paper.

If you have not already read the Oracle APEX Application Development Lifecycle Technical Paper I suggest you read it now (start at page 17) to become familiar with the approach.

I've written this blog to complete what I believe are missing steps between the Technical Paper explaining that CI/CD can be achieved and the actual steps required to achieve it.

In this blog, we're going to set this all up on Oracle Cloud as it's the most convenient place to deploy a generic solution.

What is this?

I've prepared a test repository pretius-apex-lifecycle-aligned-sample-customers which is a derivative work of the APEX Sample Customers Application, built to demonstrate the alignment to the Oracle APEX Application Development Lifecycle Technical Paper. The repository deviates somewhat from the Technical paper to incorporate some advancements in thinking as mentioned in my Deconstructing the APEX Application Development Lifecycle Paper presentation (see page 7). All deviations from the Technical Paper are mentioned in the README.md

As part of the Getting Started section, you'll acquire a Jenkins Instance and a docker image containing Oracle23c+ORDS+APEX. All instructions are in this guide.

Using the utilities provided in the pretius-apex-lifecycle-aligned-utils repository, you will build a Jenkins server to deploy & test the Sample Customers Application, before removing the container and collating all the test evidence.

It is the intention that you can take the ideas from this work and adapt your repository to this method, adding or removing steps as appropriate.

Please see the suitability as an approach section in this blog before implementing these techniques as some issues were found that you should be aware of.

This blog was written with this quote in mind from the Overview of Team-Centric Development Approach section of the Oracle APEX Application Development Lifecycle Technical Paper:

"Most developers on the team don't need to directly interact with the file artifacts since another team member playing the role of a build manager adds their work to the Git repository."

This quote is important as you'll see in the suitability as an approach section.

Therefore this blog has been designed so that the 'Build Manager' has already constructed a build zip file and this will be deployed and tested by this method.

Oracle APEX CI/CD in Action

This picture shows all the steps performed in the Jenkins pipeline. As you can see it takes around 8-9 mins to complete.

Following the build, the following artifacts are stored:

1. The zip used to deploy the build

2. Video evidence, from Cypress, of the application working. This test is just logging in and out of the application - however, this test case can be adapted to your tests.

   

3. utPLSQL Tests (not pictured due to this bug in SQLcl 23.1) in JUnit XML format.

Build Fundamentals

All steps are explained in the utils repository here. However, to emphasize some key points.

1. The docker image is a bare minimum, with no schemas or APEX workspaces. Therefore it's treated as a golden image for purposes of testing the deployment of any APEX DB application.

2. The approach in the Technical Paper does not reference the exported application's parsing schema, workspace, or application ID. Therefore Those values are required to be extracted from the split APEX files and used in the pipeline. Application Alias, incidentally, is not exported and therefore is held within a pipeline configuration. These of course could be changed see the Adaptation to your Project > Configure the Pipeline section of this blog.

3. Typically other schemas are often used in an APEX application, these can be exported, using SQLcl's lb genschema command, into the /other_schemas folder, with each sub-folder named after the schema name. In the petius-apex-lifecycle-aligned-sample-customers repository, logger has been exported as a demonstration of this capability.

4. Liquibase cannot create DB users, therefore all DB users (i.e. the parsing schema & all /other_schemas sub-folder names) are created in advance with core grants. Note these users are only for deployment tests & the environment is destroyed following the build.

Getting Started

You must have the environment set up by following the steps in the Oracle APEX CI/CD: Initial Setup blog & you are now returning to this blog to complete the configuration.

Configure a Jenkins Pipeline

This section hooks up the demonstration pipeline to the environment setup work you have recently completed.

1. Jenkins > Dashboard > New Item
   Item Name = Sample-Customers-CICD-Pipeline
   Type = Pipeline

   

2. Click OK to create the pipeline

3. Copy the contents of pretius-pipeline.jenkinsfile to the Pipeline > Script

4. Click Save

5. Click Build Now

All being well, the Build will be complete as per the picture in the Oracle APEX CI/CD in Action section

Adaptation to your Project

To adapt this to your project:

1. The folder structure must resemble the test repository pretius-apex-lifecycle-aligned-sample-customers

2. The artifacts must be exported through apexexport2git and zipped using apexgit2buildzip. Please also see the Suitability as an Approach section which highlights some issues as of SQLcl 23.1

3. The build zip must include the workspace and resemble the structure of sample_customers_0000.zip

4. Other_schemas, data, pre, post & cypress folders are optional.

5. The pipeline above is manually triggered. You can modify this to trigger upon other events.

Configure Jenkins to access a Private Repository

If your GitHub project is on a private repository, you'll need to allow Jenkins to access it by following the steps below.

1. Create a GitHub Personal access token (classic) by following this guide . If you will be using a private repo on GitHub for your CI/CD, you must complete this step
   Note: you just need the repo scope

   

2. Copy the token to clipboard, then in Jenkins > Dashboard > Manage Jenkins > Manage Credentials > System > Global Credentials (unrestricted) > Add Credentials

   

   Username = your GitHub Username
   Password = Paste token from clipboard

Configure the Pipeline

1. in Jenkins > Dashboard > + New Item > Create a new pipeline, or copy the existing one

2. Paste in the contents of pretius-pipeline.jenkinsfile, there are two sections to modify to your requirements

Additionally, change the Jenkins Settings section if you require specific environment settings.

Suitability as an Approach

All exports/imports were performed using SQlcl 23.1

Using a previous version of SQLcl is not advisable.

To effectively implement this approach you'll need to use the lb genschema -split command used in the apexexport2git script from the technical paper. If working with additional schemas this will need to be adapted to lb genschema -split -synonyms -grants command.

In following the method described in the Technical Paper, the following observations were found

1. The general ordering of controller.xml was not consistent with objects being referenced before they were created. This was also the case in Sample Customers, however, other projects were exported correctly without manual resolution. This has been logged as bug #35372250

2. This was more pronounced with the lb genschema -split -synonyms -grants command, with grants being placed in inconsistent places. Let's be clear about this with an example:

   Left= As exported by lb genschema
   Right = As left, however with manual resolution

   

   With reference to line numbers on the left, the issues here are:
   1. Missing context which was required to be manually exported with gen-object & manually added to line 45 on the right.
   2. Line 50-59: Grants are running before objects are created, and have now been manually moved to the end of the controller.xml
   3. Line 64+65: The Procedure references the Package, and therefore a compilation issue occurs as they've been exported in an incorrect order
   4. Line 70: The file has not been able to be parsed correctly. This means it will not be sorted according to any object dependencies. A non-parsed file means the filename cannot be determined, nor the sort order. In the case above, by luck, the sort order of this file is correct. Also, see point 7 below.

3. Complex schemes such as ut3 from utPLSQL were impossible to export due to the numerous ordering issues. This is why the provided pipeline uses an alternative approach to download and install utPLSQL using the headerless method.

4. Storage and Tablespace information were being emitted when specifically instructed not to emit. This causes lb update to error when specific named tablespaces cannot be located. This has been logged as bug #35372351.

5. lb update removes blank lines, not only differing from the source but causing utPLSQL to not detect any tests to run. This has been logged as bug #35304127

6. Larger DB, i.e controller.xml files containing 10k lines, take upwards of 4 hours to export with lb genschema

7. Some objects were emitted with parsing issues, however, they didn't appear to cause a problem when importing for me.

   

Hopefully, future versions of SQLcl will resolve the above. However for now the Build Manager has to manually resolve the issues in the generated changesets to enable the installation.

This is why I added the quote about the Build Manager, as multiple developers creating their builds in a Feature-Centric approach, for now, will certainly run into the above issues.

In addition, since APEX Applications cannot be imported in YAML nor is SQL merging supported, any merge conflicts requiring resolution will be problematic.

In the case of pretius-apex-lifecycle-aligned-sample-customers, manual attention was certainly required in generating the changesets.

The bottom line is, that this solution relies on SQLcl providing complete & ordered changesets & SQLcl 23.1 currently not providing that.