This base lists commands you would most commonly use with SQLcl Project
- 1. SQLcl Aliases
- 2. Project Commands
- 3. Liquibase Commands
- 4. Git Commands
- 5. SQLcl Commands
- 6. Liquibase Changeset Directions
- 7. Environment Variables
Several of the most commonly used commands below are configured as SQLcl Aliases and saved in the file scripts/xml/sqlcl-project-aliases.xml in this repo. To load these aliases in your SQLcl environment, run the SQLcl alias load command as follows:
alias load scripts/xml/sqlcl-project-aliases.xmlalias save scripts/xml/sqlcl-project-aliases.xmlYou can check if they were loaded successfully by running alias list command and see if you have aliases starting with prj_
SQL> alias list
prj_install
prj_mr
prj_status
prj_sync
SQL>
- Always run
project exportbeforeproject stage. - Review staged files carefully before committing.
project gen-artifactonly includes staged files.
Initializes a new SQLcl Project structure. Used only once per repository.
Example:
project init -name real_sqlcl -schemas cla_apex,cla_utilitiesExports database objects or APEX applications from the connected environment into the repository.
Example:
conn -name proj_dev
project export -o APEX.110SQL> proj export -o APEX.110
The current connection //10.0.0.158:1521/dev.privsubnet2reg.vcnexample01.example.vcn.com CLA_DEPLOYER will be used for all operations
*** APEX_APPLICATIONS ***
Exporting Workspace CLA_INTERNAL - application 110:Tactical Emergency Response Program
-------------------------------
APEX_APPLICATION 1
-------------------------------
Exported 1 objects
Elapsed 20 sec
SQL> Shows what objects export command would exported, but does not run export
Example:
conn -name proj_dev
project export -listCompares the repo state with the database and prepares staged files for a release.
Example:
conn -name proj_dev
project stageThe most common custom changes:
- DML on “fixed” tables, for example, insert 15 invoice statuses into INVOICE_STATUS table
- Handle objects that SQLcl project does not handle natively, for example:
- Run
Alter System - Create/Drop APEX Workspace
- Create/Drop Scheduled Jobs, AQ Tables, Refresh Materialized Views
- Stop/Start Jobs and APEX Automations
- ...and much more…
conn -name proj_dev
proj stage add-custom -file-name test.sqlAfter running add-custom command above, open the file dist/releases/next/changes/<your_branch>/_custom/test.sql for edit and add your code after LB header.
Creates a release directory for grouping staged changes into a deployable unit.
Example:
project release -name release_25_2Generates the deployable ZIP artifact from the release folder.
Example:
project gen-artifact -release release_25_2Deploys the ZIP artifact to the connected environment. Should be executed only with the correct DBA-level deployer account.
Example:
conn -name proj_test
project deploy -file dist/releases/release_25_2.zip -verbose- Always connect then
cd distbefore running LB. - Use
-changelog-file releases/main.changelog.xmlexcept for drop-all.
Shows which Liquibase changesets are pending or already applied.
Example:
conn -name proj_test
cd dist
lb status -changelog-file releases/main.changelog.xmlAlias: prj_status
Applies pending changesets to the current environment.
Example:
conn -name proj_test
cd dist
lb update -changelog-file releases/main.changelog.xmlThis is a command that runs from dist/instal.sql . Unlikely you will need to run it directly
- Marks all existing changesets as executed without applying them. Mostly used for baseline creation.
changelog-sync-sqlshows what changesets will be applied
Example:
conn -name proj_prod
cd dist
lb changelog-sync -changelog-file releases/main.changelog.xmlAlias: prj_sync
Marks the next unexecuted changeset as executed. This is mostly used in the following scenario:
- You ran deployment and it failed, let's say on a unique constaraint
- You ran DML and get rid of the issues
- Now you can run
lb mark-next-changeset-ranto mark the failed changeset as executed mark-next-changeset-ran(-sql)shows which changeset will be run, but does not run it
Example:
conn -name proj_test
cd dist
lb mark-next-changeset-ran -changelog-file releases/main.changelog.xmlAlias: prj_mr
Drops all objects in the connected schema.
- Usually runs in a VM or disposable environment.
- Also used in Hotfix flow to prepare DEV environment for applying a hotfix
Example:
conn -name proj_vm
cd dist
lb drop-alllb drop-all
--Starting Liquibase at 2025-11-24T14:00:53.818350 using Java 17.0.13 (version 4.30.0 #0 built at 2025-04-01 10:24+0000)
INFO: The drop-all command may result in unrecoverable destructive changes to objects at 'jdbc:oracle:thin:@//10.0.0.158:1521/dev.privsubnet2reg.vcnexample01.example.vcn.com'.
To protect against unwanted drops, set --requireForce=true, which will require a --force=true flag on the command.
Learn more at https://docs.liquibase.com/dropall.
All objects dropped from CLA_DEPLOYER@jdbc:oracle:thin:@//10.0.0.158:1521/dev.privsubnet2reg.vcnexample01.example.vcn.com
Operation completed successfully.
- Tag prod releases for rollbacks.
- Commit staged changes after review.
- You can run git commands from inside SQLcl using
!git ...
Switches the working directory to the given branch.
Example:
git checkout feature_1Creates a new branch and switches into it.
Example:
git checkout feature_1Stages modified files for commit.
Example:
git add .Commits staged files with a message.
Example:
git commit -m "Added missing constraint to SCOTT.EMP"Sends local commits to the remote repository.
#Example ( first push):
git push --set-upstream origin fix_missing_grants
#Subseqent pushes
git pushCreates an annotated tag – usually for PROD releases.
Example:
git tag -a v25.2 -m "Production baseline after April release"
git push origin v25.2Restores files from another branch or commit.
Examples:
git restore --source=main --worktree --staged -- dist/releases/ords
git restore --source=main --worktree --staged dist/releases/
git restore --staged dist/releases/
git clean -fd dist/releases/Merges another branch into the current one.
Example 1: Merge feature to main - Usually should be done on remote server via PR/MR
git checkout main
git merge feature_1Example 2 : Merge main into feature - Usually should be done to resolve merge conflicts
git feature_1
git merge main- Prefer saved connections (
conn -name ...) for consistency.
Establishes a database session.
Example:
connect cla_deployer/password@//dev.example.vcn.com:1521/dev- Create Saved Connection
---DEV Deployer
connect &PROXY_USER[CLA_DEPLOYER]/&PROXY_PASSWORD_DEV@//10.0.0.158:1521/dev.privsubnet2reg.vcnexample01.example.vcn.com
connect -save proj_dev -savepwd -replace- Use Saved Connection
conn -name proj_devChanges working directory inside SQLcl.
Example - Run APEX app install manually:
cd src/database/cla_apex/apex_apps/f100/
@f100.sql- The most importnt case is
cd dist
cd dist
lb mark-next-changeset-ran -changelog-file releases/main.changelog.xml
@install Shows the current working directory inside SQLcl.
Example:
pwd- This is a script that really does the installation. In 99% in non-Prod environment cases you will deploy as
Alias: prj_install
conn -name proj_vm
cd dist
@install
and not as
conn -name proj_vm
project deploy -file artifact/hotfix-app116-25.2.zip -verbose
Exports an APEX application from the connected workspace.
Example:
conn -name proj_dev
apex export -applicationid 110 -split -dir apex_exportLists saved connections
Example:
SQL> connmgr list
.
+--- akluev
+--- dev
+--- dev_apex
+--- inst
+--- prod_apex
+--- prod_pub
+--- prod_ut
+--- proj_dev
+--- proj_dev_ut
+--- proj_fbt
+--- proj_prod
+--- proj_prod_pub
+--- proj_test
+--- proj_vm
+--- test_apex
+--- vm_cdb
|___ vm_sys-- changeset SqlCl:1761249915279 stripComments:false logicalFilePath:dev-01\_custom\ams_config_dml.sql runOnChange:true--preconditions onFail:CONTINUE
--precondition-sql-check expectedResult:0 SELECT count(*) from dual where '${AMS_SERVER_FQDN}' ='$'||'{'||'AMS_SERVER_FQDN'||'}'begin
cla_apex.cla_configuration_pkg.upsert (
p_instance_id =>1 ,
p_key_type_id => 1,
p_key => 'ams_server',
p_description => 'Local AMS (APEX Messaging Service) server URL',
p_value => 'https://${AMS_SERVER_FQDN}',
p_key_group => 'ams'
);
end;
/SQLcl Project substitutes OS-level environment variables during deployment.
These variables must be defined in the operating system, not inside SQLcl or SQL scripts.
If a variable is missing, SQLcl Project does not fail — instead, it inserts the literal placeholder:
'${AMS_SERVER_FQDN}'
This can silently corrupt configuration values across DEV/TEST/PROD.
export AMS_SERVER_FQDN='127.0.0.1'
export API_KEY='abc123'$env:AMS_SERVER_FQDN = "127.0.0.1"
$env:API_KEY = "abc123"set AMS_SERVER_FQDN=127.0.0.1
set API_KEY=abc123These must be set on the machine that runs the deployment.
SQLcl Project will NOT throw an error if a required variable is missing.
It simply writes the literal text ${VAR_NAME} into the database.
Use Liquibase preconditions to ensure variables are set:
--preconditions onFail:CONTINUE
--precondition-sql-check expectedResult:0 SELECT count(*) FROM dual WHERE '${AMS_SERVER_FQDN}' = '$'||'{'||'AMS_SERVER_FQDN'||'}'Behavior:
- Missing variable → condition TRUE → changeset skipped
- Valid variable → condition FALSE → changeset runs