Kickstarting CI/CD for Oracle Database/APEX with SQLcl

After a lot of reading, trying to understand, testing, and getting beat up by the tool, I finally managed to make a simple CI/CD process work between development and production. I can now generate releases from the development database that can be applied in the same way to other production environments. In this article, I’ll show a bit of my approach.

Before we get started, let’s set some expectations:

A. Not everything is perfect

If you're expecting a perfect script that does absolutely everything for you, it’s better to let go of that idea now. SQLcl isn’t perfect, nor is it very well documented. You’ll still need to make a few manual adjustments.

I’ve noticed that each version brings new bugs, but there's always a workaround.

B. Java objects are ignored

Java objects in the database are skipped by the tool. You’ll need to find a way to handle that if you're using them.

C. Caution if both environments run on the same PDB

If your dev and prod environments are under the same PDB, I recommend handling APEX/ORDS in a separate project. There are some specific quirks that could cause problems.

D. This assumes you're generating artifacts for the first time

This article assumes you're generating artifacts for the first time and applying them to a clean environment. If you want to deploy to a database that already has the objects, you’ll need to run a specific command to sync them — I’ll share that at the end.

Requirements:

To follow this tutorial, you'll obviously need Oracle Database. You’ll also need:

1. Windows (Linux is possible, but you’ll need to adapt some steps)

2. SQLcl (I'm using version 25.2.1.195)

3. Java (jdk-21.0.8 in this case)

4. Oracle Instant Client (version 23.8 in this case)

5. GIT (Any recent version. Must be installed — portable versions won't work)

Once everything is installed, open the Windows CMD and manually set some environment variables to avoid issues. Below is an example — you'll need to adjust the paths to match your setup. I recommend typing them line by line to ensure Windows processes everything correctly:

rem The CHCP 65001 command ensures your CMD handles special characters (like accents) better.
chcp 65001 
rem If you run into memory issues, you can manually set Java memory.
rem set JAVA_TOOL_OPTIONS=-Xms512m -Xmx6G 
rem Here we define the path to your Java Home to make sure it uses the correct version.
set "JAVA_HOME=C:\Oracle\jdk-21.0.8"
rem Now we define the path to your SQLcl installation.
set "SQLCL_HOME=C:\Oracle\sqlcl-25.2.1.195.1751\sqlcl"
rem If needed, here's where you'd set the Instant Client path.
rem SET INSTANT_CLIENT_HOME=C:\Util\instantclient\instantclient_23_4 

rem This adds all the above to your system PATH along with the default.
set PATH=%SQLCL_HOME%\bin;%JAVA_HOME%\bin;%INSTANT_CLIENT_HOME%;%PATH%
rem If you're using TNSADMIN to pull DB credentials, you can set it like this.
set TNS_ADMIN=C:\Oracle\instantclient_23_4\network\admin

You've now configured the environment to move on to the next steps. Do not close this window, since it contains all the environment variables we need. My example project will be called PMT.

You can minimize the CMD window and create an empty folder for your project. In my case, the folder will be PMT. Back in CMD, navigate to the folder you just created:

cd C:\Oracle\projetos\PMT

Now that you’re in the right folder, let’s connect SQLcl:

CONNECTING TO SQLCL

You’ve got a few connection options. Just choose one:

1. Connect without passing credentials initially:

sql -thin /nolog

conn PMT/pass@dbpessoal

2. Direct connection:

sql -thin PMT/pass@//databasehostnameorip:1521/servicename

3. Using TNSNAMES.

sql -thin PMT/pass@dbpessoal

PREPARING YOUR FIRST RELEASE

Once connected via SQLcl and inside your project folder, initialize and configure your project (only needs to be done once):

project init -name PMT -schemas PMT

Project configuration

Once the project is initialized, you can either skip any further configuration or customize it to your liking. If you're curious to see the available settings, just run the following command:

project config -list -verbose

Below, I’m listing the commands I personally prefer. Feel free to use them as examples — or not. To understand exactly what each command does, just run the previous command — it gives a very clear explanation.

project config set -name export.format.enable -value false -verbose 
project config set -name export.setTransform.emitSchema -value false -verbose
project config set -name export.apex.expComments -value true -verbose 
project config set -name export.apex.expOriginalIds -value true -verbose 
project config set -name export.apex.expSavedReports -value true -verbose 
project config set -name export.apex.expPubReports -value true -verbose

These configs are saved in the .dbtools folder.

Configurando filtros

Inside the .dbtools folder, there’s a subfolder named filters containing a single file: project.filters.

If you open this file, you’ll find some extra settings — for example, how to ignore certain objects or export only specific APEX apps. In our case, we won’t modify anything in that file. Let’s go straight to the GIT commands. Still, I recommend that you take a look at the APEX objects, ORDS, and grants later on.

Assuming you already have Git installed, we’ll create the main branch with the following command. Since we’re still inside SQLcl, we need to prefix Git commands with !. This applies to any other OS-level command you want to run from SQLcl as well.

--with this command you’ll create and switch to your initial branch.
--It’s important to keep what comes after the two dashes: -- in this first command:
!git init --initial-branch=main
--this command adds the files to git
!git add .
--after adding, we commit — and can include a message to describe this commit
!git commit -m "Project initialization"

Let’s now create our first release, named 1.0:

--this command creates a new branch and moves us into it
!git checkout -b release-1.0

We’re now in branch 1.0, so it’s time to export our database. To do that, we’re going to run the commands below. Important: Don’t let any developers make changes to the database while the export is running.

project export -verbose

--adds the files to git and commit
!git add .
!git commit -m "release-1.0"

All exported objects go into the /src folder.

Now we will put the project in stage, which will compare the current export with previous branches. Below, the list of commands:

--this command prepares the files for release
project stage

--if you want to add a list of DML commands, add it with this command at this step.
--project stage add-custom -file-name dml_file_name

--if you want to check if everything is ok with the project you can run project verify, but it’s not mandatory.
--project verify -verbose 

--we set the release version
project release -version 1.0 -verbose
--with the command below we generate the artifact. A .zip with all the deployment instructions and all objects, already listed in the order they must be executed.
project gen-artifact -version 1.0 -verbose

--we add and commit to git.
!git add . 
!git commit -m "Commit Version 1.0"

--after success in all steps above, we need to go back to the main branch and merge the current release.
!git checkout main 
!git merge release-1.0

Now your first release is complete and you are ready to deploy it to other databases.

DEPLOYING TO THE TARGET ENVIRONMENT

After your release is ready, you will find the .zip artifact inside the artifacts folder, within your project’s main folder.
Now we will repeat some commands and connect to the target database. Remember, we are considering a clean target database where the objects don’t exist yet.
If the target database already contains the objects, this won’t work — you need to run another command first, which I’ll cover at the end of this article.

If you still have the CMD open, just run the quit command to exit SQLcl and reconnect to the target database.If you closed CMD, just run again the environment variable setup commands, go back to your project folder, and start SQLcl (this time connect to the target database — the one that will receive the objects. This is very important).

project deploy -file artifact/PMT-1.0.zip -verbose

Since we chose verbose mode, it will show every object being applied to the target database, until you get the success message. Besides your objects, it will create 3 tables in your target database: DATABASECHANGELOG, DATABASECHANGELOG_ACTIONS, and DATABASECHANGELOGLOCK, which are the Liquibase control tables.

PREPARING YOUR NEXT RELEASES

Assuming you already generated your first release and now need to generate others, I recommend making a backup of the project folder. Don’t try to use Windows compress because it ignores all Git-related files in the folder. You can copy the folder or create a .zip using 7Zip.

Let’s get to work. The first step is to go back to SQLcl, inside the project folder, and connect to the source database again. For that, run:

--exporting the project again
project export -verbose
--creating a new branch
!git checkout -b release-1.1
!git add . 
!git commit -m "New files exported from development database in version 1.1"
--putting in stage to compare our current branch with main
project stage -verbose
--creating the release
project release -version 1.1 -verbose
--generating the new artifact
project gen-artifact -version 1.1 -verbose
--adding and committing
!git add . 
!git commit -m "Commit dos novos arquivos da versão 1.1"
--checking out and going back to main branch
!git checkout main
--merging the last release into main
!git merge release-1.1

All set. You will now see another .zip artifact with all objects from the previous release plus the objects from the current release. When applying, Liquibase will confirm that all objects from the previous release were applied and then start applying the current release changes. If you’re sure the previous releases are successfully applied, you can unzip the artifact and remove the references to older releases from the main.changelog.xml file located in /releases. After editing, you will need to re-zip the file with the changes to deploy it.

OTHER CONSIDERATIONS

A. If you want to start when your target database already contains the objects:

In this case, you will create the objects normally, but before deploying the first release to the target database, you need to run this command on the target database:

liquibase changelog-sync -chf dist/releases/main.changelog.xml

This command will create the Liquibase tables in your target database and mark the objects as already applied. From here on, the next releases will work since Liquibase understands the first release has been applied.

B. If you are trying to apply all this in the same PDB but in different schemas:

Make ORDS and APEX a separate process because it might try to overwrite what already exists. For that, in .dbtools/filters, mark to not export APEX or ORDS. You can do that separately later.

FINAL NOTES:

Some personal observations about this process:

• Each new release artifact carries all previous releases along with it. It will always behave like this, but when applying, to make everything work properly, do not manually change the target database anymore, especially regarding object creation and table changes. The whole process must be done through the tool.

• On Windows, I noticed permissions are strongly linked to the user. So, if you have a Terminal Server with multiple people working, I recommend creating a dedicated devops user.

• Sometimes the screen freezes and you just need to press ENTER to continue. Other times it freezes and no ENTER will help — just wait. That’s normal.

• I recommend generating releases frequently, not letting too many changes accumulate before generating the release. This makes troubleshooting artifacts much easier.

• Test at your own risk. I recommend backing up the database before deployments.

• If you are interested in seeing this tutorial adapted for Linux, just comment here — I have already started working on that.

Bugs I noticed in some SQLcl versions

25.2.1 — I noticed this version still has problems handling primary keys of type IDENTIFIED BY. I had to change several database tables to the normal model (sequence + trigger) to avoid errors. I also still have problems with Liquibase recognizing dropped or renamed objects. It may also have problems connecting via TNSNAMES.

24.1 — Existing sequences are generated every time, causing conflicts.

References:

https://rafal.hashnode.dev/part-51-oracle-sqlcl-project-the-only-cicd-tool-for-apex-you-will-ever-need

https://pretius.com/blog/oracle-sqlcl-project-command