oracle-sqldeveloperliquibasesqlcl

oracle SQLcl liquibase update does not insert into DATABASECHANGELOG_ACTIONS

I noticed something I can’t explain when trying Oracle sqlcl “build 23.3.0.270.1251” with liquibase. When I use “generate-schema” and “update” to apply my changes from one schema to other, everything works fine and DATABASECHANGELOG_ACTIONS table is loaded with DDL instructions executed. But in some circuntances I need to bring only part of the changes to production.

I ‘d tried “diff-changelog” from dev to production and it generated a correct “changes.xml” containing only the changes I want.

Here is my change:

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">
<changeSet author="ricardo (generated)" id="1698352210917-1">
<modifyDataType columnName="URL_SERVICO" newDataType="varchar(1000 CHAR)" tableName="URL_SERVICO"/>
</changeSet>
</databaseChangeLog>

But, when I ran “update”, after the changes where applied, the DATABASECHANGELOG_ACTIONS was not loaded with the corresponding DDL. That was confirmed with “update-sql”.

SQL> liquibase update-sql -chf v20231026r1714/changes.xml

Result:

--Starting Liquibase at 17:30:58 (version 4.18.0 #5864 built at 2022-12-02 18:02+0000)

-- Loaded 1 change(s)
-- *********************************************************************
-- Update Database Script
-- *********************************************************************
-- Change Log: v20231026r1714/changes.xml
-- Ran at: 10/26/23, 5:30 PM
-- Against: PML_DATA_TLIQUIBASE@jdbc:oracle:thin:@PDB_DES.WORLD
-- Liquibase version: 4.18.0
-- *********************************************************************

-- Lock Database
UPDATE PML_DATA_TLIQUIBASE.DATABASECHANGELOGLOCK SET LOCKED = 1, LOCKEDBY = 'ZZZZ (111.11.0.1)', LOCKGRANTED = SYSTIMESTAMP WHERE ID = 1 AND LOCKED = 0;

-- Changeset v20231026r1714/changes.xml::1698352210917-1::ricardo (generated)
ALTER TABLE PML_DATA_TLIQUIBASE.URL_SERVICO MODIFY URL_SERVICO VARCHAR2(1000 CHAR);

INSERT INTO PML_DATA_TLIQUIBASE.DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, MD5SUM, DESCRIPTION, COMMENTS, EXECTYPE, CONTEXTS, LABELS, LIQUIBASE, DEPLOYMENT_ID) VALUES ('1698352210917-1', 'ricardo (generated)', 'v20231026r1714/changes.xml', SYSTIMESTAMP, 49, '8:ce272fb18fbb857080c5aeef643a9a5f', 'modifyDataType columnName=URL_SERVICO, tableName=URL_SERVICO', '', 'EXECUTED', NULL, NULL, '4.18.0', '8352268410');

-- Release Database Lock
UPDATE PML_DATA_TLIQUIBASE.DATABASECHANGELOGLOCK SET LOCKED = 0, LOCKEDBY = NULL, LOCKGRANTED = NULL WHERE ID = 1;

Operation completed successfully.

SQL>
Solution

  • The changeLogs produced by the “diff-changelog” command, aren't processed with the same SQLcl logic as the changeLogs produced by our generate-object and generate-schema commands.

    The SQL statement and scripts recorded in DATABASECHANGELOG_ACTIONS, are being put there by SQLcl, not by the Liquibase open source project.

    We're planning on enhancing the diff command to bring many new features for Oracle Database developers, and one of the downstream effects of this will be that the resulting changeLogs will do exactl what you're looking for. This is on our roadmap for first half of 2024.