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. This article will focus on the more complex use of Oracle-flavored Liquibase. If you need a refresher on Liquibase and its use, I advise you to read the great Liquibase tutorial written by my friend Rafał.

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 table, but it doesn’t provide enough information. 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 must add a new XSD (XML Schema Definition) 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 the 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">

The dbchangelog-ext extension

Unfortunately, while Oracle gives you a path to an 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, we will get a working link and a full XSD in future iterations.

Tags

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

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

createOracleDictionary

Missing from the 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 the 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 the XSD.

A couple of problems with this one. First 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 its intended use. 

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 run properly. The same goes for createOracleRefConstraint, although it gave me a slightly different error. 

I would advise you not to 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 the 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 the 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 the 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, it’s a specification of a given object generated by dbms_metadata with SXML transformation applied. Oracle uses this tag 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 the XSD. I was unable 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. Unfortunately, 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 is 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 Oracle APEX applications (check out Matt Mulvaney’s Oracle APEX tutorial to learn more about this low-code platform). 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>

Conclusion – what’s next?

And that’s about it. Hopefully, this list will save you some time when you write your own XML changesets. In the future, I plan to release a follow-up to this article, in which I will look at how these tags translate into automatic rollbacks. If you have any questions, reach out to me at jdobruchowski@pretius.com. I also advise you to check other Liquibase-related articles on the Pretius blog:

1. What is Liquibase and how to start using it? Automate your database scripts deployment with this Liquibase tutorial
2. Liquibase for teams: GIT collaboration and easy deployment
3. Boost the management of your Oracle Database version control changes with Liquibase
4. Liquibase rollback – A smart way to do it with Jenkins
5. Testcontainers + Liquibase: Make integration testing easier
6. Use Liquibase to track DB changes without direct access– A quick guide
7. Use SQLcl Liquibase to move all database objects from DEV to the UAT environment