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 Newbie

      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.