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