带有liquibase的一个sqlFile中的PL/SQL和SQL脚本? [英] PL/SQL and SQL script in one sqlFile with liquibase?
问题描述
我尝试了很多选项,以便在包含pl/sql块和简单sql语句的room changeSet/sqlFile中运行.例如:
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');
但是我得到以下异常:
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')
]
当我尝试在xml中更改splitStatement和endDelimiter时,什么都没有改变.
When I try to change in the xml the splitStatement and endDelimiter, nothing changed.
你有主意吗?
推荐答案
"endDelimiter"完美运行.
The "endDelimiter" works perfectly.
SQL语句中的分号会产生无效字符错误",因此,当它不是分隔符时,必须将其删除. (是的,它在PL/SQL和SQL * Plus中工作,就像斜杠"/"一样,更多:
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? )
解决方案:
-
endDelimiter ="/"
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>
这篇关于带有liquibase的一个sqlFile中的PL/SQL和SQL脚本?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!