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.

Name	Type	Comment
objectName	attribute string required	Name of the dictionary to create
objectType	attribute string optional	This should be always set to DICTIONARY
ownerName	attribute string required	Name of the schema owning this object
replaceIfExists	attribute boolean required	Should object be recreated on execution
source	element string required	Put 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.

Name	Type	Comment
objectName	attribute string required	Name of the procedure to create
objectType	attribute string optional	This should be always set to PROCEDURE
ownerName	attribute string required	Name of the schema owning this object
replaceIfExists	attribute boolean required	Should object be recreated on execution
source	element string required	Put 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.

Name	Type	Comment
objectName	attribute string required	Name of the grant to create
objectType	attribute string optional	This should be always set to OBJECT_GRANT
ownerName	attribute string required	Name of the schema owning this object
replaceIfExists	attribute boolean required	Should object be recreated on execution
source	element string required	Put 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

Name	Type	Comment
objectName	attribute string required	Name of the Package to create
objectType	attribute string optional	This should be always set to PACKAGE_BODY
ownerName	attribute string required	Name of the schema owning this object
replaceIfExists	attribute boolean required	Should object be recreated on execution
source	element string required	Put 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.

Name	Type	Comment
objectName	attribute string required	Name of the job to create
objectType	attribute string optional	This should be always set to JOB
ownerName	attribute string required	Name of the schema owning this object
replaceIfExists	attribute boolean required	Should object be recreated on execution
source	element string required	Put 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

Name	Type	Comment
objectName	attribute string required	Name of the Package to create
objectType	attribute string optional	This should be always set to PACKAGE_SPEC
ownerName	attribute string required	Name of the schema owning this object
replaceIfExists	attribute boolean required	Should object be recreated on execution
source	element string required	Put 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.

Name	Type	Comment
objectName	attribute string required	Name of the Package to create
objectType	attribute string optional	This should be always set to PACKAGE_SPEC
ownerName	attribute string required	Name of the schema owning this object
replaceIfExists	attribute boolean required	Should object be recreated on execution
source	element string required	Put 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

Name	Type	Comment
objectName	attribute string required	Name of the Trigger to create
objectType	attribute string optional	This should be always set to TRIGGER
ownerName	attribute string required	Name of the schema owning this object
replaceIfExists	attribute boolean required	Should object be recreated on execution
source	element string required	Put 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.

Name	Type	Comment
objectName	attribute string required	Name of the type body to create
objectType	attribute string optional	This should be always set to TYPE_BODY
ownerName	attribute string required	Name of the schema owning this object
replaceIfExists	attribute boolean required	Should object be recreated on execution
source	element string required	Put 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.

Name	Type	Comment
objectName	attribute string required	Name of the Type Specification to create
objectType	attribute string optional	This should be always set to TRIGGER
ownerName	attribute string required	Name of the schema owning this object
replaceIfExists	attribute boolean required	Should object be recreated on execution
source	element string required	Put 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.

Name	Type	Comment
objectName	attribute string	Name of the object to create
objectType	attribute string	Type of the object that will be created
ownerName	attribute string	Name of the schema owning this object
replaceIfExists	attribute boolean ??	Should object be recreated on execution. This parameter doesn't exist in XSD but oracle adds it when generating this tag
source	element string required	Full 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 )

Name	Type	Comment
objectName	attribute string required	Name of the object to drop
objectType	attribute string required	Type of the object that will be droped
ownerName	attribute string required	Name of the schema owning this object
source	element string required	From 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

Name	Type	Comment
objectName	attribute string required	Name of the procedure to drop
ownerName	attribute string required	Name of the schema owning this object

<n0:dropOracleprocedure objectName="HELLOLIQUIBASE" ownerName="ADMIN"></n0:dropOracleprocedure>

dropOracleFunction

Name	Type	Comment
objectName	attribute string required	Name of the function to drop
ownerName	attribute string required	Name 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

Name	Type	Comment
objectName	attribute string required	Name of the package to drop
ownerName	attribute string required	Name of the schema owning this object

<n0:dropOraclePackageBody objectName="HELLOLIQUIBASE" ownerName="ADMIN"></n0:dropOraclePackageBody>

dropOraclePackageSpec

Name	Type	Comment
objectName	attribute string required	Name of the package to drop
ownerName	attribute string required	Name 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

Name	Type	Comment
objectName	attribute string required	Name of the trigger to drop
ownerName	attribute string required	Name of the schema owning this object

<n0:dropOracleTrigger objectName="HELLOLIQUIBASE" ownerName="ADMIN"></n0:dropOracleTrigger>

dropOracleTypeBody

Missing from xsd.

Name	Type	Comment
objectName	attribute string required	Name of the type to drop
ownerName	attribute string required	Name of the schema owning this object

        <n0:dropOracleTypeBody objectName="DATA_TYP1" ownerName="SC_CORE"  >
        </n0:dropOracleTypeBody>

dropOracleTypeSpec

Missing from xsd.

Name	Type	Comment
objectName	attribute string required	Name of the type to drop
ownerName	attribute string required	Name of the schema owning this object

        <n0:dropOracleTypeSpec objectName="DATA_TYP1" ownerName="SC_CORE"  >
        </n0:dropOracleTypeSpec>

dropOracleSynonym

Missing from xsd.

Name	Type	Comment
objectName	attribute string required	Name of the type to drop
ownerName	attribute string required	Name of the schema owning this object

        <n0:dropOracleSynonym objectName="DATA_TYP1" ownerName="SC_CORE"  >
        </n0:dropOracleSynonym>

runOracleScript

This one is well documented here

Name	Type	Comment
objectName	attribute string required	This is the name of the script to run , it doesn't seem to do anything
ownerName	attribute string required	Name of the schema to run this script on
sourceType	attribute string required	Set to URL, STRING or FILE
source	element string required	Depending 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.

Name	Type	Comment
objectName	attribute string required	This is the name of the script to run , it doesn't seem to do anything
ownerName	attribute string required	Name of the schema to run this script on
sourceType	attribute string required	Set to URL, STRING or FILE
source	element string required	Depending 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.

Jakub Dobruchowski's blog

Jakub Dobruchowski's blog

SQLCL liquibase tags explained #JoelKallmanDay

Table of contents

Preface

How to use them?

Oracle dbchangelog-ext

Tags

createOracleDictionary

createOracleProcedure

createOracleConstraint

createOracleGrant

createOraclePackageBody

createOracleJob

createOraclePackageSpec

createOraclePublicSynonym

createOracleRefConstraint

createOracleSynonym

createOracleTrigger

createOracleTypeBody

createOracleTypeSpec

createSxmlObject

dropSxmlObject

dropOracleProcedure

dropOracleFunction

dropOracleGrant

dropOraclePackageBody

dropOraclePackageSpec

dropOracleRefConstraint

dropOracleTrigger

dropOracleTypeBody

dropOracleTypeSpec

dropOracleSynonym

runOracleScript

runApexScripts

What's next