Do not split SQL on delimiters within BEGIN/END blocks for rollback statements

[gallipi]

Search first

• I searched and no similar issues were found

Description

It would be great if this logic could also apply to the rollback statement. #1589

Steps To Reproduce

I've tried it, but it fails when detecting the first ";" So, adding the "rollbackEndDelimiter:/" attribute is needed to solve the problem.

`--changeset liquibase:scheduler_schedule_1 rollbackEndDelimiter:/
--comment: Creation scheduler schedule
BEGIN
SYS.DBMS_SCHEDULER.CREATE_SCHEDULE
(
schedule_name => 'EVERY_07AM'
,start_date => SYSTIMESTAMP
,repeat_interval => 'FREQ=DAILY;INTERVAL=1; BYHOUR=7; BYMINUTE=0;BYSECOND=0'
,end_date => NULL
,comments => 'Scheduler para la ejecucion todos los dias a las 7 de la mañana.'
);
END;
/

/* liquibase rollback
BEGIN
DBMS_SCHEDULER.DROP_SCHEDULE(
schedule_name => 'EVERY_07AM',
force => TRUE
);
END;
/
*/`

Actual Behavior

Attribute "rollbackEndDelimiter" is needed in the changeset definition to make the rollback command work.Otherwise, you get the following error:
Caused by: liquibase.exception.RollbackFailedException: liquibase.exception.DatabaseException: ORA-06550: línea 6, columna 3: PLS-00103: Se ha encontrado el símbolo "end-of-file" cuando se esperaba uno de los siguientes: ; <an identifier> <a double-quoted delimited-identifier> El símbolo ";" ha sido sustituido por "end-of-file" para continualr

Expected/Desired Behavior

Avoid using rollbackEndDelimiter attribute

Liquibase Version

=4.19.1

Database Vendor & Version

any

Liquibase Integration

CLI

Liquibase Extensions

No response

OS and/or Infrastructure Type/Provider

RedHat

Additional Context

No response

Are you willing to submit a PR?

• I'm willing to submit a PR (Thank you!)

[nvoxland]

Yes, that would be a good fix. As our ability to parse sql more smartly than a simple endDelimiter regexp can do so trying to be smarter as a default.

The fix will go into FormattedSqlParser if anyone is interested in picking up the change

[afn7081]

I am interested in taking this up if its still open?