Does H2 support PLSQL when using Oracle mode?

Viewed 9962

I'm trying to write some functional tests for our java application using H2 in-memory database. Since we use Oracle as our production DB, some of the code is written in PL/SQL. So I switched on Oracle compatibility mode and now my url looks like this: jdbc:h2:mem:test;MODE=Oracle.

The actual application code that I'm having troubles with looks like this (note, both sequence and table have been created before the call):

CallableStatement = con.prepareCall(
    " begin insert into profiles(" + PROFILE_FIELDS + ") " +
    " values (sq_profiles.nextval, ?, ?, ?, ?, ?) " + 
    " returning profile_id into ?; end;");

It fails with the same error that this sample code I wrote to test begin ... end; behavior:

Connection con = connectionProvider.getConnection()
con.prepareStatement("CREATE TABLE test (id INT NOT NULL)").execute()
con.prepareStatement("INSERT INTO test SELECT 1 FROM dual").execute()
con.prepareStatement("BEGIN INSERT INTO test SELECT 2 FROM dual; END;").execute()

The error I get:

Syntax error in SQL statement "BEGIN INSERT[*] INTO TEST SELECT 2 FROM DUAL; END; "; SQL statement:
BEGIN INSERT INTO test SELECT 2 FROM dual; END; [42000-167]

So my question is: are begin ... end; blocks supported by H2? If yes, what could be wrong with the code above?

Thanks.

1 Answers
Related