I think that there is a deployment issue specific to APEX applications in the current SQLcl Projects liquibase workflow. But before I explain the details, version info:
- Oracle Database 26ai
- SQLcl version: 25.4.0.0 build: 25.4.0.336.1328
- APEX version 24.2.12
Details:
For sake of problem illustration, just create a very basic APEX application – no underlying tables required – just one page is fine. In my case, the application ID just happens to be 222. Then:
- Go through the normal SQLcl Projects workflow and create release 1.0.
- Make a small change and go through the normal workflow again and create a release 2.0.
- Make another small change and go through the workflow one more time creating a release 3.0.
After these three iterations, the main.changelog.xml file should show:
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.3.xsd">
<include file="1.0/release.changelog.xml" relativeToChangelogFile="true"/>
<include file="apex/apex.changelog.xml" relativeToChangelogFile="true"/>
<include file="2.0/release.changelog.xml" relativeToChangelogFile="true"/>
<include file="3.0/release.changelog.xml" relativeToChangelogFile="true"/>
<include file="next/release.changelog.xml" relativeToChangelogFile="true"/>
</databaseChangeLog>
Which is really the issue: if we deploy to a new environment (where this app has not been installed before), we'll see that it actually installs the APEX application four times over. As confirmed in the liquibase tracking tables:
SQL> select filename, dateexecuted, orderexecuted from DATABASECHANGELOG order by orderexecuted;
FILENAME DATEEXECUTED ORDEREXECUTED
_________________________________________________ __________________________________ ________________
first-upload/demo/apex_applications/f222.sql 14-JAN-26 03.00.32.673848000 PM 1
releases/apex/f222/f222.xml 14-JAN-26 03.00.33.371587000 PM 2
second-version/demo/apex_applications/f222.sql 14-JAN-26 03.00.34.309408000 PM 3
third-version/demo/apex_applications/f222.sql 14-JAN-26 03.00.35.111776000 PM 4
Incidentally, the SQLcl output seems to show each installation's output twice – so a total of 8 APEX installation outputs to STDOUT – not entirely sure why that is but that's not the main point.
So it's a bit redundant: installing, dropping, installing the APEX Application over and over to evolve it through the three versions in new environment. Evolving a schema object through the three releases makes sense where version 2 might add a new column, version 3 another column, etc. But APEX is different as it' always a “_delete & re-install_”.
Where this becomes a problem:
Now where this changes from being just a redundancy and maybe a bit of a nuisance (i.e. if the app is large and takes time to install multiplied by each iteration) or when you have many many releases, it becomes an actual problem when you need to modify the APEX Application's installation details.
Say I follow @neil-fernandez-oracle ‘s excellent blog here as it’s critical that I change the application ID from 222 to 333 in the new environment, and I do that using a liquibase.properties file, that's when the problem is illustrated. As it's only the apex/apex.changelog.xml changelog that looks at the installation parameters (it's the only one with a f222.**xml** – and it's run second!
Consequently, when I deploy this application into a new system, where I need to change the application ID as mentioned, the first script that the project deploy runs from the liquibase changelogs is, as I showed previously, 1.0/release.changelog.xml. And at this point, the application ID is still the old value of 222. Then it runs apex/apex.changelog.xml where it picks up the new app_id of 333 and installs a second copy. (And of course it then proceeds to run the other two changelogs and install 333 two more times over. And it's only 333 for these additional installs because, in the scenario, it processed apex/apex.changelog.xml and hence f222.xml in the same SQLcl session.)
The end result is that when I look in my APEX workspace I now have two applications installed: 222 (reflecting version 1.0 of my application) and version 333 with the latest version.
Sure, in this simple example, I can then just log in and drop the pointless 222 . But what if I already had some other app which was 222 (which is why I'm changing the app_id in the first place)? This deployment would have dropped and clobbered my old app 222 which could potentially be quite a problem! Or what if I were changing another property such as the schema, and the original schema didn't exist in the new environment. Then the entire deployment would probably fail.
Possible Solutions:
There are a number of possible solutions to this issue that comes to mind off of the top of my head:
1. Since APEX installations are always a full "delete & re-install", really only the ./dist/releases/apex/f222/f222.sql installation (or changelog file apex/apex.changelog.xml) is all that needs to be run. Remove the others from the changelog.
2. Or if there is some reason to run all APEX changesets (which I'm not sure why but perhaps there is some reason), then if the order was switched in the first project stage command so that apex/apex.changelog.xml is listed first in dist/releases/main.changelog.xml. So that f<app_id>.xml is processed before the SQL files.
3. Finally, currently it's only dist/releases/apex directory that has the required f<app_id>.xml file, but if this file was also included for the other named version releases then that should address the problematic part of this scenario as well.
Thoughts?