executeSqlScript fails with Spring for PL/SQL block

Question

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;

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

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?

Answer 1

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

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

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.

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.