PL/SQL and SQL script in one sqlFile with liquibase?
Solution 1
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>
Solution 2
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.
Admin
Updated on July 20, 2022Comments
-
Admin almost 2 years
I try very lot options to run in room changeSet/sqlFile which contains pl/sql block and simlple sql statement. E.g.:
BEGIN aud.someProcedure('parameter'); END; / insert into test_table(_id, value) VALUES(1, 'test');
But I get the following exception:
liquibase.exception.MigrationFailedException: Migration failed for change set [xml path]: Reason: liquibase.exception.DatabaseException: ORA-06550: 4 line, 1 col: PLS-00103: Encountered the symbol: "/" [Failed SQL: BEGIN aud.someProcedure('parameter'); END; // insert into test_table(_id, value) VALUES(1, 'test') ] at liquibase.changelog.ChangeSet.execute(ChangeSet.java:584) at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:51) at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:73) at liquibase.Liquibase.update(Liquibase.java:210) at liquibase.Liquibase.update(Liquibase.java:190) at liquibase.Liquibase.update(Liquibase.java:186) at org.jenkinsci.plugins.liquibase.builder.LiquibaseBuilder.perform(LiquibaseBuilder.java:128) at hudson.tasks.BuildStepMonitor$1.perform(BuildStepMonitor.java:20) at hudson.model.AbstractBuild$AbstractBuildExecution.perform(AbstractBuild.java:779) at hudson.model.Build$BuildExecution.build(Build.java:205) at hudson.model.Build$BuildExecution.doRun(Build.java:162) at hudson.model.AbstractBuild$AbstractBuildExecution.run(AbstractBuild.java:537) at hudson.model.Run.execute(Run.java:1741) at hudson.model.FreeStyleBuild.run(FreeStyleBuild.java:43) at hudson.model.ResourceController.execute(ResourceController.java:98) at hudson.model.Executor.run(Executor.java:381) Caused by: liquibase.exception.DatabaseException: ORA-06550: 4 line, col oszlop: PLS-00103: Encountered the symbol: "/" [Failed SQL: BEGIN aud.someProcedure('parameter'); END; // insert into test_table(_id, value) VALUES(1, 'test') ]
When I try to change in the xml the splitStatement and endDelimiter, nothing changed.
Do you have eny idea?