sqloracle-databasespring-bootspring-boot-test

PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ; Spring Boot Test issue

Trying to run following script before my testcase using @SpringbootTest

I refer to this script with my @Sql tag

CREATE OR REPLACE TRIGGER SA."TRIGGER"
BEFORE INSERT
ON "SA"."FOO"
FOR EACH ROW
DECLARE
    nextval NUMBER;
BEGIN
    SELECT FOO_SEQUENCE.NEXTVAL
    INTO nextval
    FROM dual;
    :new.ID_FOO := nextval;
END;

this script works in SQL Developer, but not in my Spring project: following error

Caused by: Error : 6550, Position : 68, Sql = BEGIN SELECT FOO_SEQUENCE.NEXTVAL INTO nextval FROM dual, OriginalSql = BEGIN SELECT FOO_SEQUENCE.NEXTVAL INTO nextval FROM dual, Error Msg = ORA-06550: line 1, column 69: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

;

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)

Solution

  • Trying to run following script before my testcase

    In Oracle, a command can only contain a single statement (this helps to prevent SQL injection attacks).

    If you are passing the entire script using JDBC then it will not split the script into individual statements and would send everything to the database; at which point, the database tries to parse the input as a single statement and fails (because you have multiple statements).

    Split the script up into individual statements and send them one-by-one to the database.

    To do that:

    Edit your script so that after every SQL statement there is a / on a blank line after the statement (and no ; after the statement) and then both SQL and PL/SQL statements will have a single, consistent statement terminator. Then you can just split the script into statements looking for \n/\n and send everything in between as individual statements (do not include the statement terminator in what you send).

    Note: This assumes you will not have string literals that contain / on an empty line in your script - if you can have that edge case then you will need a more robust parser.