How to Deploy APEX Applications with SQLcl Projects

Release an APEX application and its schema changes together with SQLcl Projects and Git, deploy them to production, and check what arrived.

SQLcl Projects is the CI/CD workflow built into SQLcl. It exports schema objects and APEX applications into a Git repository, turns the differences between Git branches into Liquibase changesets, packages each release as a zip artifact, and deploys that artifact to another database or schema. Tables and the application that uses them travel in one release. This guide deploys an APEX application and its table to a production workspace, then ships a second release with a schema change and an application change, and checks what actually arrived.

What You Need

  • SQLcl with Java, and Git. The examples use SQLcl 26.3, Java 21, and Oracle APEX 26.1.
  • A development workspace DEVOPS_DEV, whose schema DEVOPS_DEV owns the ORDERS table and application 500, Order Tracker.
  • A production workspace DEVOPS_PROD, with an empty schema DEVOPS_PROD.

The development steps run in SQLcl connected as DEVOPS_DEV, in the project directory; the deployment steps run connected as DEVOPS_PROD. The workflow is: init, export, stage, release, gen-artifact, and deploy.

Step 1: Create the Project

Example:

project init -name order_tracker -schemas DEVOPS_DEV

Output:

------------------------
PROJECT DETAILS
------------------------
Project name:    order_tracker
Schema(s):       DEVOPS_DEV
Directory:       order_tracker
Connection name:
Project root:     order_tracker
Your project has been successfully created

The project gets a .dbtools folder with its settings, an empty src folder for the exported objects, and a dist folder for the releases. Put it under Git:

Example:

git init -q -b main
git add .
git commit -q -m "Initialize the order_tracker project"
git log --oneline

Output:

3e8476c Initialize the order_tracker project

Step 2: Leave Schema Names Out of the DDL

By default, the exported DDL names the schema, as in create table devops_dev.orders, so a deployment would create objects in DEVOPS_DEV again. Turning off emitSchema creates them in the schema that runs the deployment:

Example:

project config set -name export.setTransform.emitSchema -value false

Output:

Process completed successfully

Commit the setting, and start a branch for the first piece of work:

Example:

git commit -q -am "Do not write schema names into the DDL"
git checkout -b base-release

Output:

Switched to a new branch 'base-release'

Step 3: Export the Table and the Application

Example:

project export

Output:

*** TABLES ***
*** APEX_APPLICATIONS ***
Exporting Workspace DEVOPS_DEV - application 500:Order Tracker
-------------------------------
APEX_APPLICATION              1
TABLE                         1
-------------------------------
Exported 2 objects
Elapsed 6 sec

project export writes every object of the project's schemas to src, and the APEX applications whose parsing schema is one of them:

Example:

find src -type f | sort | sed -n '1,8p'
echo "..."
git add .
git commit -q -m "Export ORDERS and the Order Tracker app"

Output:

src/database/devops_dev/apex_apps/f500/.apex/apexlang.json
src/database/devops_dev/apex_apps/f500/application.apx
src/database/devops_dev/apex_apps/f500/deployments/default.json
src/database/devops_dev/apex_apps/f500/f500.sql
src/database/devops_dev/apex_apps/f500/page-groups.apx
src/database/devops_dev/apex_apps/f500/pages/p00000-global-page.apx
src/database/devops_dev/apex_apps/f500/pages/p00001-home.apx
src/database/devops_dev/apex_apps/f500/pages/p00002-orders.apx
...

The application is stored both as an installable SQL file, f500.sql, and as readable .apx files, which make application changes easy to review.

Step 4: Stage the Changes

project stage compares the current branch with main and writes Liquibase changesets for the differences:

Example:

project stage

Output:

Stage is Comparing:
Old Branch	refs/heads/main
New Branch	refs/heads/base-release

Stage processing completed, please review and commit your changes to repository

 Untracked files:
	dist

Commit the staged files and merge the branch into main:

Example:

find dist -type f | sort
git add .
git commit -q -m "Stage the changes"
git checkout -q main
git merge -q base-release
git log --oneline

Output:

dist/env/default.properties
dist/install.sql
dist/releases/apex/apex.changelog.xml
dist/releases/apex/f500/f500.sql
dist/releases/apex/f500/f500.xml
dist/releases/main.changelog.xml
dist/releases/next/changes/base-release/devops_dev/tables/orders.sql
dist/releases/next/changes/base-release/stage.changelog.xml
dist/releases/next/release.changelog.xml
dist/utils/prechecks.sql
dist/utils/recompile.sql
5eaa4df Stage the changes
2e2e6e8 Export ORDERS and the Order Tracker app
9b60f7b Do not write schema names into the DDL
3e8476c Initialize the order_tracker project
  • dist/releases/next holds the changes that are not released yet, here the ORDERS table.
  • dist/releases/apex holds the application, with a changeset that installs it.
  • dist/install.sql runs the whole Liquibase update.

Step 5: Release 1.0 and Build the Artifact

Example:

project release -version 1.0
project gen-artifact -version 1.0

Output:

Process completed successfully
Your artifact has been generated order_tracker-1.0.zip

project release moves the contents of next into a 1.0 release folder, and gen-artifact packs dist into artifact/order_tracker-1.0.zip. The changeset that installs the application reads optional properties, which let one artifact install the application under another workspace, ID, schema, or alias:

Example:

grep -- "-- override_" dist/releases/apex/f500/f500.xml

Output:

 -- override_schema    = ${apex.500.schema}
 -- override_alias     = ${apex.500.alias}
 -- override_workspace = ${apex.500.workspace}
 -- override_app_id    = ${apex.500.appId}
 -- override_offset    = ${apex.500.offset}

Step 6: Deploy Release 1.0 to Production

The deployment needs only the artifact and a properties file for the target. Application 500 already exists in this APEX instance, so production gets it as application 600:

Example:

cp ../order_tracker/artifact/order_tracker-1.0.zip .
cat > prod.properties <<'PROPS'
apex.500.workspace=DEVOPS_PROD
apex.500.appId=600
apex.500.schema=DEVOPS_PROD
apex.500.alias=ORDER-TRACKER-PROD
PROPS
ls

Output:

order_tracker-1.0.zip
prod.properties

Connected as DEVOPS_PROD:

Example:

project deploy -file order_tracker-1.0.zip -defaults-file prod.properties

Output:

Defaults-file properties successfully applied
Starting the migration...
Installing/updating schemas
--Starting Liquibase at 2026-10-06T12:04:31.071032 using Java 21.0.12.1 (version 4.33.0 #0 built at 2025-12-09 17:47+0000)
Running Changeset: base-release/devops_dev/tables/orders.sql::1791268466526::DEVOPS_DEV
Table ORDERS created.
Running Changeset: base-release/devops_dev/tables/orders.sql::1791268466532::DEVOPS_DEV
Table ORDERS altered.
Running Changeset: base-release/devops_dev/tables/orders.sql::1791268466537::DEVOPS_DEV
Table ORDERS altered.
Running Changeset: releases/1.0/release.changelog.xml::release-1.0::DEVOPS_DEV
Running Changeset: releases/apex/f500/f500.xml::INSTALL_500::SQLCL-Generated
PL/SQL procedure successfully completed.

--application/set_environment
APPLICATION 500 - Order Tracker
--application/delete_application
--application/create_application
--application/plugin_settings
--application/shared_components/navigation/lists/navigation_bar
--application/shared_components/navigation/lists/navigation_menu
--application/shared_components/navigation/lists/page_navigation
--application/shared_components/navigation/listentry
--application/shared_components/files/icons_app_icon_144_rounded_png
--application/shared_components/files/icons_app_icon_192_png
--application/shared_components/files/icons_app_icon_256_rounded_png
--application/shared_components/files/icons_app_icon_32_png
--application/shared_components/files/icons_app_icon_512_png
--application/shared_components/security/authorizations/administration_rights
--application/shared_components/navigation/navigation_bar
--application/shared_components/logic/application_settings
--application/shared_components/navigation/tabs/standard
--application/shared_components/navigation/tabs/parent
--application/shared_components/user_interface/lovs/boolean
--application/pages/page_groups
--application/shared_components/navigation/breadcrumbs/breadcrumb
--application/shared_components/navigation/breadcrumbentry
--application/shared_components/user_interface/themes
--application/shared_components/user_interface/theme_style
--application/shared_components/user_interface/theme_files
--application/shared_components/user_interface/template_opt_groups
--application/shared_components/user_interface/template_options
--application/shared_components/globalization/language
--application/shared_components/logic/build_options
--application/shared_components/globalization/messages
--application/shared_components/globalization/dyntranslations
--application/shared_components/security/authentications/oracle_apex_accounts
--application/user_interfaces/combined_files
--application/pages/page_00000
--application/pages/page_00001
--application/pages/page_00002
--application/pages/page_00003
--application/pages/page_00004
--application/pages/page_00005
--application/pages/page_09999
--application/end_environment
...done

UPDATE SUMMARY
Run:                          5
Previously run:               0
Filtered out:                 0
-------------------------------
Total change sets:            5

Liquibase: Update has been successful. Rows affected: 0

Produced logfile: sqlcl-lb-1791268471068.log

Operation completed successfully.

Liquibase created the table with its constraints, then installed the application. A check in production:

Example:

select application_id, application_name, alias, owner, version
from   apex_applications where workspace = 'DEVOPS_PROD';
select count(*) as orders from orders;

Output:

   APPLICATION_ID APPLICATION_NAME    ALIAS                 OWNER          VERSION
_________________ ___________________ _____________________ ______________ ______________
              600 Order Tracker       ORDER-TRACKER-PROD    DEVOPS_PROD    Release 1.0

   ORDERS
_________
        0

Release 1.1: A Schema Change and an Application Change

The next piece of work starts on a new branch, after committing the 1.0 release on main:

Example:

git add .
git commit -q -m "Release 1.0"
git checkout -b add-status

Output:

Switched to a new branch 'add-status'

In development, ORDERS gets a STATUS column and the application a new version number, and the project is exported again:

Example:

-- the schema change
alter table orders add status varchar2(10) default 'NEW' not null
  constraint orders_status_ck check (status in ('NEW', 'SHIPPED', 'DELIVERED'));
-- the app change
begin
  apex_util.set_workspace('DEVOPS_DEV');
  apex_application_admin.set_application_version(p_application_id => 500, p_version => 'Release 1.1');
  commit;
end;
/
project export

Output:

Table ORDERS altered.

PL/SQL procedure successfully completed.

*** TABLES ***
*** APEX_APPLICATIONS ***
Exporting Workspace DEVOPS_DEV - application 500:Order Tracker
-------------------------------
APEX_APPLICATION              1
TABLE                         1
-------------------------------
Exported 2 objects
Elapsed 6 sec

Git shows what changed, and the changes are committed before staging, because stage compares committed branches:

Example:

git status --short
git diff -U0 src/database/devops_dev/tables/orders.sql | grep '^[-+][^-+]' | grep -v sqlcl_snapshot
git add .
git commit -q -m "Add order status, app release 1.1"

Output:

 M src/database/devops_dev/apex_apps/f500/application.apx
 M src/database/devops_dev/apex_apps/f500/f500.sql
 M src/database/devops_dev/tables/orders.sql
-    notes            varchar2(400 byte)
+    notes            varchar2(400 byte),
+    status           varchar2(10 byte) default 'NEW' not null enable
+alter table orders
+    add constraint orders_status_ck
+        check ( status in ( 'NEW', 'SHIPPED', 'DELIVERED' ) ) enable;

Example:

project stage

Output:

Stage is Comparing:
Old Branch	refs/heads/main
New Branch	refs/heads/add-status

Stage processing completed, please review and commit your changes to repository

Changes not staged for commit
	modified: dist/releases/apex/f500/f500.sql
	modified: dist/releases/main.changelog.xml

 Untracked files:
	artifact
	dist/releases/next

Example:

find dist/releases/next -type f | sort
git add .
git commit -q -m "Stage the order status change"
git checkout -q main
git merge -q add-status

Output:

dist/releases/next/changes/add-status/devops_dev/tables/orders.sql
dist/releases/next/changes/add-status/stage.changelog.xml
dist/releases/next/release.changelog.xml

Example:

project release -version 1.1
project gen-artifact -version 1.1

Output:

Process completed successfully
Your artifact has been generated order_tracker-1.1.zip

Stage did not recreate the table: it generated ALTER statements for the difference:

Example:

grep -v sqlcl_snapshot dist/releases/1.1/changes/add-status/devops_dev/tables/orders.sql

Output:

-- liquibase formatted sql
-- changeset DEVOPS_DEV:1791268488888 stripComments:false  logicalFilePath:add-status/devops_dev/tables/orders.sql

alter table orders add (
    status varchar2(10) default 'NEW' not null enable
)
/

-- changeset DEVOPS_DEV:1791268488896 stripComments:false  logicalFilePath:add-status/devops_dev/tables/orders.sql

alter table orders
    add constraint orders_status_ck
        check ( status in ( 'NEW', 'SHIPPED', 'DELIVERED' ) ) enable
/

Deploy Release 1.1

Example:

cp ../order_tracker/artifact/order_tracker-1.1.zip .
ls

Output:

order_tracker-1.0.zip
order_tracker-1.1.zip
prod.properties
sqlcl-lb-1791268471068.log

Example:

project deploy -file order_tracker-1.1.zip -defaults-file prod.properties

Output:

Defaults-file properties successfully applied
Starting the migration...
Installing/updating schemas
--Starting Liquibase at 2026-10-06T12:04:53.366412 using Java 21.0.12.1 (version 4.33.0 #0 built at 2025-12-09 17:47+0000)
Running Changeset: add-status/devops_dev/tables/orders.sql::1791268488888::DEVOPS_DEV
Table ORDERS altered.
Running Changeset: add-status/devops_dev/tables/orders.sql::1791268488896::DEVOPS_DEV
Table ORDERS altered.
Running Changeset: releases/1.1/release.changelog.xml::release-1.1::DEVOPS_DEV

UPDATE SUMMARY
Run:                          3
Previously run:               5
Filtered out:                 0
-------------------------------
Total change sets:            8

Liquibase: Update has been successful. Rows affected: 0

Produced logfile: sqlcl-lb-1791268493364.log

Operation completed successfully.

Only the three new changesets ran; the five of release 1.0 were skipped as previously run. A check in production:

Example:

select application_id, version from apex_applications where workspace = 'DEVOPS_PROD';
select column_name, data_default from user_tab_columns where table_name = 'ORDERS' order by column_id;
select id, author, filename from databasechangelog order by orderexecuted;

Output:

   APPLICATION_ID VERSION
_________________ ______________
              600 Release 1.0

COLUMN_NAME         DATA_DEFAULT
___________________ _______________________________________
ORDER_ID            "DEVOPS_PROD"."ISEQ$$_98608".nextval
CUSTOMER_NAME
DELIVERY_METHOD     'DELIVERY'
DELIVERY_ADDRESS
NOTES
STATUS              'NEW'

6 rows selected.

ID               AUTHOR             FILENAME
________________ __________________ ____________________________________________
1791268466526    DEVOPS_DEV         base-release/devops_dev/tables/orders.sql
1791268466532    DEVOPS_DEV         base-release/devops_dev/tables/orders.sql
1791268466537    DEVOPS_DEV         base-release/devops_dev/tables/orders.sql
release-1.0      DEVOPS_DEV         releases/1.0/release.changelog.xml
INSTALL_500      SQLCL-Generated    releases/apex/f500/f500.xml
1791268488888    DEVOPS_DEV         add-status/devops_dev/tables/orders.sql
1791268488896    DEVOPS_DEV         add-status/devops_dev/tables/orders.sql
release-1.1      DEVOPS_DEV         releases/1.1/release.changelog.xml

8 rows selected.
  • The STATUS column arrived, and DATABASECHANGELOG records every changeset that ran.
  • The application is still Release 1.0. Its installing changeset, INSTALL_500, was not run again.

Liquibase runs that changeset again only when the changeset itself changes. Its text, with the properties filled in, was the same in both releases; the new application export is in the separate file f500.sql. In this test with SQLcl 26.3, a changed application alone therefore did not reach production through project deploy.

Install the Changed Application from the Artifact

The 1.1 artifact contains the new export, so it can be installed directly, with the same settings as in the properties file:

Example:

unzip -q order_tracker-1.1.zip -d release-1.1
ls release-1.1/releases/apex/f500

Output:

f500.sql
f500.xml

Example:

begin
  apex_application_install.set_workspace('DEVOPS_PROD');
  apex_application_install.set_application_id(600);
  apex_application_install.generate_offset;
  apex_application_install.set_schema('DEVOPS_PROD');
  apex_application_install.set_application_alias('ORDER-TRACKER-PROD');
end;
/
@release-1.1/releases/apex/f500/f500.sql
select application_id, version from apex_applications where workspace = 'DEVOPS_PROD';

Output:

PL/SQL procedure successfully completed.

--application/set_environment
APPLICATION 500 - Order Tracker
--application/delete_application
--application/create_application
--application/plugin_settings
--application/shared_components/navigation/lists/navigation_bar
--application/shared_components/navigation/lists/navigation_menu
--application/shared_components/navigation/lists/page_navigation
--application/shared_components/navigation/listentry
--application/shared_components/files/icons_app_icon_144_rounded_png
--application/shared_components/files/icons_app_icon_192_png
--application/shared_components/files/icons_app_icon_256_rounded_png
--application/shared_components/files/icons_app_icon_32_png
--application/shared_components/files/icons_app_icon_512_png
--application/shared_components/security/authorizations/administration_rights
--application/shared_components/navigation/navigation_bar
--application/shared_components/logic/application_settings
--application/shared_components/navigation/tabs/standard
--application/shared_components/navigation/tabs/parent
--application/shared_components/user_interface/lovs/boolean
--application/pages/page_groups
--application/shared_components/navigation/breadcrumbs/breadcrumb
--application/shared_components/navigation/breadcrumbentry
--application/shared_components/user_interface/themes
--application/shared_components/user_interface/theme_style
--application/shared_components/user_interface/theme_files
--application/shared_components/user_interface/template_opt_groups
--application/shared_components/user_interface/template_options
--application/shared_components/globalization/language
--application/shared_components/logic/build_options
--application/shared_components/globalization/messages
--application/shared_components/globalization/dyntranslations
--application/shared_components/security/authentications/oracle_apex_accounts
--application/user_interfaces/combined_files
--application/pages/page_00000
--application/pages/page_00001
--application/pages/page_00002
--application/pages/page_00003
--application/pages/page_00004
--application/pages/page_00005
--application/pages/page_09999
--application/end_environment
...done

   APPLICATION_ID VERSION
_________________ ______________
              600 Release 1.1

1 row selected.

Production now runs Release 1.1 of the application, on the table of release 1.1.

Things to Know

  • Check the application version in APEX_APPLICATIONS after every deployment, so an application that was not reinstalled is noticed at once.
  • Keep one properties file per target environment outside the artifact, so the same zip deploys to test and production.
  • project verify checks the project for missing or inconsistent files before a release.
  • Commit before project stage: it compares committed branches, and uncommitted exports are not staged.

Related Guides

Conclusion

SQLcl Projects exports tables and APEX applications into Git, stages branch differences as Liquibase changesets, and deploys each release as an artifact, with properties that install the application under another workspace and ID. Schema changes arrive as ALTER statements; check the application version after each deployment, and install the artifact's application export directly when only the application changed.

Tags
Vinish Kapoor
Vinish Kapoor

An Oracle ACE, author of four books on Oracle APEX, SQL and PL/SQL, and Oracle Forms, and a software developer building Oracle database applications since 2001.

guest

0 Comments
Oldest
Newest Most Voted