Run Liquibase updates for a specific schema with SQLcl

Have a new version of your application schema to deploy? Let’s demonstrate how to run Liquibase updates for a specific schema.

I have a changeSet for a table called TEST. Let’s take a gander:

<!--?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-latest.xsd">
	<changeset id="8bd00e14f228d04b3653cb1766909f05a4fa8992" author="(HR)-Generated">
		<n0:createsxmlobject objectname="TEST" objecttype="TABLE" ownername="HR">
			<n0:source><!--[CDATA[
  <TABLE xmlns="http://xmlns.oracle.com/ku" version="1.0"-->
   <schema>HR</schema>
   <name>TEST</name>
   <relational_table>
      <col_list>
         <col_list_item>
            <name>A</name>
          ...
</col_list_item></col_list></relational_table></n0:source></n0:createsxmlobject></changeset></databasechangelog>

We have both ‘ownerName=”HR”‘ and ‘<SCHEMA>HR</SCHEMA>’ in this changeSet for a table called ‘TEST.’

changeSet from schema x, login as Y and create in Y

I’m going to create another account, called, ‘NOT_HR.’

X = HR
Y = NOT_HR

CREATE USER not_hr IDENTIFIED BY oracle;
GRANT resource, CONNECT, dba TO not_hr;
ALTER USER not_hr QUOTA 25M ON "USERS";

Now, let’s login as NOT_HR and do an update-sql –

lb update-sql -changelog-file test_table.xml

Don’t trust me? Ok, let’s actually run the update

lb update -changelog-file test_table.xml

Ok, so used a changeSet created based on an object in the HR schema, and used it to create the same object in my NOT_HR schema.

This is the easiest path. Take your changeSets and run them using the user you want them to be applied to. Want to apply them to ABC123 schema? Login as ABC123 and run lb update!

changeSet from schema x, login as z and create in y

X = HR
Y = NOT_HR
Z = NOT_HR2

So we’re going to login Z (NOT_HR2), but create the table in Y (NOT_HR), using a changeSet originally created for X (HR.)

Not shown: me dropping and re-creating the NOT_HR schema.

lb update -changelog-file test_table.xml -default-schema-name NOT_HR

My TEST table has been added in NOT_HR’s schema.

And I can see what happened by looking into NOT_HR’s Liquibase tables. In the DATABASECHANGELOG_ACTIONS table, we record the SQL executed for each changeSet –

Liquibase adds ALTER SESSION SET CURRENT_SCHEMA=NOT_HR before executing the SQL.

changeSet from schema x, login as x and create in Y

X = HR
Y = NOT_HR

This is where we introduce to you an additional flag for the update command, -output-default-schema.

We’ve been using -default-schema-name, which you’ve just seen above.

If we also use -output-default-schema, the schema name is actually emitted in the DDL being executed.

Let’s see that in action.

We’re logged in as HR, our changeSet is from HR, and we’re going to create the table in NOT_HR.

Liquibase updates for a specific schema — lb update -changelog-file test_table.xml -desn NOT_HR -output-default-schema true

-desn is shorthand for -default-schema-name, just as -ouds would substitute for -output-default-schema.

We can see not only was our table created, but Liquibase is including “NOT_HR” in that feedback.

And if we peek into DATABASECHANGELOG_ACTIONS SQL column, that the CREATE TABLE also has the “NOT_HR” schema inserted.

DATABASECHANGELOG_ACTIONS table in my NOT_HR schema.

Let’s end on a trick!

Our lead developer reminded me of this one. If you’re on the end of your liquibase command and you’ve forgotten the name of a parameter or some bit of syntax, you can always add ‘help’ or ‘he’ or ‘help –ex’ to the END.