SQLcl Projects - Using Liquibase properties to modify your APEX installation

Prereqs

• Latest SQLcl

• Existing SQLcl Project with an APEX app

Note

On average, I prefer to keep the same Schema name, APEX Application ID, Workspace Name, and Workspace ID across environments and have separate databases. I understand that this isn't the case for everyone, and even with separate instances, you may still have valid reasons to change the Workspace name, Application ID, etc.

Dynamic APEX Install Demo

If you have been using SQLcl Projects, you might have noticed some interesting things in your APEX application's autogenerated changeset file: dist/releases/apex/f<xxx>/f<xxx>.xml. Below is an example of my APEX application export (f100.sql) and staged changes for my SQLcl Project.

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns:n0="http://www.oracle.com/xml/ns/dbchangelog-ext"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.3.xsd">
<changeSet id="INSTALL_100" author="SQLCL-Generated"  failOnError="true" runAlways="true">
    <n0:runApexScript objectName="install" objectType="SCRIPT" ownerName="ADMIN" sourceType="STRING"  relativeToChangelogFile="true" >
        <n0:source><![CDATA[
 declare 
 -- sqlcl version      = 24.4.1.0 
 -- override_schema    = ${apex.100.schema}
 -- override_alias     = ${apex.100.alias}
 -- override_workspace = ${apex.100.workspace}
 -- override_app_id    = ${apex.100.appId}

 l_app_id varchar2(255) := q'[100]';  
 l_workspace varchar2(255) := q'[DEMO]'; 

 l_override_workspace varchar2(255) := q'[${apex.100.workspace}]'; 
 l_override_schema varchar2(255) := q'[${apex.100.schema}]'; 
 l_override_alias varchar2(255) := q'[${apex.100.alias}]'; 
 l_override_app_id varchar2(255) := q'[${apex.100.appId}]'; 
 l_generate_offset boolean := false;

 function get_prop_default(p_prop_name varchar2)
 return varchar2 is
 begin  
   return '${apex.100.' || p_prop_name || '}';
 end;  

 begin  
  apex_application_install.clear_all(); 

  -- set workspace 
     if (l_override_workspace != l_workspace and l_override_workspace != get_prop_default('workspace')) then
      apex_application_install.set_workspace(l_override_workspace);
  else 
      apex_application_install.set_workspace(l_workspace);         
  end if; 
  commit;  

  -- set app id 
  if (l_override_app_id != l_app_id and l_override_app_id != get_prop_default('appId')) then
     apex_application_install.set_application_id(l_override_app_id);
    l_generate_offset := true;
  else  
      apex_application_install.set_application_id(l_app_id);
  end if; 

 if (l_override_schema != get_prop_default('schema')) then
    apex_application_install.set_schema(l_override_schema);
  end if;

  -- set alias 
  if (l_override_alias != get_prop_default('alias')) then
    apex_application_install.set_application_alias(l_override_alias);
   l_generate_offset := true;
 end if;

  -- generate offset if necessary
 if (l_generate_offset)  then
    apex_application_install.generate_offset();
 end if;
end;  
/
-- sqlcl_checksum  1740173850613
@f100.sql
        ]]></n0:source>
    </n0:runApexScript>
</changeSet>
</databaseChangeLog>

You can now set the following properties without having to hand write custom code:

• override_schema = ${apex.<app_id>.schema}

• override_alias = ${apex.<app_id>.alias}

• override_workspace = ${apex.<app_id>.workspace}

• override_app_id = ${apex.<app_id>.appId}

How to set property values

In Liquibase there is the concept of properties that you can set multiple ways:

1. Via a properties file

2. SQLcl command line argument

3. Directly in the change log (XML/JSON/YAML)

4. Environment variables

I am going to show point 1 today and may show the other 3 points in the future.

Properties File

Here is my repo after creating my SQLcl Project, exporting, staging, and creating a release. I have one APEX application in my DEV environment with the app ID 100. This will be its unique identifer.

Next, create a properties file. I created one named liquibase.properties and placed in my dist/utils/ folder.

apex.100.alias = NEIL_TEST_APP
apex.100.appId = 12345678
apex.100.workspace = TEST
apex.100.schema = WKSP_TEST

Next modify dist/install.sql and add the -defaults-file utils/liquibase.properties option to your lb update command. This will tell Liquibase to use that file to drive our custom parameter/property values. Below is in example of the modified section in our dist/install.sql

-- Kick off Liquibase
prompt "Installing/updating schemas"
lb update -log -changelog-file releases/main.changelog.xml -search-path "." -defaults-file utils/liquibase.properties

Next generate your artifact. Remember the assumption was that you have already done all the previous steps of exporting, staging, and creating release.

neiferna@neiferna-mac sqlcl_apex_bug % sql /nolog


SQLcl: Release 24.4 Production on Fri Mar 07 09:54:46 2025

Copyright (c) 1982, 2025, Oracle.  All rights reserved.

SQL> project gen-artifact -version 1.0
Your artifact has been generated demo-1.0.zip

Next it is time to deploy!

SQL> conn -name demo
Connected.
SQL> project deploy -file artifact/demo-1.0.zip
Starting the migration...

Voila, it got installed into a different workspace, with a different alias, and a new Application ID.

Future

I have another blog post coming soon that will show you how to add custom scripts to make the installer for APEX installation parameters even more configurable. This will include switching and modifying authentication schemes using substitution values and custom scripts. The example above includes some built-in parameters, and there may be more flexibility in the future.