ExecuteSqlScript with Spring error for PL / SQL block

Question

I am trying to populate my database using a built-in function executeSqlScriptfrom AbstractTransactionalJUnit4SpringContextTestsusing 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 gets the following error. I am not sure what they managed to do in the SQL file that 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 can I express in an SQL file for executeSqlScript? What is the reason for the error when returning?

Answer 1

It looks like you are trying to use PL / SQL functions in a script.

Methods executeSqlScript(..)are AbstractTransactionalJUnit4SpringContextTestsinternally delegated ScriptUtils.executeSqlScript(..)backstage, and ScriptUtilsonly supports pure SQL scripts.

, , , SQL account__id table1.

( ) - , ";" (, "end;"), AbstractTransactionalJUnit4SpringContextTests.executeSqlScript, ScriptUtils.executeSqlScript(..) (, ) ResourceDatabasePopulator.