Alter Oracle session variables outside of a Hibernate transaction

Question

We've been using a pattern like this for a while to ensure a specific operation is executed with BATCH NOWAIT, for performance reasons.

try {
    session.createSQLQuery("ALTER SESSION SET COMMIT_LOGGING='BATCH' COMMIT_WAIT='NOWAIT'").executeUpdate();
    // Do the operation (which also calls transaction.commit())
    return callback.apply(session);
} finally {
    session.createSQLQuery("ALTER SESSION SET COMMIT_LOGGING='IMMEDIATE' COMMIT_WAIT='WAIT'").executeUpdate();
}

This has worked fine in Hibernate 4. As of Hibernate 5, the last statement fails because it's not inside a transaction (as it's just been committed).

javax.persistence.TransactionRequiredException: Executing an update/delete query

It isn't an update or a delete, but executeUpdate() is the only method you can call to execute this statement without returning any rows. It shouldn't need to be in a transaction since session variables apply to the entirety of the connection, and it does need to be executed to restore the session variables because a connection pool is in use.

I've tried using one of the query methods instead, but this statement has -1 rows, and it won't let me stack SELECT 1 FROM DUAL on the end.

Is there any way to execute a native query from Hibernate that's neither update/delete or results-returning, outside of a transaction?

Thanks Nick. You mentioned trying the query methods instead. I hesitate to ask, but would it meet your requirement to execute an impure query, that altered the session as a side effect? — alexgibbs, Feb 12 '19 at 19:06

score 0 · Accepted Answer · answered Feb 12 '19 at 21:53

Using the underlying Connection directly bypasses Hibernate's checks and allows me to execute such a statement in peace.

try {
    session.doWork(conn ->
            conn.createStatement().execute("ALTER SESSION SET COMMIT_LOGGING='BATCH' COMMIT_WAIT='NOWAIT'")
    );
    return callback.apply(session);
} finally {
    session.doWork(conn ->
            conn.createStatement().execute("ALTER SESSION SET COMMIT_LOGGING='IMMEDIATE' COMMIT_WAIT='WAIT'")
    );
}

Alter Oracle session variables outside of a Hibernate transaction

1 Answers1