Geek Answers Handbook

PL / SQL and SQL script in one sqlFile with lipibase?

I try to use many parameters to run in the room changeSet / sqlFile, which contains the pl / sql block and simlple sql statement. For instance:.

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 the xml value of splitStatement and endDelimiter, nothing has changed.

Do you have an eny idea?

+5
source share
2 answers

"endDelimiter" works great.

The semicolon in SQL expresses an "invalid character error", so you need to remove it when it is not a separator. (Yes, it does its job in PL / SQL and SQL * Plus, as with the slash "/", more: When do I need to use a semicolon and 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>
+3
source

Try putting the insert statement at the beginning, end of the block, and possibly remove the trailing slash character (/):

 BEGIN aud.someProcedure('parameter'); insert into test_table(_id, value) VALUES(1, 'test'); END;

A slash is the termination character used by SQL * Plus and some other interactive query tools used to mark the end of a PL / SQL block.

0
source

More articles:


All Articles