Liquibase 4.8 breaking the PL/SQL procedure because of the dashes in the quoted string

General Discussion
1

Hi, I have encountered an odd and very ridiculous issue. We were deploying a PL/SQL procedure from the .sql file via Liquibase 4.8 Docker build, and Liquibase broke the .sql code during the deployment.

One of the lines in said procedure was:

DBMS_OUTPUT.put_line ( ’ ---------------EMPTY-----------------’ );

And Liquibase transformed it into this:

DBMS_OUTPUT.put_line ( ’

And by leaving only the one quote, then breaking the entire procedure. What I don’t understand, is, why Liquibase should transfer anything inside the quotes, as it’s a simple tring, where anyone should be able to write anything.

Liquibase 4.8 - Docker Build used by CICD pipeline through Gitlab
Oracle 19c database

(unfortunately we are not able to use any higher version, because, and that is another ridiculous issue, when using endDelimiter:/ as I feel, it’s an industry standard when deploying SQL and PL/SQL changes, whenever this character is used in a mathematical operation (divide), it’s being recognized as an endDelimiter.

It’s documented here, and I find it absurd that no-one has looked into this critical and absurd issue since March.

So sorry, if I am overly agressive, but this tool had already caused our team SOOO much headaches, and has issues with so many small things SQLPlus and SQLDeveloper never had…

2

Can you provide your changeset parameters? You can choose any character you want as the end delimiter.

3

I ran this changeset as a test, with your dbms_output line, and it ran fine, no errors.

<changeSet id="create_procedurev1" author="XYZ" runOnChange="true">
  <sql endDelimiter="/">
    create or replace procedure test_proc is
      v_dummy1 date;
      v_dummy2 date;
    begin
      --This is select 1
      select sysdate into v_dummy1 from "DUAL";
      --This is select 2
      select sysdate-1 into v_dummy2 from dual;
      DBMS_OUTPUT.put_line ( '---------------EMPTY-----------------' );
    end test_proc;
    /  
  </sql>
  <rollback/>
</changeSet>
4

We are using .sql files, and there are the metadata which are in every .sql file Liquibase deploys.

–liquibase formatted sql
–changeset author:datalite endDelimiter:/ runOnChange:true

5

And we are using includeAll tag which deploys every .sql file in the target folder.

6

Your changeset definition is essentially the same as mine, which I just ran without error, the procedure was created as expected:

--liquibase formatted sql
--changeset XYZ:pl-sql-example runOnChange:true endDelimiter:/ stripComments:false
    create or replace procedure test_proc2 is
      v_dummy1 date;
      v_dummy2 date;
    begin
      --This is select 1
      select sysdate into v_dummy1 from "DUAL";
      --This is select 2
      select sysdate-1 into v_dummy2 from dual;
      DBMS_OUTPUT.put_line ( '---------------EMPTY-----------------' );
    end test_proc2;
    /

--rollback not required

Running Changeset: db-changelog-10.sql::pl-sql-example::XYZ
[2022-06-24 19:00:22] INFO [liquibase.changelog] Custom SQL executed
[2022-06-24 19:00:22] INFO [liquibase.changelog] ChangeSet db-changelog-10.sql::pl-sql-example::XYZ ran successfully in 100ms

image
image800×285 18.7 KB

You see I also have two comments lines in my code, with double-dashes style, which cause no issues.

Can you check your quotes in the dbms_output statement to make sure they are not formatted as some alternate microsoft quote? I had to edit and fix them in the code that I cut from your example above.

Also, if your code contains a “/”, then choose another delimiter, like “$” or “GO”. Any delimiter will work as long as it is not in your code.

7

Unfortunately, it was truly the dashes and that was the only thing I changed in that text. After that, it worked as expected. I haven’t touched the quotes at all. I literally just changed dashes to equal signs.

DBMS_OUTPUT.put_line ( ‘=============EMPTY===========’ );

8

That is very strange, glad you got it to work. I will say that I’m using 4.9.1 and I’m not using the docker image, so I’m not sure what difference that is making. But I’ve been using the same pl/sql techniques going back to v3.6 without any issues with dashes.

9

Yeah, it’s very strange indeed, I had trouble understanding why that could be a problem.