Liquibase - loading many Oracle triggers into a single file

Question

I have several Oracle triggers stored in a file that we upload to our database using sqlplus. We want to use Liquibase instead, but I really don't want to split triggers into separate files. The file looks like this:

create or replace trigger Trig1 ... ... end Trig1; / create or replace trigger Trig2 ... ... end Trig2; / ...etc. 

I tried

 <sqlFile splitStatements="true" endDelimiter="/" path="triggers.sql"> 

but he is still trying to divide by ';'. If I change splitStatements to false. it then ignores the "/" and includes everything as an invalid single trigger. Any ideas?

TIA.

Answer 1

I understand that this is an old problem, and the OP moved on, but for someone else I found the answer here: http://forum.liquibase.org/topic/oracle-end-delimiter-issue .

It turns out that "endDelimiter" is a regular expression - this line should be used:

 \n/\s*\n|\n/\s*$ 

Answer 2

I recently got this problem. endDelimiter should be:

 endDelimiter="(?m)^/$" 

Answer 3

I have a similar problem with Oracle 11g and Liquibase. I get ORA-00911. In my db-changelog.xml, I point to the sql file where I have triggers. This does not work at all. I tested what you said above with / \;

 CREATE OR REPLACE TRIGGER ADRESSE_ID_TR BEFORE INSERT ON ADRESSE FOR EACH ROW WHEN (new.ID IS NULL) BEGIN SELECT adresse_seq.NEXTVAL INTO :new.ID FROM dual; END ADRESSE_ID_TR;\ 

My workaround is to add in to db-changelog.xml. I do not like this because db-changelog.xml will be very large and I want it in .sql files not in db-changelog.xml.

Another problem is when I create DDL with tools like Oracle SQL Developer and paste them, they do not work. Many sql do not work, may not be supported. I spend a lot of time testing my SQL and Liquibase with Eclipse to fix SQL. Any tips or will you fix it?

Answer 4

This syntax looks correct. EndDelimiter should say that it overrides the default value; delimiter and use / instead. What version of Liquibase are you using? Does it help if you put \ at ease?

  create or replace trigger Trig1 ... ... end Trig1; / create or replace trigger Trig2 ... ... end Trig2; / ...etc.