CODEWITHSUNDEEP
javaspringoracle-databasejdbcplsql
s.harris
s.harris

Reputation: 643

Spring JDBC cannot handle DECLARE statement when running SQL Script

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?

Upvotes: 1

Views: 1727

Answers (1)

s.harris
s.harris

Reputation: 643

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.

Upvotes: 2

Related Questions