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.

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.

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

        <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

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

<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

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

        <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

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

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

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

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

        <n0:dropSxmlObject objectName="HOT" ownerName="SC_CORE" objectType='IT LIKE IT&apos;S ' >
            <n0:source>Anything can go here
            </n0:source>
        </n0:dropSxmlObject>

dropOracleProcedure

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

dropOracleFunction

<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

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

dropOraclePackageSpec

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

dropOracleRefConstraint

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

dropOracleTrigger

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

dropOracleTypeBody

Missing from xsd.

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

dropOracleTypeSpec

Missing from xsd.

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

dropOracleSynonym

Missing from xsd.

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

runOracleScript

This one is well documented here

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.

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