SQLcl & Liquibase: running Oracle Scripts in a changeSet

Liquibase has offered for a very long time, the ability to define changeSets using SQL. In fact, according to their survey, it’s the MOST popular way of developing your schema deployment scripts.

preferred method of authoring database scripts 2019 — Top 10 findings liquibase survey 2019 courtesy https://www.liquibase.org/

And it’s no surprise as to to why. You can do pretty much anything you want for a change, vs being limited by the change types provided by Liquibase. Say, if you can’t add the column in the way you wanted to via the addColumn change type, it’s very easy for a database developer to just write their standard…

ALTER TABLE XYZ ADD COLUMN ABC VARCHAR2(25)...

What if you could run SQLcl type stuff though?

I have a dumb example. It’s really here just to show you what we CAN do, not what you SHOULD do. For example, the SPOOL and LOAD commands are quite powerful for writing files and loading data to a table.

What if I wanted to create a changeSet that grabbed data from an existing table and wrote it to a file and load it to another table (silly because we could just do an INSERT AS SELECT)?

Well, we can!

<?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 id="abcd1234567890" author="Generated" failOnError="false"   runAlways="true" >
                <n0:runOracleScript objectName="MY_SCRIPT" objectType="SCRIPT" ownerName="FUNKY" sourceType="STRING" replaceIfExists="false">
                        <n0:source><![CDATA[
cd C:\liquibase\load_table
set sqlformat csv
set feedback off
spool locations.csv
select * from hr.locations;
spool off
create table locations as select * from hr.locations where 1=2;
load locations locations.csv
commit;
                        ]]></n0:source>
                </n0:runOracleScript>
        </changeSet>
</databaseChangeLog>

The script is pretty simple and self-explanatory. A couple of SET commands, a CD, SPOOL, and LOAD.

What’s not straightforward is how this is possible. You’ll note that this isn’t a sql or sqlFile changeType in Liquibase. No, it’s a custom one named ‘runOracleScript.’

n0 is the XML Namespace for our Oracle Liquibase extension.

runOracleScript says, ok, run this code through SQLcl – NOT through Liquibase’s SQL execution routine.

objectType=’SCRIPT’ means we’re going to provide the code right here, but we also support ‘FILE’ and ‘URL’.

Imagine ALL the things you can do in SQLcl and your scripts…that’s now possible in a changeSet!

Simpler, but powerful CLI features

Need something a bit more dynamic? If only we had substitution variables…but wait, we do!

For generating an object –

For seeing what an update would do –

Updatesql shows you what WOULD run if you submitted this changeSet via an update.

Lots and lots of changes coming soon to v20.2

More commands, more features, lots of bug fixes, and enhanced documentation are on the way! And as soon as it’s released you can expect more blogs and videos.

When? The same time the .2 releases normally come out – about 6-7 months into the calendar year.