Dropping an Oracle table with SQLcl and Liquibase

You’re using Liquibase to manage your Oracle application schemas, and you’re also using SQLcl to make that easier.

Now you want to get rid of a table. How can we do that? You’ve probably heard us talk about the automated changeLots and UPDATES/ROLLBACKs quite a bit, but the power and flexibility of Liquibase isn’t sacrificed just because you choose to code the changeSets for yourself!

While we could write a SQL script to do a DROP TABLE (IF EXISTS), and run that, it can be much easier for folks browsing logs and DIFF reports to identify what’s happened if you use single-task types of operations vs a ‘generic whatever SQL I want’ type of changeSet.

Let’s see what Liquibase gives us.

Choice 1: Use the dropTable changeSet (KILL -9 !!!)

This example uses XML as an example. Looking for JSON or YAML instead? You’ll need to use SQLcl version 22.4, and I have examples here.

I’m going to drop my table, ‘TABLE1’ from my ‘JEFE’ schema.

And I’m borrowing my changeSet directly from the Liquibase docs.

Here’s the actual XML I’m using.

<?xml version="1.0" encoding="UTF-8"?>
 <databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog" 
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
        xmlns:n0="http://www.oracle.com/xml/ns/dbchangelog-ext" 
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog 
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.6.xsd">
<changeSet  author="thatjeffsmith-demos-XML-drop-table-liquibase-docs"  id="dropTable-example">  
    <dropTable  cascadeConstraints="true"  
            schemaName="JEFE"  
            tableName="TABLE1"/>  
</changeSet>
</databaseChangeLog>

Ok, I’m going to connect as JEFE to my database with SQLcl, and run the changeLog.

If I browse my schema, I won’t see ‘TABLE1’ anymore, and I look at the DATABASECHANGELOG table Liquibase uses to maintain the schema state, I can see the changeLog ran my changeSet.

dropTable doesn’t use the PURGE keyword

This means that for awhile at least, your table should be available in the Recycle Bin.

Let’s look at a safer approach.

Is this the ‘best’ route to take What if we just renamed it?

I’m really hesitant about dropping objects or even columns out of tables. It’s destructive, and sometimes hard to recover from.

I like a softer approach.

Renaming objects, if only temporarily. If someone ‘breaks the build’, it’s much easier to rename the object back, then recreating it, especially if there’s data attached.

Liquibase also gives us a renameTable changeSet.

So let’s run a renameTable instead –

<?xml version="1.0" encoding="UTF-8"?>
 <databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog" 
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
        xmlns:n0="http://www.oracle.com/xml/ns/dbchangelog-ext" 
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog 
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.6.xsd">
<changeSet  author="liquibase-docs"  id="renameTable-table1-table_one">  
    <renameTable  newTableName="TABLE_ONE"  
            oldTableName="TABLE1"  
            schemaName="JEFE"/>  
</changeSet>
</databaseChangeLog>

And here we go –

This approach doesn’t come for free either, unfortunately. You need to be careful about renaming columns and tables, especially when you actually mean to keep them around!

If you are using our automated changeLogs and updates, if you submit a changeSet where a table has had its column renamed, the database will see that you have an unused column, drop it, and add your new one. Bad, bad, bad!

The database doesn’t know that column XYZ used to be column ABC…so to correct this deficiency we’ll have a -force flag for updates later this year that will be required before SQLcl and the database allow for any destructive DDL to be performed on your schema.

In the meantime, TEST your changeLogs! A great way to do this is via the update-sql command. You can see the SQL/DDL that will be performed based on the current state of the database and the provided changeLog.