getting ORA-02067: transaction or savepoint rollback required

[leksh nair]

Hi
We are using datasource for db connections.most of the transactions do not fail but sometimes gives this error-->
ORA-02067 : transaction or savepoint rollback required

with no datasource configured, system doesnt error out anytime.
Pl help if anyone out there has faced such a problem..its urgent..

[Vinod Tiwari]

Can you provide exception trace also?

[Mahesh D Kamath]

I am facing the same issue - SQLException while trying to call stored procedure
---------------------------------------------------
Product : Oracle Portal 10g

Issue Summary : A piece of code in our portal fails intermittently to fetch data by executing a stored procedure.

ERROR: SQLException
sqlE.getMessage() - ORA-02067: transaction or savepoint rollback required
sqlE.getSQLState() = 42000
sqlE.getErrorCode() - 2067
sqlE.getCause() - NULL


CODE DETAILS : This java method accepts a username as parameter to fetch user specific data (a menu) by executing the stored procedure.
JDBC connection is created against a datasource setup on portal.

.
.
private DataSource ds;
private String sourceName;
.
.
public Connection getConnection() throws Exception
  {
   Connection conn = null;
    try {
      
      if (ds == null ) {
        InitialContext ctx;
        
        ctx = new InitialContext();
        ds = (DataSource) ctx.lookup(this.sourceName);
        conn = ds.getConnection();
      }
      else {
        conn = ds.getConnection();
      }                          
      
    }
    catch (NamingException ne) {
.
.
.
  
        OracleCallableStatement cs = null;
                cs = (OracleCallableStatement)conn.prepareCall
                      ("BEGIN  :1:=" + portletName + "(:2); END;");

                //Returned value from stored procedure
                cs.registerOutParameter(1, java.sql.Types.CLOB);

                //Input parameter for Stored procedure-partyNumber
                cs.setString(2, ssoUserName);

                // Execute the CallableStatement
                cs.execute();
                // Get the clob value
                rawContent = cs.getClob(1);

            }
        } catch (SQLException sqlE) {
            LogManager.log("SQL Error Generating Content for: " +
                             portletName + " with username: " + ssoUserName +
                             " details: " +  " \n MESSAGE : "+ sqlE.getMessage()
                             +  " \n SQLState : "+ sqlE.getSQLState()
                             +  " \n Error Code : "+ sqlE.getErrorCode()
                             +  " \n Error Cause : "+ sqlE.getCause() );
                              sqlE.printStackTrace(System.err);
        } 

[Martin Vashko]

Welcome to the ranch, Manesh!

I've never seen this, but the description seems to indicate you might have a problem regarding a trigger or remote database. Do you use any triggers or database links in your application?

[Mahesh D Kamath]

Thanks Martin. I've tried Googling around this ORA error and all references suggests the issue to be related to remote database trigger and stuff.
However, in this case, the stored procedure is never reached. I'm able to establish the fact because, I have an exception block added to the procedure which logs all errors that happens in the block.
So, had it been an issue with the remote database or trigger, it should have been well caught in my stored procedure exception block.
Not sure if my statement makes sense.

[Martin Vashko]

In any case, the description implies this need not be a bug in your code. This seems to be a kind of error that sometimes 'just happens' and you'll probably need to handle it gracefully, similarly to having to handle ORA-08177 when using Serializable isolation level. You could, for example, automatically restart the transaction when this error occurs - ie. in the catch block, inspect the exception, and if it was the ORA-02067 (sqlE.getErrorCode() == 2067), retry (probably limiting the retry count to a sensible number like 3 or 5).