After many many months of pain I've tracked down my problem. I am using Spring with iBATIS to access Oracle. If a *non-transactional* iBATIS call throws any sort of exception, it seems that the database connection it was using gets left in a strange sort of transactional limbo state. Consequently the next (and all subsequent) *transactional* code to try and use that connection fails with the following error:
org.springframework.transaction.CannotCreateTransa ctionException: Could not open JDBC Connection for transaction; nested exception is java.sql.SQLException: ORA-01453: SET TRANSACTION must be first statement of transaction
java.sql.SQLException: ORA-01453: SET TRANSACTION must be first statement of transaction
at oracle.jdbc.driver.DatabaseError.throwSqlException (DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoe r.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoe r.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java: 743)
For instance I have a piece of DAO code a bit like this:
If the DataIntegrityViolationException is thrown, the DB connection gets shafted. It seems that any exception thrown across the boundary between iBATIS and Spring causes this to happen. The only way to workaround this issue seems to be to make all my DAO transactional, but that is surely horribly inefficient - I don't want to use transactions unless they are necessary and most of my DAO/manager code is just performing a single DB access.
catch (DataIntegrityViolationException exception)
// Ignore this exception since it is to be expected in certain normal cases.
Any thoughts? This seems like a bug somewhere in iBATIS or Spring to me.