执行SQL脚本因Spring for PL/SQL块而失败

问题描述

我正在尝试使用以下外部SQL文件使用AbstractTransactionalJUnit4SpringContextTests中的内置函数executeSqlScript填充数据库.

Im trying to populate my database using the builtin function executeSqlScript from AbstractTransactionalJUnit4SpringContextTests using the following external SQL file.

declare
   id number;
begin
   insert into table1 (field1) values ('V1') returning account__id  into id;
   insert into table2 (my_id, field2) VALUES (id, 'Value3');
end;

但是我收到以下错误.我不确定我要使用executeSqlScript执行的SQL文件中允许执行的操作.

However im getting the following error. Im not sure what im allowed to do in a SQL file I would like to execute using executeSqlScript.

org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement at line 1 of resource class path resource [testdata.sql]: declare id number; nested exception is java.sql.SQLException: ORA-06550: line 1, column 17:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   := . ( @ % ; not null range default character
Caused by: java.sql.SQLException: ORA-06550: line 1, column 17:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   := . ( @ % ; not null range default character

所以我的问题是:我可以在SQL文件中为executeSqlScript表示什么?接收错误的原因是什么?

So my questions are:What am I allowed to express in the SQL file for executeSqlScript?What is the cause of the error im reciving?

推荐答案

您似乎正在尝试在脚本中使用PL/SQL的功能.

It appears that you are attempting to use features of PL/SQL in your script.

AbstractTransactionalJUnit4SpringContextTests中的executeSqlScript(..)方法在内部委托给ScriptUtils.executeSqlScript(..)在幕后，而ScriptUtils仅支持纯SQL脚本.

The executeSqlScript(..) methods in AbstractTransactionalJUnit4SpringContextTests internally delegate to ScriptUtils.executeSqlScript(..) behind the scenes, and ScriptUtils only supports pure SQL scripts.

因此，您可能需要切换到简单的SQL语句，并找到一种不同的机制来从table1检索account__id的值.

So you'll likely need to switch to simple SQL statements and find a different mechanism for retrieving the value of the account__id from table1.

另一个选项(我没有尝试过 )是将语句分隔符更改为";"以外的其他内容(例如，"end;")，但是您不能通过AbstractTransactionalJUnit4SpringContextTests.executeSqlScript .相反，您需要调用ScriptUtils.executeSqlScript(..)或(也许最好使用)ResourceDatabasePopulator.

Another option (which I have not tried) would be to change the statement separator to something other than ";" (e.g., "end;"), but you cannot do that via AbstractTransactionalJUnit4SpringContextTests.executeSqlScript. Instead, you'd need to invoke ScriptUtils.executeSqlScript(..) or (perhaps preferably) use a ResourceDatabasePopulator.

这篇关于执行SQL脚本因Spring for PL/SQL块而失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！