I previously talked about our new Liquibase support in SQLcl, so you may want to start there if it’s brand new to you.
I shared some slides, and links to the Docs, but not much in the way of demo. I wanted to fix that today, so here’s a quick video showing off SQLcl and Liquibase.
The Plot
I have two schemas:
- LB_DEV
- LB_UAT
I’m going to create two tables in LB_DEV, and call this first go, version 1 (v1).
I’m going to generate a Liquibase changelog of this schema using SQLcl.
LB_DEV SQL> lb genschema
I’m going to update my UAT environment, basically ‘Install v1.’ Note, this is where the cool part comes in – in the previous step, ‘genschema’ has asked SQLcl to create the ‘controller.xml’ and all of the referenced object xml files FOR ME. And, SQLcl has ordered them such in the controller that there won’t be any dependency issues going forward.
Whiz-bang!
LB_UAT SQL> lb update controller.xml false
I’m going to do some work in Dev, add some fancy things like constraints, including a foreign key.
I’m going to see what that would like like in UAT BEFORE I upgrade it to ‘v2.’
LB_UAT SQL> lb updatesql controller.xml false
UPDATESQL says, hey, just show me the SQL you would theoretically run if you were to do an UPDATE with this controller.xml changeset.
Now, let’s actually do the update to ‘v2.’
LB_UAT SQL> lb update controller.xml false
And finally I’m going to show how you can do ‘normal Liquibase stuff’ using SQLcl, like use sqlFile to run some INSERTs to populate my base tables.
5 Comments
Hello Jeff,
I am using SQLcl from VSCode and I see my Liquibase options are complete different from what you discussed in tis article. What could be the reason?
PS: I assume SQLcl in VSCode no different from standalone SQLcl
Following is the log from my VSCode.
SQL> SELECT * FROM v$version;
BANNER BANNER_FULL BANNER_LEGACY CON_ID
_________________________________________________________________________ _________________________________________________________________________ _________________________________________________________________________ _________
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production
Version 19.26.0.1.0 Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production 0
SQL> version
Oracle SQLDeveloper Command-Line (SQLcl) version: 24.4.0.0 build: 24.4.0.345.1707
SQL>
SQL> lb generate-db-object -change-type SQL -object-type table -object-name pd_lb_test
–Starting Liquibase at 2025-03-03T23:43:55.901866800 (version 4.25.0 #3966 built at 2023-11-10 23:07:29 UTC)
Changelog created and written out to file pd_lb_test_table_1.sql
Operation completed successfully.
SQL> liquibase
calculate-checksum data future-rollback-from-tag-sql generate-ords-module mark-next-changeset-ran-sql rollback-to-date tag update-sql
changelog-sync db-doc future-rollback-sql generate-ords-schema release-locks rollback-to-date-sql tag-exists update-testing-rollback
changelog-sync-sql diff generate-apex-object generate-schema rollback set unexpected-changesets update-to-tag
changelog-sync-to-tag diff-changelog generate-changelog history rollback-count show update update-to-tag-sql
changelog-sync-to-tag-sql drop-all generate-controlfile list-locks rollback-count-sql snapshot update-count validate
clear-checksums future-rollback-count-sql generate-db-object mark-next-changeset-ran rollback-sql status update-count-sql version
I published that video 5 years ago. The feature, and it’s commands/syntax have changed, A LOT.
Hello Jeff,
Is there a way to generate control file only for specific objects?
Thanks,
Pradeep.
Genschema with filters?
Hi Jeff,
this sounds eally interesting. I’ve looked into Liquibase some time ago and at that time one needed a “Pro” version of liquibase to be able to version all kind of oracle-objects (such as stored procedures, object types. mv’s). With your “rewrite” of the oracle-liquibase-library, is this still true, or is it possible to use the free version of liquibase?
Regards,
Andreas