The current version has several issues in a very common multi-schema deployment scenario — one intended to support from-nothing-to-everything installations.
We have:
- User:
CLA_DEPLOYER, which runs installations. This is a powerful user with DBA-like privileges. - Application schemas: defined in
.dbtools/project.config.json[ "CLA_APEX", "CLA_PUBLIC", "CLA_UTILITIES" ]
-
When exporting user, system, and role privileges as
CLA_DEPLOYERwith the filter
export_type not in ('USER','USER_SYS_PRIVS','USER_ROLE_PRIVS'),
the tool exports privileges of the current user instead of those belonging to the application schemas. -
When the export is performed separately as each schema user (for example
CLA_APEX, thenCLA_PUBLIC),
the files forCLA_PUBLICoverwrite those created forCLA_APEX.
- Change export types
'USER_SYS_PRIVS'and'USER_ROLE_PRIVS'to useALL/DBA_SYS_PRIVSandALL/DBA_ROLE_PRIVS, and filter by project schemas rather than the connected user. - Apply the same logic for the
'USER'export type. - Introduce a schema-level structure under
src/database/sys, similar tosrc/database:
src/database/sys
+--schema1
| +--object_grants
| | +--object_grants_files.sql
| +--user_role_privs.sql
| +--user_sys_privs.sql
|
+--schemaN
+--object_grants
| +--object_grants_files.sql
+--user_role_privs.sql
+--user_sys_privs.sql
It would also be cleaner and more consistent to move the CREATE USER script from
src/database/<schema>/users/<schema>.sql
to
src/database/sys/<schema>/user.sql.
Some (but not all) wrapped packages that are valid in the source database become invalid when deployed on the target:
CLA_APEX PACKAGE BODY AOP_CONVERT22_PKG 0,0 PLS-00753: malformed or corrupted wrapped unit
After almost 3 hours of troubleshooting I gave up finding the root cause and implemented a workaround instead.
Symptoms:
The package can be compiled error-free when executed directly from SQLcl using the @ syntax:
SQL> set feedback on
SQL> @dist/releases/25.1/changes/prod/cla_apex/package_bodies/ape_qa_utils.sql
Package Body CLA_APEX.APE_QA_UTILS compiled
SQL> show errors
No errors.
SQL>However, when executed by Liquibase during deployment, it fails with a compilation error:
Installing/updating schemas
--Starting Liquibase at 2025-10-31T14:24:34.536062100 using Java 17.0.13 (version 4.30.0 #0 built at 2025-04-01 10:24+0000)
Running Changeset: prod/cla_apex/package_bodies/ape_qa_utils.sql::1761066026494.2::CLA_APEX
Package Body CLA_APEX.APE_QA_UTILS compiled
Errors for PACKAGE BODY CLA_APEX.APE_QA_UTILS:
LINE/COL ERROR
-------- ------------------------------------------------------------
0/0 PLS-00753: malformed or corrupted wrapped unit
When the package is created as valid by running the @ script, any subsequent attempt to recompile it from SQL Developer causes it to become invalid again with the same PLS-00753 error.
So at this point, I’m not sure whether this is a bug in the SQLcl Project, Liquibase integration, or some other underlying Oracle issue.
When running deployments multiple times from SQLcl, Liquibase occasionally fails to execute changesets that are clearly eligible to run.
The issue appears to be related to its internal cache.
A typical example looks like this:
Installing/updating schemas
--Starting Liquibase at 2025-10-31T14:21:28.708231 using Java 17.0.13 (version 4.30.0 #0 built at 2025-04-01 10:24+0000)
Database is up to date, no changesets to execute
UPDATE SUMMARY
Run: 1
Previously run: 2307
Filtered out: 0
-------------------------------
Total change sets: 2308
Produced logfile: sqlcl-lb-1761934888704.log
Operation completed successfully.
As you can see, Liquibase first reports
Database is up to date, no changesets to execute
but immediately afterward includes a summary showing 1 changeset as “Run.”
This indicates that a valid, pending changeset was detected but skipped because the Liquibase cache incorrectly determined the database was already up to date.
- Occurs intermittently when running consecutive deployments in the same SQLcl session.
- Affects any changeset type (DDL, PL/SQL, etc.).
- Leaves the target environment inconsistent with the expected state.
The only reliable workaround found so far is to exit SQLcl and start a new session before re-running the deployment.
Once restarted, Liquibase correctly detects and executes the missing changeset.
If this issue cannot be fixed directly, SQLcl should at least fail with an error when it detects that there are changesets to run but none were executed.
In such cases, it would be better to abort the operation and display a clear message (e.g., “Detected pending changesets that were not executed — possible Liquibase cache issue”) rather than reporting a successful completion.
Expected vs. Actual Behavior
| Aspect | Expected | Actual |
|---|---|---|
| Liquibase update execution | All eligible changesets are executed | Some changesets are skipped due to cache inconsistency |
| SQLcl behavior | Fails or warns when inconsistencies are detected | Reports “Database is up to date” and exits successfully |
The -l (list) option of the proj export command is supposed to only list objects that would be exported, without actually creating or modifying any files.
However, when used for grants, it still creates corresponding grant files on disk — an unexpected and annoying behavior.
Simple Test:
create table cla_apex.t1
( f1 number )
/
grant select on cla_apex.t1 to cla_public
/Run:
SQL> proj export -l -v -o cla_apex.t1
The current connection //localhost:1521/freepdb1 CLA_DEPLOYER will be used for all operations
CLA_APEX.T1
CLA_APEX.object_grants_as_grantor.CLA_APEX.TABLE.T1
-------------------------------
GRANT 1
TABLE 1
-------------------------------
Listed 2 objects
Elapsed 8 sec
SQL>After this, check Git status — you will see that a new file for grants was actually created:
git status
On branch dev-01
Your branch is up to date with 'origin/dev-01'.
Untracked files:
(use "git add <file>..." to include in what will be committed)
src/database/cla_apex/object_grants/object_grants_as_grantor.cla_apex.table.t1.sql
This behavior defeats the purpose of the list-only option and should be treated as a bug.
The export of the USER type currently omits any information about user tablespace quotas.
As a result, recreated users may have no quotas defined in the target environment, which can lead to deployment failures or space allocation issues.
To be complete, the export of USER type should include a query to DBA_TS_QUOTAS (or USER_TS_QUOTAS) and generate the corresponding
ALTER USER ... QUOTA ... ON ... statements for each tablespace.
This omission should be considered a bug.
1.6. STAGE Command Always Regenerates ORDS Changesets Even When 'ORDS_SCHEMA' Export Type Is Disabled
Every time the stage command runs, it regenerates the files:
dist/releases/ords/<schema>/ords.sql
even when there are no changes in ORDS and the ORDS_SCHEMA export type is disabled.
This is very annoying, especially when working with a high volume of changes or during code reviews.
Even more importantly, it may cause developers to overlook real changes in ORDS metadata, because the constant regeneration creates unnecessary noise in Git.
If it is not possible not to generate ORDS files at all, then I would suggest regenerating them with a static ID and the runOnChange:true flag.
This way, even if they are recreated, Git will correctly detect that the file content hasn’t actually changed.
-- changeset CLA_PUBLIC:CLA_PUBLIC_ORDS
stripComments:false
logicalFilePath:ords/cla_public/ords.sql
runOnChange:trueIdeally, the ORDS changesets should not be generated unless there are actual metadata changes, but if that cannot be avoided, this approach would at least make the noise manageable and Git-friendly.
Expected vs. Actual Behavior
| Aspect | Expected | Actual |
|---|---|---|
| ORDS file generation | Files are generated only when ORDS metadata changes | Files are regenerated every time the stage command runs |
| Git diff | Clean, meaningful changes only | Constant noise from regenerated ords.sql files |
If I modify the LB UPDATE command in dist/install.sql as follows:
prompt "Installing/updating schemas"
lb update -debug -log -changelog-file releases/main.changelog.xml -search-path "."and then try to run:
SQL> proj stage -vSQLcl throws an error:
Starting execution of stage command using the current branch
Stage is Comparing:
Old Branch refs/heads/dev-01
New Branch refs/heads/testing
ERROR: An error has occurred processing your request:
The install.sql file in your dist folder is not using the correct call for liquibase project.
This must be fixed before stage can be run, you can remove the file or the dist directory and run stage again.
SQLcl currently enforces a rigid validation of the lb update call inside install.sql.
If any parameters differ from the default form (for example, adding -debug or -log options), the stage command refuses to run.
This limitation appears unnecessarily restrictive.
Developers often need to adjust the LB UPDATE command for valid reasons, such as:
- Enabling debug or verbose logging for troubleshooting
- Changing the location of Liquibase control tables
- Using SQLcl
DEFINEvariables to conditionally include or exclude certain changelogs or options - Adding other standard Liquibase options supported by the CLI
Blocking these legitimate modifications provides no technical benefit and only frustrates users.
Most developers will simply revert the file before running stage, then reapply their modifications afterward — adding manual overhead for no real gain.
SQLcl should not enforce a strict match for the LB UPDATE command syntax.
As long as the command still calls lb update, proj stage should proceed normally.
If validation is needed at all, it should only check that the lb update command exists in the file — not that it matches an exact template.
Expected vs. Actual Behavior
| Aspect | Expected | Actual |
|---|---|---|
install.sql customization |
Users can freely modify lb update command options |
SQLcl rejects modified lb update calls |
| Stage execution | Proceeds as long as a valid lb update is present |
Fails with error forcing revert to default command |
✅ End of Bugs Section