Can a JBoss JDBC connection be invalidated so that it doesn't get resupplied from the connection pool?

Question

I have a circumstance where a JDBC connection places the Oracle session to which it is attached into a particular state (i.e. DBMS_FLASHBACK enabled mode). It's possible for the exit of this mode to fail (at least theoretically) which means that the session is left in the state erroneously. In this case, the connection can be returned to the pool, and obtained by another thread with the Oracle session still in DBMS_FLASHBACK enabled mode.

I have proved that would actually happen. (JBoss 4.2.1)

What would be ideal would be is to catch the SQLException when the mode exit fails, and mark the connection as "bad" so that once it was returned to the pool, JBoss would destroy the connection and create a new one.

But I can't find any way to mark the connection for immediate destruction. Does anyone know of a way?

Answer 1

Your Oracle database connection configuration should contain an exception sorter:

<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>

This attempts to determine when an exception occurs if the connection can be reused, or must be disconnected. This is on a best efforts basis, and doesn't work in every case. My preference for production installs is to mark all exceptions as fatal. To do this, simply set your exception sorter to org.jboss.resource.adapter.jdbc.GenericExceptionSorter.