Sample Header Ad - 728x90

Liquibase on Oracle SQLcl - table and index drops not being propagated

1 vote
0 answers
482 views
I've been using Oracle's SQLcl, specifically the lb command to use Liquibase , for the last day or so to use for versioning my development, staging and production databases, which are using Oracle's Autonomous Data Warehouse (version 19). It works fine when doing the tutorial changes, like the ones listed by Jeff Smith in his video on it here , although the syntax of the commands has changed a bit. I'm reaching a problem when I want to drop an index, if I just execute the SQL statement DROP INDEX EXAMPLE_IDX in my development environment and then run the lb genschema statement, it doesn't drop the index on my staging environment when I run lb update -changelog controller.xml. I think this is because it's generating a fresh Liquibase changelog every time I run lb genschema which seems to overwrite the previous changelog. I'm also having the same behaviour when trying to drop tables etc, and I'm not sure how I am supposed to propogate DROP statements on tables and indexes with the lb tool. Here's what I'm doing (users/passwords etc all changed). Open SQLcl to create the staging database's structure (v1)
{plain}
set cloudconfig wallets/dev.zip
connect user/password@dev_medium
lb genschema

set cloudconfig wallets/staging.zip
connect user/password@staging_medium
lb update -changelog controller.xml
Open SQL Developer on the dev database and delete the index.
{sql}
DROP INDEX INDEXNAME;
Open SQLcl to update the changelog and the staging database.
{plain}
set cloudconfig wallets/dev.zip
connect user/password@dev_medium
lb genschema

set cloudconfig wallets/staging.zip
connect user/password@staging_medium
lb update -changelog controller.xml
After doing these steps, I'm still noticing that the tables and indexes are still there, which means my database behaves differently to what it should be doing! If anyone has knowledge or experience of using the lb tool, SQLcl, or can let me know some best practices that would be amazing.
Asked by Ash Oldershaw (121 rep)
Jul 1, 2020, 01:36 PM
Last activity: Sep 26, 2023, 03:45 PM