liquibase formatted sql file containing multiline rollback statements to allow Oracle PL/SQL

Question

I am using Liquibase version 3.5.3, writing "formatted sql" files that need to execute PL/SQL (to our Oracle 11g database).

The changeset uses the "endDelimiter:/" option to allow this.

--liquibase formatted sql --changeset mike:51.9 endDelimiter:/ /* -- 'Create/Modify triggers for FIN_PLANS' -- */ BEGIN HIST_PKG.TRIG_HIST_TAB('FIN_PLANS'); END; /

The rollback for this changeset similarly needs to execute PL/SQL, however despite numerous searches I can not find a way to do this. I have tried specifying multiple rollback statements, but each rollback line is executed individually (as in "sql statements") and I don't know how to make it recognise it is a PL/SQL "block".

I've tried the endDelimitor option on the rollback but it is not recognised. I've tried assuming that the endDelimitor option for the changeset also applies to the rollback, but alas no.

What I want is something like ...

--liquibase formatted sql
--changeset mike:51.9 endDelimiter:/
/* --  'Create/Modify triggers for FIN_PLANS' -- */
BEGIN
   HIST_PKG.TRIG_HIST_TAB('FIN_PLANS');
END;
/
--rollback endDelimiter:/
--rollback BEGIN
--rollback  HIST_PKG.TRIG_HIST_TAB('FIN_PLANS');
--rollback END;
--rollback /

My searches have turned up http://forum.liquibase.org/topic/issue-with-pl-sql-and-rollbacks But this solution is using "format xml" (not "format sql"). Also https://liquibase.jira.com/browse/CORE-1608 was created but this has since been closed, as it also points out it can be done with "format xml".

So is it possible to write liquibase rollbacks in "formated sql" files that support calling PL/SQL code?

Mike Mike · Accepted Answer · 2017-07-28T06:34:48

A work colleague has found an answer to this question. You have to set the rollback end delimiter "in the changeset header". Doh!

--liquibase formatted sql
--changeset mike:51.9 endDelimiter:/ rollbackEndDelimiter:/
/* --  'Create/Modify triggers for FIN_PLANS' -- */
BEGIN
   HIST_PKG.TRIG_HIST_TAB('FIN_PLANS');
END;
/
--rollback BEGIN
--rollback  HIST_PKG.TRIG_HIST_TAB('FIN_PLANS');
--rollback END;
--rollback /

Hope that helps anyone else who was struggling like me.

liquibase formatted sql file containing multiline rollback statements to allow Oracle PL/SQL

1 Answers