Liquibase doesnt split statements correctly after a declare

[dmcghan]

Environment

Liquibase Version: 4.17.0

Liquibase Extension(s) & Version: Oracle's SQLcl 22.4.0.0

Database Vendor & Version: Oracle Database 19c

Operating System Type & Version: macOS 11.7

Infrastructure Type/Provider: Oracle Cloud

Description

We got the following error recently while running SQLcl's 'lb update' command using a liquibase formatted sql changeset: ORA-00900: invalid SQL statement.

I could narrow it down to when the changeset included both a declaration block in an "instead of" trigger and included multiple statements.

Steps To Reproduce

Given the following controller (main.controller.xml) with pointers to three changsets:

<?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"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                    http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.3.xsd">
  <include file="table.sql" relativeToChangelogFile="true"/>
  <include file="view.sql" relativeToChangelogFile="true"/>
  <include file="instead-of-trigger.sql" relativeToChangelogFile="true"/>
</databaseChangeLog>

And this changeset for the table (table.sql):

-- liquibase formatted sql
-- changeset name:123
create table t (c number);

And this changeset for the view (view.sql):

-- liquibase formatted sql
-- changeset name:124
create or replace view v as select * from t;

The following changeset (instead-of-trigger.sql) for the instead of trigger will fail with a "ORA-00900: invalid SQL statement" error. Note that the changeset includes a declaration section in the trigger and a subsequent alter statement:

-- liquibase formatted sql
-- changeset name:125
create or replace editionable trigger io_tgr instead of
  insert or update or delete on v
  for each row
declare
  insufficient_privileges exception;
  pragma exception_init ( insufficient_privileges, -1031 );
begin
  null;
end io_tgr;
/

alter trigger io_tgr enable;

The following with succeed (without the declaration section):

-- liquibase formatted sql
-- changeset name:125
create or replace editionable trigger io_tgr instead of
  insert or update or delete on v
  for each row
begin
  null;
end io_tgr;
/

alter trigger io_tgr enable;

And the following will succeed (without the trailing alter statement):

-- liquibase formatted sql
-- changeset name:125
create or replace editionable trigger io_tgr instead of
  insert or update or delete on v
  for each row
declare
  insufficient_privileges exception;
  pragma exception_init ( insufficient_privileges, -1031 );
begin
  null;
end io_tgr;
/

It's only when both the declaration section and the trailing alter statement are included in the changeset that the code seems to be parsed/broken up incorrectly for execution.

[FBurguer]

Hello! As you mentioned, liquibase is not parsing correctling and spliting the statements when it shouldnt. There are a few workarounds from this issue and Im going to list some examples bellow:

1. If you want to keep the same changeset you can just add the attribute endDelimeter

-- liquibase formatted sql
-- changeset name:125 endDelimeter:\n/
create or replace editionable trigger io_tgr instead of
  insert or update or delete on v
  for each row
declare
  insufficient_privileges exception;
  pragma exception_init ( insufficient_privileges, -1031 );
begin
  null;
end io_tgr;
/
alter trigger io_tgr enable;

In this example I used \n/ so it only split statements when there is a / on a new line

you can also try something like

-- liquibase formatted sql
-- changeset name:125 endDelimeter:$$
create or replace editionable trigger io_tgr instead of
  insert or update or delete on v
  for each row
declare
  insufficient_privileges exception;
  pragma exception_init ( insufficient_privileges, -1031 );
begin
  null;
end io_tgr;
$$
alter trigger io_tgr enable;

$$ is commonly used on some databases as a splitsatement

2. Another option is seperating the two statements on different changeset and adding the attribute splitStatement:false

-- liquibase formatted sql
-- changeset name:125 splitStatements:false
create or replace editionable trigger io_tgr instead of
  insert or update or delete on v
  for each row
declare
  insufficient_privileges exception;
  pragma exception_init ( insufficient_privileges, -1031 );
begin
  null;
end io_tgr;

-- changeset name:126
alter trigger io_tgr enable;

I hope this helps!