@Sql annotation needs to do statement splitting

[mikehearn]

Expected Behavior

SQL scripts that contain multiple statements separated by semi-colon should work on all databases.

Actual Behaviour

On Oracle you get an error like:

java.sql.SQLException: ORA-03048: SQL reserved word ';' is not syntactically valid following 'DELETE FROM MYTABLE'

This happens because JDBC by spec only accepts one SQL statement per call, although you can use addBatch to add more than one. Some JDBC drivers conveniently split multi-statement strings for you, but they aren't required to do so and Oracle's driver doesn't. So Micronaut needs to do it. This is a general problem and from looking around the internet it seems splitting SQL scripts into statements probably has re-usable implementations elsewhere.

Workaround

Wrap the script in a transaction like this:

BEGIN
stmt1;
stmt2;
COMMIT;
END;

Steps To Reproduce

1. Create a @MicronautTest and use something like @Sql(scripts = "classpath:clear-data.sql", phase = Sql.Phase.BEFORE_ALL)
2. Put multiple statements in clear-data.sql separated by semi-colons on separate lines.
3. Run the test.

Environment Information

Oracle JDBC driver com.oracle.database.jdbc:ojdbc11-production:23.4.0.24.05

Example Application

No response

Version

4.5.0