If a failed lock query is executed, and the exception is caught, the log still shows:

o.h.engine.jdbc.spi.SqlExceptionHelper : ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

This spams the logs, but the SqlExceptionHelper also shows helpful messages, like constraint violations, which would be omitted if the log level were set to FATAL in application.properties like so

logging.level.hibernate.engine.jdbc.spi.SqlExceptionHelper=FATAL

Is there a way to show some, but not all messages, from the SqlExceptionHelper?

The query is defined as

@Query(nativeQuery = true, value = "select * from locks for update nowait") List<Object> lockTable();

As written, the exception is handled, but the SqlExceptionHelper still writes to the log.

If a failed lock query is executed, and the exception is caught, the log still shows:

o.h.engine.jdbc.spi.SqlExceptionHelper : ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

This spams the logs, but the SqlExceptionHelper also shows helpful messages, like constraint violations, which would be omitted if the log level were set to FATAL in application.properties like so

logging.level..hibernate.engine.jdbc.spi.SqlExceptionHelper=FATAL

Is there a way to show some, but not all messages, from the SqlExceptionHelper?

The query is defined as

@Query(nativeQuery = true, value = "select * from locks for update nowait") List<Object> lockTable();

As written, the exception is handled, but the SqlExceptionHelper still writes to the log.

• spring
• oracle-database
• hibernate
• sqlexception

• 1 Are you submitting PL/SQL blocks, or can you? – Paul W Commented Mar 6 at 16:22
• @PaulW: it's currently a native query. Rewriting to PL/SQL as in medium/@hisyam126/… should be possible. – serv-inc Commented Mar 7 at 8:14

2 Answers 2

If you can submit your database work via an anonymous PL/SQL block (BEGIN... END;) then you can control exception handling natively yourself. Simply bind an exception name to the error you want to handle and wrap the failing call in a handler:

DECLARE
  resource_busy exception;
  PRAGMA EXCEPTION_INIT(resource_busy,-00054);
BEGIN
  -- do my thing that might fail;

EXCEPTION
  WHEN resource_busy THEN
    NULL; -- do nothing, or replace with some sort of status/messaging so you know it failed
END;

If you are issuing a query to get back data, how you get data back from a PL/SQL block varies based on whether you need 1 row or multiple rows, and what the capabilities of your client are (ref cursor, collection, simple out vars, etc..). But the above demonstrates the concept of eliminating noise from an exception you don't care to see.

As a more lightweight alternative, you can consider setting the log level temporarily. It seems that a logback logger is used for hibernate, so to deactivate, you use

LoggerContext ctx = (LoggerContext) LoggerFactory.getILoggerFactory();
ch.qos.logback.classic.Logger logger = ctx.getLogger(SqlExceptionHelper.class.getName());
Level previous = logger.getEffectiveLevel();
logger.setLevel(Level.OFF);

and restore using

logger.setLevel(previous);

afterwards.