Semi-colon in quotes fails with invalid statement

Community Answers
1

We have a sql formatted script with a single Insert statement. One of the columns contains a semi-colon in the string as shown below. Liquibase execution fails with “Error: ORA-00933: SQL command not properly ended”. I have tried to override the endDelimiter by adding “endDelimiter:”; ", but that does not work either. I cannot find any documentation on what regular expression syntax is allowed for the endDelimiter property. Has anyone encountered this or have a work-around?

--liquibase formatted sql

--changeset devops:scs-3504.1 dbms:oracle runOnChange:true failOnError:false splitstatements:false
Insert into EVENTS_OWNER.ALTDEF
   (ALTALTID, ALTCLID, ALTDESC, ALTPARA, ALTPRI, 
    ALTDBID, ALTRUN, ALTDCRE, ALTDMAJ, ALTUTIL)
 Values
   ('NEW_STOR_CHK', 
    'EVENTS', 
    'New',
'INITPROC=null;FINALPROC=null;REPORTNAME=NEW_STOR_CHK;FILENAME=NEW_STOR_CHK.xml;HOURS=0;PARAM1=0;PARAM2=0;PARAM3=0', 
    1, 
    'GOLD_PROD', 
    0, 
    sysdate, 
    sysdate, 
    'BATCH');
2

Are you trying to NOT treat the ; in the column string as a delimiter? If that is the case, have you tried defining and using a different character as the delimiter?

3

No. As you can see in the script, the semi-colon is the delimiter for the Insert statement. We still need it to be treated as such. The issue is that Liquibase is finding the semicolon inside the string to be inserted in a column. Unfortunately, the default endDelimiter is just a semicolon. I was able to get it to work by overriding the endDelimiter to ;$ for this changeset. That appears to make it look for a semicolon followed by an end of line character.

1 Like