JDBI incorrectly splits statements for Oracle SQL script

[galzetta]

Hello, I want to use JDBI to run a plain SQL script for an Oracle DB (i.e. JDBI should just forward the SQL to the server and do no interpretation whatsoever ideally). Unfortunately it seems impossible to achieve this due to how JDBI interprets the SQL.

In particulare createScript seems to "split" the SQL into "batches" and execute them separately... but it fails to split the statements correctly. In particular my SQL file contains a TRIGGER definition containing multiple lines of code but JDBI considers the first ; inside the trigger to be the end of the statement resulting in invalid SQL.

The Oracle script is similar to this:

CREATE TABLE EXAMPLE
(
    ID            NUMBER PRIMARY KEY,
    USERNAME      VARCHAR2(128) NOT NULL,
    CREATED_AT    TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE SEQUENCE EXAMPLE_ID_SEQUENCE
    START WITH 1
    INCREMENT BY 1;


CREATE OR REPLACE TRIGGER EXAMPLE_TRIGGER
    BEFORE INSERT
    ON EXAMPLE
    FOR EACH ROW
BEGIN
    SELECT sys_context('USERENV', 'SESSION_USER') INTO :new.USERNAME FROM DUAL; -- JDBI STOPS HERE!
    IF :new.USERNAME = 'something'  THEN
        raise_application_error(-20000, 'Some error');
    END IF;
    SELECT EXAMPLE_ID_SEQUENCE.nextval INTO :new.ID FROM DUAL;
    SELECT CURRENT_TIMESTAMP INTO :new.CREATED_AT FROM DUAL;
    SELECT CURRENT_TIMESTAMP INTO :new.UPDATED_AT FROM DUAL;
END;

When executing this using createScript what happens is that an EXAMPLE_TRIGGER is created but its source code ends after the first line of the body where I wrote JDBI STOPS HERE in the codeblock above. It then gives an error because it finds some SQL which is invalid at top level.

Is this a JDBI bug? Shouldn't JDBI understand where the TRIGGER statement ends? If not how can I run the SQL script without JDBI messing around with splitting it in batches?

[stevenschlansker]

Hi @galzetta , sorry you ran into a problem here. The Script support is very old and none of the developers have access to Oracle tooling so it's very hard for us to test against it.

If you just want Jdbi to submit SQL to the server without trying to split on statements, which should avoid the problem you outline, instead try a normal Statement by just calling handle.execute or handle.createUpdate. That will avoid the semicolon splitting that is causing you trouble.

Hope that helps

[galzetta]

@stevenschlansker Thank you for the fast reply.

Unfortunately that doesn't work too because createUpdate and handle.execute perform argument binding, which clashes with Oracle trigger syntax. You can see inside the trigger definition there is :new.ID for example and JDBI will complain that it sees a binding that is not fullfilled. That's why I was tryng to use createScript which looked the most promising method.

Note that you can have access to some Oracle instance by building a Docker container from here: https://github.com/oracle/docker-images/tree/main/OracleDatabase/SingleInstance

It's a bit of a pain in the ass and the container created is really heavy, however you may consider creating one to at least do some testing on Oracle. This is what I'm using in development.

---

Correct me if I'm wrong, but to me it seems like the issue starts here: https://github.com/jdbi/jdbi/blob/master/core/src/main/java/org/jdbi/v3/core/statement/Script.java#L65
where the Script class splits into statements.

The SqlScriptParser receives a semicolon handler that simply appends the statement, looking at its code here: https://github.com/jdbi/jdbi/blob/master/core/src/main/java/org/jdbi/v3/core/internal/SqlScriptParser.java#L35 I don't see how this could handle more complex definitions like BEGIN ... END of Oracle.

So it seems like the SqlScriptLexer at https://github.com/jdbi/jdbi/tree/master/core/src/main/antlr4/org/jdbi/v3/core/internal/lexer should be augmented and the SqlScriptParser should have some more logic to handle these statements.

If I ever have some time on my hands I might try to revise by Uni languages class and see if I can come up with a PR to improve on this.

[spannm]

Just published PR #2051 to fix this issue by adding support for BEGIN...END blocks found in many flavors of SQL.