Formatted SQL, endDelimiter issue

The trouble is that the statements coming to the database through the JDBC driver generally need to be sent one at a time, it doesn’t do any splitting of statements like the SQL client applications do. Therefore, Liquibase needs to know how to split them and assumes “;” as where to split because that is standard.

However, that doesn’t work with procedures and other times you really do need a “;”. That is generally a rare case, so I’d suggest only dealing with endDelimiters etc. when you need to.

Even easier than endDelimiter:/ is probabably using splitStatement:false. That will cause liquibase to not do any processing of the SQL in the changeSet and just send it as it is. For most statements, don’t bother with splitStatements tag, but if you have a procedure or something that is causing problems, just add splitStatements:false at that point.

Nathan

Hi,

I am very new to the world of SQL and Databases in general so this issue may be me overlooking something very simple,but it has me abit confused all the same!

The issue I am running into is, whenever a SQL script contains multiple semi-colons Liquibase will return “ORA-00900: invalid SQL statement.”

Here is a simple example that will return this error.

–liquibase formatted sql

–changeset conor:1.3.0_createproc2

CREATE OR REPLACE PROCEDURE conor_proc2

    IS

    BEGIN

      DBMS_OUTPUT.PUT_LINE(‘Hello From The Database!’);

    END conor_proc2;

–rollback drop procedure conor_proc2;

After abit of playing around and research I found that the endDelimiter was most likely the problem. So when I made this change

–changeset conor:1.3.0_createproc2 endDelimiter:/

CREATE OR REPLACE PROCEDURE conor_proc2

    IS

    BEGIN

      DBMS_OUTPUT.PUT_LINE(‘Hello From The Database!’);

    END conor_proc2;

/

–rollback drop procedure conor_proc2;

It works fine. My understanding it has something to do with the fact that the default of endDelimiter is ‘;’.

I have no problem with adding an endDelimiter statement to the changeset as long as it is consistent with every changeset. However what I find if I add a endDelimiter parameter to a changeset that has only 1 semi-colon it runs into problems. For example

–changeset conor:1.2.0_createview endDelimiter:/

CREATE OR REPLACE VIEW CONOR_VIEW1 AS SELECT id

FROM conor_table1;

/

–rollback drop view conor_view1;

Will return “ORA-00933: SQL command not properly ended”.

However if I remove the semi-colon the statement will executed successfully. Similarly if I don’t add the endDelimiter parameter the statement will execute successfully.

–liquibase formatted sql

–changeset conor:1.2.0_createview endDelimiter:/

CREATE OR REPLACE VIEW CONOR_VIEW1 AS SELECT id

FROM conor_table1

/

–rollback drop view conor_view1;

Successfull update.

–changeset conor:1.2.0_createview

CREATE OR REPLACE VIEW CONOR_VIEW1 AS SELECT id

FROM conor_table1;

–rollback drop view conor_view1;

This also works.

I know most likely the issue is to do with my SQL syntax but I am still a bit stumped.

I was hoping someone could shed some light on how exactly does the endDelimiter parameter work. And how I could make using the endDelimiter parameter consistent with simple SQL statements and longer SQL scripts.

Thanks for any help!

Conor.

TL:DR. endDelimiter parameter not needed with SQL statements containing 1 semi-colon. endDelimiter parameter is needed with SQL statements that include multiple semi-colons in formatted SQL.