Spring JDBC cannot handle DECLARE statement when running SQL Script

Question

I have a Spring CRUD application, with an Oracle SQL database.

I have several integration tests and use the @Sql annotation to run .sql files which put the database in a desired state before running the test. I have had no problem with any of my .sql scripts so far, although they have all be very simple INSERT and DROP statements.

I am now trying to set up a scenario in which the database holds thousands of records in a particular table. I am not bothered about the content, only the number of records.

In order to replicate this scenario, I have written the following SQL script:

DECLARE
    id integer := 1;
BEGIN
    WHILE id <= 3000
        LOOP
            INSERT INTO USER (ID, FIRST_NAME, LAST_NAME, AGE)
            VALUES (id, 'John', 'Smith', 32);
            id := id + 1;
        END LOOP;
END;

I have ran this script using IntelliJ and it works as expected.

However, when I put the script into a @Sql annotation, I run into the following error:

org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of class path resource [sql/setUp.sql]: DECLARE id integer := 1; nested exception is java.sql.SQLException: ORA-06550: line 1, column 23:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   * & = - + ; < / > at in is mod remainder not rem
   <an exponent (**)> <> or != or ~= >= <= <> and or like like2
   like4 likec between || multiset member submultiset

I am at a loss as to the solution; I have encountered similar issues where the solution was just a missing semi-colon, but that doesn't seem to be the case here. What's even more mystifying is that I tried running the following script via the @Sql annotation:

BEGIN
    INSERT INTO USER (ID, FIRST_NAME, LAST_NAME, AGE)
    VALUES (1, 'John', 'Smith', 32);
END;

This produced the following error, which makes even less sense:

org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #1 of class path resource [sql/test.sql]: BEGIN INSERT INTO USER (ID, FIRST_NAME, LAST_NAME, AGE) VALUES (1, 'John', 'Smith', 32); nested exception is java.sql.SQLException: ORA-06550: line 1, column 87:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   ;

Most interesting to note is that removing the BEGIN and END statements (leaving just the INSERT INTO statement) works.

So does Spring JDBC simply not support BEGIN/END blocks? Are there limitations to the pre test @Sql annotation that I'm not aware of? Or have I missed something painfully obvious?

Please provide a minimal reproducible example. The problem seems to be that whatever you're doing is splitting on ; to send individual statements (as JDBC itself only supports individual statement execution). To execute scripts like this usually requires switching or disabling statement terminators in some way to be able to send the whole thing. That means you will probably have to fine-tune the @SqlConfig of the @Sql (eg the value of separator). — Mark Rotteveel
@MarkRotteveel: the Oracle JDBC driver does support running an anonymous PL/SQL block because it is in fact one single statement. It's probably Spring trying to split up what looks like multiple statements — a_horse_with_no_name
@a_horse_with_no_name Yes, the default of @Sql is to split on ;. So as far as Spring is concerned, after every ; it encounters, a new statement starts. — Mark Rotteveel
Not an answer because I'm not sure it's right, but you might try adding another line after the END; statement with just a single / character in the first column. — Bob Jarvis - Reinstate Monica
@MarkRotteveel thanks to your suggestion, I found a solution. Much appreciated! — s.harris

s.harris s.harris · Accepted Answer · 2020-02-12T12:39:11

Thanks to @MarkRotteveel for his suggestion, I have discovered the solution to this issue.

JDBC was indeed running each line of the script as if it was a separate statement, which is incompatible with statement blocks. This is because the separator is set to the ; symbol by default, causing JDBC to treat each block of code ended with a ; as if it was a separate script. I set this separator to the EOF symbol, which caused JDBC to treat the whole file as a single script.

My original test code looked something like this:

@Sql(scripts = {"sql/cleanup.sql", "sql/setUp.sql"})
public void testWithThousandsOfRecords() {
  // do tests
}

My test code now looks like this:

@Sql(scripts = "sql/cleanup.sql")
@Sql(scripts = "sql/setUp.sql", config = @SqlConfig(separator = ScriptUtils.EOF_STATEMENT_SEPARATOR))
public void testWithThousandsOfRecords() {
  // do tests
}

Note that I had to separate my cleanup and setUp scripts, as changing the separator may break some scripts that worked before.

Spring JDBC cannot handle DECLARE statement when running SQL Script

1 Answers