0 Replies Latest reply on Feb 17, 2004 5:58 PM by 415066

    Executing Stored Procedure

    415066
      I have a Stored Procedure which does insert/update. I am executing this using SessionBroker.getActiveUnitOfWork().executeQuery() with in a Weblogic Server under JTS.

      The executeQuery is throwing an exception with SQL error code of ORA-00900 Invalid SQL Statement as follows:


      Internal Exception: java.sql.SQLException: ORA-00900: invalid SQL statement

      Error Code: 900
           at oracle.toplink.exceptions.DatabaseException.sqlException(DatabaseException.java:226)
           at oracle.toplink.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:730)
           at oracle.toplink.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:778)
           at oracle.toplink.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:639)
           at oracle.toplink.publicinterface.UnitOfWork.executeCall(UnitOfWork.java:1393)
           at oracle.toplink.internal.queryframework.CallQueryMechanism.executeCall(CallQueryMechanism.java:134)
           at oracle.toplink.internal.queryframework.CallQueryMechanism.executeCall(CallQueryMechanism.java:115)
           at oracle.toplink.internal.queryframework.CallQueryMechanism.executeNoSelectCall(CallQueryMechanism.java:167)
           at oracle.toplink.internal.queryframework.CallQueryMechanism.executeNoSelect(CallQueryMechanism.java:146)
           at oracle.toplink.queryframework.DataModifyQuery.execute(DataModifyQuery.java:41)
           at oracle.toplink.queryframework.DatabaseQuery.execute(DatabaseQuery.java:493)
           at oracle.toplink.publicinterface.Session.internalExecuteQuery(Session.java:1958)
           at oracle.toplink.publicinterface.UnitOfWork.internalExecuteQuery(UnitOfWork.java:2229)
           at oracle.toplink.publicinterface.Session.executeQuery(Session.java:1086)
           at oracle.toplink.publicinterface.Session.executeQuery(Session.java:1038)
           at com.trcinc.webpharm.persistence.PersistenceFactory.executeStoredProcedure(PersistenceFactory.java:665)
           at com.trcinc.webpharm.persistence.PersistenceFactory.setAuditingContext(PersistenceFactory.java:783)
           at com.trcinc.webpharm.businesscomponent.customer.CustomerManagementImpl.createPatient(CustomerManagementImpl.java:146)
           at com.trcinc.webpharm.businesscomponent.customer.CustomerManagement_8bfdks_EOImpl.createPatient(CustomerManagement_8bfdks_EOImpl.java:1018)
           ... 10 more
      Caused by: java.sql.SQLException: ORA-00900: invalid SQL statement

           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:582)
           at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1983)
           at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1141)
           at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2149)
           at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2032)
           at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2894)
           at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608)
           at weblogic.jdbc.wrapper.PreparedStatement.executeUpdate(PreparedStatement.java:94)
           at oracle.toplink.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:724)
           ... 27 more


      Although, I am able to execute the stored procedure with ServerSession.acquireUnitOfWork().executeQuery().

      The way I am creating the call and query for both the scenarios above is as follows:

                StoredProcedureCall storedProcedureCall = new StoredProcedureCall();
                storedProcedureCall.setProcedureName("AUDITING.SETUSERINFO");
                               storedProcedureCall.addUnamedArgumentValue("userSessionId");

           storedProcedureCall.addUnamedArgumentValue("userId");
           
                DatabaseQuery query = new DataModifyQuery();
                query.setCall(storedProcedureCall);
                query.setSessionName(sessionName);
      UnitOfWork uow = null; //set with different onces for the two scenario above
      uow.executeQuery(query);


      Any ideas??