1. User's Guide
  2. Using Liquibase
  3. Important 23.4 Update with Liquibase Changelog Files

3.11 Important 23.4 Update with Liquibase Changelog Files

For cross-schema use, changelog files generated before SQLcl release 23.4 may require regeneration or manual updates. These alterations are necessary only if you are changing schema names between your export and import with pre-23.4 changelogs.

If you do not regenerate your changelogs or make the manual adjustments, then:

  • Changelogs containing schema names are applied only to the schema named in them, regardless of provided parameters.
  • Changelogs not containing schema names are applied only to the schema you are currently connected to with SQLcl, regardless of provided parameters.

SQLcl release 23.4 introduced a %USER_NAME% replacement for the schema name stored in changesets.

There are two types of changes for manually updating your changelogs with the proper %USER_NAME% substitution:

  • Changelogs with <SCHEMA></SCHEMA> XML element
  • Changelogs that use SQL within a CDATA field

For changelogs with the <SCHEMA></SCHEMA> XML element, replace the content inside with %USER_NAME%.

In the following code block, the two <SCHEMA> XML elements that appear in this code are highlighted using bold font. These are replaced with %USER_NAME% 

<?xml version="1.0" encoding="UTF-8"?>
<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">
 <changeSet id="5b3935a6b05750a24ac6ebc1c646bc6fe4cb4b40" author="(HR)-Generated" failOnError="false"   runOnChange="true" runAlways="false"  >
    <n0:createSxmlObject objectName="MERCHANDISE" objectType="TABLE" ownerName="HR"  replaceIfExists="true" >
        0:source><![CDATA[
<TABLE xmlns="http://xmlns.oracle.com/ku" version="1.0">
<SCHEMA>HR</SCHEMA>
<NAME>MERCHANDISE</NAME>
<RELATIONAL_TABLE>
 <COL_LIST>
  <COL_LIST_ITEM>
   <NAME>ID</NAME>
   <DATATYPE>NUMBER</DATATYPE>
   <IDENTITY_COLUMN>
    <SCHEMA>HR</SCHEMA>
    <GENERATION>ALWAYS</GENERATION>
    <START_WITH>1</START_WITH>
    <INCREMENT>1</INCREMENT>
    <MINVALUE>1</MINVALUE>
    <MAXVALUE>9999999999999999999999999999</MAXVALUE>
    <CACHE>20</CACHE>
   </IDENTITY_COLUMN>
…

After you do the manual adjustment, this section changes to:

<?xml version="1.0" encoding="UTF-8"?>
<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">
  <changeSet id="5b3935a6b05750a24ac6ebc1c646bc6fe4cb4b40" author="(HR)-Generated" failOnError="false"   runOnChange="true" runAlways="false"  >
    <n0:createSxmlObject objectName="MERCHANDISE" objectType="TABLE" ownerName="HR"  replaceIfExists="true" >
     <n0:source><![CDATA[
<TABLE xmlns="http://xmlns.oracle.com/ku" version="1.0">
<SCHEMA>%USER_NAME%</SCHEMA>
<NAME>MERCHANDISE</NAME>
<RELATIONAL_TABLE>
   <COL_LIST>
     <COL_LIST_ITEM>
       <NAME>ID</NAME>
       <DATATYPE>NUMBER</DATATYPE>
       <IDENTITY_COLUMN>
       <CHEMA>%USER_NAME%</SCHEMA>
       <GENERATION>ALWAYS</GENERATION>
       <START_WITH>1</START_WITH>
       <INCREMENT>1</INCREMENT>
       <MINVALUE>1</MINVALUE>
       <MAXVALUE>9999999999999999999999999999</MAXVALUE>
       <CACHE>20</CACHE>
       </IDENTITY_COLUMN>
…

For changelogs that use SQL within the CDATA field, attach "%USER_NAME%". to the front of all database object references. If a schema name is in these locations, replace it with "%USER_NAME%". Note that there is a period after "%USER_NAME%".

Two examples are shown below, each showing a changelog file before and after the manual adjustment, with the area of adjustment highlighted in bold font in the code block for emphasis.

Example 1

<?xml version="1.0" encoding="UTF-8"?>
<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">
<changeSet id="f1b1bf677cf990caa0147f863ea18486cdf23815" author="(LBUSER)-Generated" failOnError="false" runOnChange="false" runAlways="false" >
<n0:createOracleRefConstraint objectName="EMP_JOB_FK" objectType="REF_CONSTRAINT" ownerName="LBUSER" replaceIfExists="false" >
<n0:source><![CDATA[ALTER TABLE "LBUSER"."EMPLOYEES" ADD CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID") REFERENCES "LBUSER"."JOBS" ("JOB_ID") ENABLE;]]></n0:source>
</n0:createOracleRefConstraint>
</changeSet>
</databaseChangeLog>

After you do the manual adjustment, this section changes to:

<?xml version="1.0" encoding="UTF-8"?>
<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">
<changeSet id="f1b1bf677cf990caa0147f863ea18486cdf23815" author="(LBUSER)-Generated" failOnError="false" runOnChange="false" runAlways="false" >
<n0:createOracleRefConstraint objectName="EMP_JOB_FK" objectType="REF_CONSTRAINT" ownerName="LBUSER" replaceIfExists="false" >
<n0:source><![CDATA[ALTER TABLE "%USER_NAME%"."EMPLOYEES" ADD CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID") REFERENCES "%USER_NAME%"."JOBS" ("JOB_ID") ENABLE;]]></n0:source>
</n0:createOracleRefConstraint>
</changeSet>
</databaseChangeLog>

Example 2

<?xml version="1.0" encoding="UTF-8"?>
<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">
  <changeSet id="2b1ce58fc2f1781ce70d5bb99bf23b6ae8741ff3" author="(TEST9)-Generated" failOnError="false"   runOnChange="false" runAlways="false"  >
     <n0:createOracleProcedure objectName="P_SQLCLERROR_PROCEDURE" objectType="PROCEDURE" ownerName="TEST9"  replaceIfExists="false" >
       <n0:source><![CDATA[CREATE OR REPLACE EDITIONABLE PROCEDURE "TEST9"."P_SQLCLERROR_PROCEDURE" is
begin
null;
end p_sqlclerror_procedure;
/]]></n0:source>
                </n0:createOracleProcedure>
        </changeSet>
</databaseChangeLog>

After you do the manual adjustment, this section appears as follows:

<?xml version="1.0" encoding="UTF-8"?>
<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">
  <changeSet id="2b1ce58fc2f1781ce70d5bb99bf23b6ae8741ff3" author="(TEST9)-Generated" failOnError="false"   runOnChange="false" runAlways="false"  >
    <n0:createOracleProcedure objectName="P_SQLCLERROR_PROCEDURE" objectType="PROCEDURE" ownerName="TEST9"  replaceIfExists="false" >
      <n0:source><![CDATA[CREATE OR REPLACE EDITIONABLE PROCEDURE "%USER_NAME%"."P_SQLCLERROR_PROCEDURE" is
begin
null;
end p_sqlclerror_procedure;
/]]></n0:source>
          </n0:createOracleProcedure>
         </changeSet>
</databaseChangeLog>