executeSqlScript 因 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

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

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.

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