3 Replies Latest reply on Mar 30, 2004 5:09 PM by adrian.brock

Unable to call two Oracle stored procs from one connection

cammil Mar 26, 2004 12:23 PM

I have an application that is implemented as a set of MBeans. In several places I need to call two Oracle stored procs within the same transaction. Currently we do this by getting a connection, calling proc1, calling proc2 and then calling commit(). This works on other app servers where we run this application.

We're trying to use jboss 3.2.3 with an oracle ds. WHat is happing is during the prepareStatement() call for the second proc we are getting the following :-

26 Mar 2004 08:04:43,269 DEBUG [RNStateEng] [RNState Thread 0] - java.sql.SQLException: ORA-01453: SET TRANSACTION must be first statement of transaction

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1093)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2047)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:854)
at oracle.jdbc.driver.OracleConnection.setTransactionIsolation(OracleConnection.java:1634)
at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.checkState(BaseWrapperManagedConnection.java:410)
at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.checkTransaction(BaseWrapperManagedConnection.java:399)
at org.jboss.resource.adapter.jdbc.WrappedConnection.checkTransaction(WrappedConnection.java:760)
at org.jboss.resource.adapter.jdbc.WrappedConnection.prepareCall(WrappedConnection.java:356)
at com.viacore.co.db.CallableStatementWrapper_ORACLE.init(CallableStatementWrapper_ORACLE.java:58)
at com.viacore.co.db.DBManager.getStatement(DBManager.java:93)
at com.viacore.server.RNMessages.getRNMessages(RNMessages.java:36)
at com.viacore.server.RNWork.run(RNWork.java:116)
at java.lang.Thread.run(Thread.java:534)

It appears that there is logic in the WrappedConnection.prepareCall() that calls setTransactionIsolation() every time a prepareStatement() call is made.

THis looks to be a bug to me. Does anyone have a work around suggestion?

Thanks,

Cameron.

1. Re: Unable to call two Oracle stored procs from one connecti

adrian.brock Mar 29, 2004 7:55 AM (in response to cammil)

The setTransactionIsolation() is invoked lazily on first use of a connection.

// Check the isolation level
if (jdbcTransactionIsolation != underlyingTransactionIsolation)
{
con.setTransactionIsolation(jdbcTransactionIsolation);
underlyingTransactionIsolation = jdbcTransactionIsolation;
}

You'll have to explain more about what you are doing.
Why does the current isolation "jdbcTransactionIsolation" differ from the
real isolation "underlyingTransactionIsolation" on the second invocation?
I suspect you'll find it has something to do with you using threads.

Regards,
Adrian
Actions
2. Re: Unable to call two Oracle stored procs from one connecti

cammil Mar 29, 2004 11:32 AM (in response to cammil)

Ok. So the problem occurs when the transaction isolation has been changed by the app or the stored procedure. I know the app changes it in a couple of places, although it's supposed to change it back before hand off. Is there some way to change what the default transaction isolation is?

Cameron.
Actions
3. Re: Unable to call two Oracle stored procs from one connecti

adrian.brock Mar 30, 2004 5:09 PM (in response to cammil)

Add <transaction-isolation>...</transaction-isolation>
to your -ds.xml datasource deployment.
See the dtd in docs/dtd for the possible values.

Regards,
Adrian
Actions

Go to original post