SQLCL liquibase tags explained #JoelKallmanDay

SQLCL liquibase tags explained #JoelKallmanDay

Preface

Here at Pretius we love Liquibase. It is a perfect tool that works at many levels of complexity, from simple generate schema no git setup to complex CI\CD pipelines that automatically install and upgrade dozens of environments. In this article, we will be focusing on the more complex use of oracle flavored liquibase. If you need a refresher on what liquibase is and how to use it you can visit Rafal's blog.

Let’s imagine you would like to write your own XML changeset using all the bells and whistles that Oracle provides, what are your options?

SQLCL documentation gives you a nice but unfortunately lacking table. What are the parameters for those tags? What are they used for? How to use them? Do we even know?

How to use them?

To use those tags you need to add a new XSD to your database change log - Notice the xmlns:n0 parameter in the example below - and use SQLCL to run your changes. Alternatively, you can add the extension to the standalone liquibase, a description of how to do it is in SQLCL documentation.

<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">

Oracle dbchangelog-ext

Unfortunately, while Oracle gives you a path to XSD to add to your XMLs this link leads to nowhere. I did some digging and found the file in SQLCL, but it is missing a lot of tags that Oracle specifies can be used. Hopefully, in future iterations, we will get a working link and full XSD.

Tags

Most of the tags do exactly what is written on the tin, but not all. Parameter specification is taken from dbchangelog-ext where possible but most are a result of me tinkering with them.

On the objectName attribute - Be sure that it matches the name of the object you are creating, SQLCL will use that attribute to generate rollback statements and it will put it into quotation marks. Unless you are using mixed case letters in your tables, triggers, sequences etc. always use all caps for this attribute.

createOracleDictionary

Missing from XSD.

NameTypeComment
objectNameattribute string requiredName of the dictionary to create
objectTypeattribute string optionalThis should be always set to DICTIONARY
ownerNameattribute string requiredName of the schema owning this object
replaceIfExistsattribute boolean requiredShould object be recreated on execution
sourceelement string requiredPut here full ddl to create the dictionary

Example:

        <n0:createOracleDirectory objectName="DATA_PUMP_DIR" objectType="DIRECTORY" ownerName="ADMIN"  replaceIfExists="true" >
            <n0:source><![CDATA[CREATE OR REPLACE DIRECTORY "DATA_PUMP_DIR" AS 'dpdump';]]></n0:source>
        </n0:createOracleDirectory>

createOracleProcedure

Missing from XSD.

NameTypeComment
objectNameattribute string requiredName of the procedure to create
objectTypeattribute string optionalThis should be always set to PROCEDURE
ownerNameattribute string requiredName of the schema owning this object
replaceIfExistsattribute boolean requiredShould object be recreated on execution
sourceelement string requiredPut here full ddl to create the procedure
        <n0:createOracleProcedure objectName="HELLOLIQUIBASE"  ownerName="ADMIN"  replaceIfExists="true" >
            <n0:source><![CDATA[CREATE OR REPLACE EDITIONABLE PROCEDURE "ADMIN"."HELLOLIQUIBASE" 
(
  NAME IN VARCHAR2 
) AS 
BEGIN
  dbms_output.put_line('Hello '||name);
END HELLOLIQUIBASE;
/]]></n0:source>
        </n0:createOracleProcedure>

createOracleConstraint

Missing from XSD.

There is a problem with this one. 1st of all it's missing from XSD, and lb generate-schema or lb generate-object will not use this tag, so there is no way for me to work out the intended use for it. Secondly, I tinkered with it a bit and in the example, you can see what I found working, but lb rollback will not be able to properly run. The same goes for createOracleRefConstraint although it gave me a slightly different error. I would advise you to not use those tags currently.

        <n0:createOracleConstraint objectName="TEST_CONST2" ownerName="SC_CORE"  >
            <n0:source><![CDATA[ ALTER TABLE test_table ADD CONSTRAINT test_const2 UNIQUE (column3); ]]>
            </n0:source>
        </n0:createOracleConstraint>

createOracleGrant

Missing from XSD.

NameTypeComment
objectNameattribute string requiredName of the grant to create
objectTypeattribute string optionalThis should be always set to OBJECT_GRANT
ownerNameattribute string requiredName of the schema owning this object
replaceIfExistsattribute boolean requiredShould object be recreated on execution
sourceelement string requiredPut here full ddl to create the grant
        <n0:createOracleGrant objectName="object_grant0" objectType="OBJECT_GRANT" ownerName="SC_CORE"  replaceIfExists="true" >
            <n0:source><![CDATA[
  GRANT INSERT ON "SC_CORE"."TEST_TABLE" TO "LIQUIBASE_USER"]]></n0:source>
        </n0:createOracleGrant>

createOraclePackageBody

NameTypeComment
objectNameattribute string requiredName of the Package to create
objectTypeattribute string optionalThis should be always set to PACKAGE_BODY
ownerNameattribute string requiredName of the schema owning this object
replaceIfExistsattribute boolean requiredShould object be recreated on execution
sourceelement string requiredPut here full ddl to create the package specification
        <n0:createOraclePackageBody objectName="TEST" objectType="PACKAGE_BODY" ownerName="SC_CORE"  replaceIfExists="true" >
            <n0:source><![CDATA[
  CREATE OR REPLACE EDITIONABLE PACKAGE BODY "SC_CORE"."TEST" as
procedure p_test  as
begin
null;
end p_test;
end TEST;]]></n0:source>
        </n0:createOraclePackageBody>

createOracleJob

Missing from XSD.

NameTypeComment
objectNameattribute string requiredName of the job to create
objectTypeattribute string optionalThis should be always set to JOB
ownerNameattribute string requiredName of the schema owning this object
replaceIfExistsattribute boolean requiredShould object be recreated on execution
sourceelement string requiredPut here code to create the job
<n0:createOracleJob objectName="LIQUI_JOB" objectType="JOB" ownerName="SC_CORE"  replaceIfExists="true" >
            <n0:source><![CDATA[

BEGIN 
dbms_scheduler.create_job('LIQUI_JOB',
job_type=>'PLSQL_BLOCK', job_action=>
'begin
 null;
end;'
, number_of_arguments=>0,
start_date=>TO_TIMESTAMP_TZ('04-OCT-2023 10.01.38.534232000 PM EUROPE/ATHENS','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_interval=> 
'FREQ=YEARLY;BYDATE=1101;BYTIME=213700'
, end_date=>NULL,
job_class=>'DEFAULT_JOB_CLASS', enabled=>FALSE, auto_drop=>FALSE,comments=>
'My test job for blogpost'
);
sys.dbms_scheduler.set_attribute('LIQUI_JOB','NLS_ENV','NLS_LANGUAGE=''ENGLISH'' NLS_TERRITORY=''POLAND'' NLS_CURRENCY=''zł'' NLS_ISO_CURRENCY=''POLAND'' NLS_NUMERIC_CHARACTERS='', '' NLS_CALENDAR=''GREGORIAN'' NLS_DATE_FORMAT=''RR/MM/DD'' NLS_DATE_LANGUAGE=''ENGLISH'' NLS_SORT=''BINARY'' NLS_TIME_FORMAT=''HH24:MI:SSXFF'' NLS_TIMESTAMP_FORMAT=''RR/MM/DD HH24:MI:SSXFF'' NLS_TIME_TZ_FORMAT=''HH24:MI:SSXFF TZR'' NLS_TIMESTAMP_TZ_FORMAT=''RR/MM/DD HH24:MI:SSXFF TZR'' NLS_DUAL_CURRENCY=''zł'' NLS_COMP=''BINARY'' NLS_LENGTH_SEMANTICS=''BYTE'' NLS_NCHAR_CONV_EXCP=''FALSE''');
dbms_scheduler.enable('LIQUI_JOB');
COMMIT; 
END; 
]]></n0:source>
        </n0:createOracleJob>

createOraclePackageSpec

NameTypeComment
objectNameattribute string requiredName of the Package to create
objectTypeattribute string optionalThis should be always set to PACKAGE_SPEC
ownerNameattribute string requiredName of the schema owning this object
replaceIfExistsattribute boolean requiredShould object be recreated on execution
sourceelement string requiredPut here full ddl to create the package specification
        <n0:createOraclePackageSpec objectName="TEST" objectType="PACKAGE_SPEC" ownerName="SC_CORE"  replaceIfExists="true" >
            <n0:source><![CDATA[
CREATE OR REPLACE EDITIONABLE PACKAGE "SC_CORE"."TEST" AS 
procedure p_test;
end;
]]></n0:source>
        </n0:createOraclePackageSpec>

createOraclePublicSynonym

Missing from XSD. Lb generate-schema will not use this tag. Functions the same as createOracleSynonym.

createOracleRefConstraint

Missing from XSD. See createOracleConstraint.

createOracleSynonym

Missing from XSD. Lb generate-schema will use this tag even for public synonyms.

NameTypeComment
objectNameattribute string requiredName of the Package to create
objectTypeattribute string optionalThis should be always set to PACKAGE_SPEC
ownerNameattribute string requiredName of the schema owning this object
replaceIfExistsattribute boolean requiredShould object be recreated on execution
sourceelement string requiredPut here full ddl to create the package specification
        <n0:createOracleSynonym objectName="LOGGER_LOGS_5_MIN" objectType="SYNONYM" ownerName="SC_CORE"  replaceIfExists="true" >
            <n0:source><![CDATA[
  CREATE OR REPLACE EDITIONABLE SYNONYM "SC_CORE"."LOGGER_LOGS_5_MIN" FOR "LOGGER_USER"."LOGGER_LOGS_5_MIN"]]></n0:source>
        </n0:createOracleSynonym>

createOracleTrigger

NameTypeComment
objectNameattribute string requiredName of the Trigger to create
objectTypeattribute string optionalThis should be always set to TRIGGER
ownerNameattribute string requiredName of the schema owning this object
replaceIfExistsattribute boolean requiredShould object be recreated on execution
sourceelement string requiredPut here full ddl to create the trigger
        <n0:createOracleTrigger objectName="TEST_TRIGGER" objectType="TRIGGER" ownerName="SC_CORE"  replaceIfExists="true" >
            <n0:source><![CDATA[
  CREATE OR REPLACE EDITIONABLE TRIGGER "SC_CORE"."TEST_TRIGGER" 
BEFORE DELETE OR INSERT OR UPDATE ON TEST_TABLE 
BEGIN
  NULL;
END;
/
ALTER TRIGGER "SC_CORE"."TEST_TRIGGER" ENABLE]]></n0:source>
        </n0:createOracleTrigger>

createOracleTypeBody

Missing from XSD.

NameTypeComment
objectNameattribute string requiredName of the type body to create
objectTypeattribute string optionalThis should be always set to TYPE_BODY
ownerNameattribute string requiredName of the schema owning this object
replaceIfExistsattribute boolean requiredShould object be recreated on execution
sourceelement string requiredPut here full ddl to create the type body
        <n0:createOracleTypeBody objectName="DATA_TYP1" objectType="TYPE_BODY" ownerName="SC_CORE"  replaceIfExists="true" >
            <n0:source><![CDATA[
  CREATE OR REPLACE EDITIONABLE TYPE BODY "SC_CORE"."DATA_TYP1" IS   
      MEMBER FUNCTION prod (invent NUMBER) RETURN NUMBER IS 
         BEGIN 
             RETURN (year + invent);
         END; 
      END; ]]></n0:source>
        </n0:createOracleTypeBody>

createOracleTypeSpec

Missing from XSD.

NameTypeComment
objectNameattribute string requiredName of the Type Specification to create
objectTypeattribute string optionalThis should be always set to TRIGGER
ownerNameattribute string requiredName of the schema owning this object
replaceIfExistsattribute boolean requiredShould object be recreated on execution
sourceelement string requiredPut here full ddl to create the type specification
<n0:createOracleTypeSpec objectName="DATA_TYP1" objectType="TYPE_SPEC" ownerName="SC_CORE"  replaceIfExists="true" >
            <n0:source><![CDATA[
  CREATE OR REPLACE EDITIONABLE TYPE "SC_CORE"."DATA_TYP1" AS OBJECT 
   ( year NUMBER, 
     MEMBER FUNCTION prod(invent NUMBER) RETURN NUMBER 
   ); 
]]></n0:source>

createSxmlObject

A bit strange but can be useful in a pinch. This tag lets you create an object defined by SXML. What is SXML? In this context, this is a specification of a given object generated by dbms_metadata with SXML transformation applied. This tag is used by Oracle to generate table changesets when you run lb generate-schema instead of doing separate changesets for each 'parts' of the table.

NameTypeComment
objectNameattribute stringName of the object to create
objectTypeattribute stringType of the object that will be created
ownerNameattribute stringName of the schema owning this object
replaceIfExistsattribute boolean ??Should object be recreated on execution. This parameter doesn't exist in XSD but oracle adds it when generating this tag
sourceelement string requiredFull SXML goes here
<n0:createSxmlObject objectName="TEST_TABLE" objectType="TABLE" ownerName="SC_CORE"  replaceIfExists="true" >
            <n0:source><![CDATA[
  <TABLE xmlns="http://xmlns.oracle.com/ku" version="1.0">
   <SCHEMA>SC_CORE</SCHEMA>
   <NAME>TEST_TABLE</NAME>
   <RELATIONAL_TABLE>
      <COL_LIST>
         <COL_LIST_ITEM>
            <NAME>COLUMN1</NAME>
            <DATATYPE>NUMBER</DATATYPE>
            <NOT_NULL></NOT_NULL>
         </COL_LIST_ITEM>
         <COL_LIST_ITEM>
            <NAME>COLUMN3</NAME>
            <DATATYPE>VARCHAR2</DATATYPE>
            <LENGTH>20</LENGTH>
            <COLLATE_NAME>USING_NLS_COMP</COLLATE_NAME>
         </COL_LIST_ITEM>
         <COL_LIST_ITEM>
            <NAME>COLUMN2</NAME>
            <DATATYPE>DATE</DATATYPE>
         </COL_LIST_ITEM>
      </COL_LIST>
      <PRIMARY_KEY_CONSTRAINT_LIST>
         <PRIMARY_KEY_CONSTRAINT_LIST_ITEM>
            <NAME>TEST_TABLE_PK</NAME>
            <COL_LIST>
               <COL_LIST_ITEM>
                  <NAME>COLUMN1</NAME>
               </COL_LIST_ITEM>
            </COL_LIST>
            <USING_INDEX>
               <INDEX_ATTRIBUTES>
                  <PCTFREE>10</PCTFREE>
                  <INITRANS>20</INITRANS>
                  <MAXTRANS>255</MAXTRANS>
                  <TABLESPACE>DATA</TABLESPACE>
                  <LOGGING>Y</LOGGING>
               </INDEX_ATTRIBUTES>
            </USING_INDEX>
         </PRIMARY_KEY_CONSTRAINT_LIST_ITEM>
      </PRIMARY_KEY_CONSTRAINT_LIST>
      <DEFAULT_COLLATION>USING_NLS_COMP</DEFAULT_COLLATION>
      <PHYSICAL_PROPERTIES>
         <HEAP_TABLE>
            <SEGMENT_ATTRIBUTES>
               <SEGMENT_CREATION_DEFERRED></SEGMENT_CREATION_DEFERRED>
               <PCTFREE>10</PCTFREE>
               <PCTUSED>40</PCTUSED>
               <INITRANS>10</INITRANS>
               <MAXTRANS>255</MAXTRANS>
               <TABLESPACE>DATA</TABLESPACE>
               <LOGGING>Y</LOGGING>
            </SEGMENT_ATTRIBUTES>
            <COMPRESS>N</COMPRESS>
         </HEAP_TABLE>
      </PHYSICAL_PROPERTIES>
   </RELATIONAL_TABLE>
</TABLE>]]></n0:source>
        </n0:createSxmlObject>

dropSxmlObject

So this should remove your object. It will just try and drop anything you will give it. I allowed myself a small joke in this example ( 'lb up' will try to execute it )

NameTypeComment
objectNameattribute string requiredName of the object to drop
objectTypeattribute string requiredType of the object that will be droped
ownerNameattribute string requiredName of the schema owning this object
sourceelement string requiredFrom my testing, it doesn't matter what you put here, but it can't be empty
        <n0:dropSxmlObject objectName="HOT" ownerName="SC_CORE" objectType='IT LIKE IT&apos;S ' >
            <n0:source>Anything can go here
            </n0:source>
        </n0:dropSxmlObject>

dropOracleProcedure

NameTypeComment
objectNameattribute string requiredName of the procedure to drop
ownerNameattribute string requiredName of the schema owning this object
<n0:dropOracleprocedure objectName="HELLOLIQUIBASE" ownerName="ADMIN"></n0:dropOracleprocedure>

dropOracleFunction

NameTypeComment
objectNameattribute string requiredName of the function to drop
ownerNameattribute string requiredName of the schema owning this object
<n0:dropOracleFunction objectName="HELLOLIQUIBASE" ownerName="ADMIN"></n0:dropOracleFunction>

dropOracleGrant

Missing from xsd. I was unable to work out how to make that one work. It requires objectName and ownerName parameters but even those trow an error.

dropOraclePackageBody

NameTypeComment
objectNameattribute string requiredName of the package to drop
ownerNameattribute string requiredName of the schema owning this object
<n0:dropOraclePackageBody objectName="HELLOLIQUIBASE" ownerName="ADMIN"></n0:dropOraclePackageBody>

dropOraclePackageSpec

NameTypeComment
objectNameattribute string requiredName of the package to drop
ownerNameattribute string requiredName of the schema owning this object
<n0:dropOraclePackageSpec objectName="HELLOLIQUIBASE" ownerName="ADMIN"></n0:dropOraclePackageSpec>

dropOracleRefConstraint

Missing from xsd. Unfortunatly i wasn't able to make that one work.

dropOracleTrigger

NameTypeComment
objectNameattribute string requiredName of the trigger to drop
ownerNameattribute string requiredName of the schema owning this object
<n0:dropOracleTrigger objectName="HELLOLIQUIBASE" ownerName="ADMIN"></n0:dropOracleTrigger>

dropOracleTypeBody

Missing from xsd.

NameTypeComment
objectNameattribute string requiredName of the type to drop
ownerNameattribute string requiredName of the schema owning this object
        <n0:dropOracleTypeBody objectName="DATA_TYP1" ownerName="SC_CORE"  >
        </n0:dropOracleTypeBody>

dropOracleTypeSpec

Missing from xsd.

NameTypeComment
objectNameattribute string requiredName of the type to drop
ownerNameattribute string requiredName of the schema owning this object
        <n0:dropOracleTypeSpec objectName="DATA_TYP1" ownerName="SC_CORE"  >
        </n0:dropOracleTypeSpec>

dropOracleSynonym

Missing from xsd.

NameTypeComment
objectNameattribute string requiredName of the type to drop
ownerNameattribute string requiredName of the schema owning this object
        <n0:dropOracleSynonym objectName="DATA_TYP1" ownerName="SC_CORE"  >
        </n0:dropOracleSynonym>

runOracleScript

This one is well documented here

NameTypeComment
objectNameattribute string requiredThis is the name of the script to run , it doesn't seem to do anything
ownerNameattribute string requiredName of the schema to run this script on
sourceTypeattribute string requiredSet to URL, STRING or FILE
sourceelement string requiredDepending on sourceType it's either the content of the script to run or a path or URL to script to execute

Example taken from documentation:

   <n0:runOracleScript objectName="myScript" ownerName="JDOE" sourceType="STRING">
       <n0:source><![CDATA[DEFINE table_name = RUNNERSTRING;create table &&table_name (id number);]]></n0:source>
    </n0:runOracleScript>

runApexScripts

This one is undocumented and used by Oracle in creating changesets that install Apex applications. I didn't do any testing on this one. It seems that compared to runOracleScript it adds some context values and probably sets the correct security group.

NameTypeComment
objectNameattribute string requiredThis is the name of the script to run , it doesn't seem to do anything
ownerNameattribute string requiredName of the schema to run this script on
sourceTypeattribute string requiredSet to URL, STRING or FILE
sourceelement string requiredDepending on sourceType it's either the content of the script to run or a path or URL to script to execute
  <n0:runApexScript objectName="install" objectType="SCRIPT" ownerName="LIQUIBASE_USER" sourceType="STRING" >
        <n0:source><![CDATA[
-- your apex script goes here
-- you can also call a file like this
--@@path/to/file/install.sql
]]>
<n0:source>
</n0:runApexScript>

What's next

Hopefully, this list will save you a little bit of time next time You decide to write your own XML changesets. In the future, I plan to release part 2 where we will take a look at how those tags translate into automatic rollbacks.