end-delimiter SQL attribute

The end-delimiter attribute can be set in a sql or sqlFile Change Type to override the default value of ;. The end-delimiter can be set to '' or to a character other than ; to indicate the end of the SQL statement. The attribute is generally used to prevent incomplete SQL statements from being processed when the SQL files contain statements referencing a ; which Liquibase treats as the default end delimiter.

A corresponding rollback-end-delimiter applies specifically to rollback for sql and sqlFile Change Types.

The end-delimiter can also be set in the Liquibase properties file by setting delimiter=<string>.

Uses

The following examples demonstrate use of the end-delimiters SQL attribute:

Use end-delimiter in SQL for PROCEDURE and FUNCTION

An end-delimiter can be used when the changeset contains SQL to create a stored procedure or function that contains the default ';' end delimiter. To avoid incomplete statements being sent to the database, the changeset must be marked to have a different end-delimiter.

Use end-delimiter for some DBMS systems to run multiple statements

The end-delimiter must be specified for some dbms systems to run multiple statements.

Setting the end-delimiter attribute

Escaping an end-delimiter

When setting an end-delimiter, note that certain DBMS and operating systems require delimiter values to be escaped. For example, a $$ end-delimiter with mysql requires escaping as: end-delimiter="\$\$". A Liquibase migration error indicates the delimiter that needs to be escaped would be:

Unexpected error running Liquibase: Migration failed for change set <changelog>::<id>::<author>:
 Reason: liquibase.exception.DatabaseException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$$$