PL/SQL and SQL script in one sqlFile with liquibase?
The "endDelimiter" works perfectly.
Semicolon in SQL statement produces "invalid character error", so you have to remove it when it isn't a delimiter.(Yes, it does its work in PL/SQL and SQL*Plus, just like a slash "/", more: When do I need to use a semicolon vs a slash in Oracle SQL? )
Solutions:
endDelimiter = "/"
<changeSet id="1" author="me"><sql endDelimiter="/"> BEGIN aud.someProcedure('parameter'); END; / insert into test_table(_id, value) VALUES(1, 'test')</sql></changeSet>
two sections
<changeSet id="1" author="me"><sql endDelimiter="/"> BEGIN aud.someProcedure('parameter'); END;</sql><sql> insert into test_table(_id, value) VALUES(1, 'test');</sql></changeSet>
or maybe ;)
<changeSet id="1" author="me"><sql endDelimiter="#Gabor was here#"> BEGIN aud.someProcedure('parameter'); END; #Gabor was here# insert into test_table(_id, value) VALUES(1, 'test')</sql></changeSet>
Try putting the insert statement inside the Begin, end block and possibly removing the trailing slash (/) character:
BEGIN aud.someProcedure('parameter'); insert into test_table(_id, value) VALUES(1, 'test');END;
The slash is a termination character used by SQL*Plus and some other interactive query tools used to denote the end of a PL/SQL block.
use semicolon in sql script files to separate sql statements that tell client software (SQL*Plus, SQL Developer) what are the single statements to be executed.
use slash in sql script files to separate pl/sql blocks that tell client software (SQL*Plus, SQL Developer) what are the single pl/sql blocks to be executed.
use slash in SQL*Plus command line when you want to execute buffered statement (yes it is a single sql statement without the semicolon or pl/sql block without the slash)