ORA-08103: object no longer exists: This error is occuring for Oracle Procedure returning Refcursor from MyBatis

When calling a Stored Procedure in Oracle returning refcursor I am getting an error

2011-05-10 03:36:23 DirtiesContextTestExecutionListener [DEBUG] After test method: context [[TestContext@3a363a36 testClass = AccountActivityServiceTest, locations = array<String>['classpath:/com/bnymellon/pwb/pfdetails/service/test/test-application-context.xml'], testInstance = com.bnymellon.pwb.pfdetails.service.test.AccountActivityServiceTest@6d2c6d2c, testMethod = getData@AccountActivityServiceTest, testException = org.springframework.jdbc.UncategorizedSQLException: 
### Error updating database.  Cause: java.sql.SQLException: ORA-08103: object no longer exists

### The error may involve com.bnymellon.pwb.pfdetails.persistence.AccountActivityMapper.getAccountActivityData-Inline
### The error occurred while setting parameters
### Cause: java.sql.SQLException: ORA-08103: object no longer exists

; uncategorized SQLException for SQL []; SQL state [72000]; error code [8103]; ORA-08103: object no longer exists
; nested exception is java.sql.SQLException: ORA-08103: object no longer exists 

I am using Spring MyBatis integraton project. the version of MyBatis is 3.0.4

I can see the Procedure is being executed. The logs are below.

2011-05-10 03:36:16 PreparedStatement [DEBUG] ==>  Executing: {call PWMWI.PAM_TRANSACTION_PKG.ACCOUNT_ACTIVITY( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)} 
2011-05-10 03:36:16 PreparedStatement [DEBUG] ==> Parameters: 1987(Integer), 5627(Integer), null, null, 2010-01-01(Date), 2010-12-31(Date), All Asset Classes(String), [All, PYR](String), (String), null

My Mapper XMl is as follows:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.bnymellon.pwb.pfdetails.persistence.AccountActivityMapper">
    <select id="getAccountActivityData"
        parameterType="com.bnymellon.pwb.pfdetails.common.AccountActivityDTO"
        statementType="CALLABLE">
        {call PWMWI.PAM_TRANSACTION_PKG.ACCOUNT_ACTIVITY(
        #{userInstance,mode=IN, jdbcType=INTEGER},
        #{accountGroupId,mode=IN,
        jdbcType=INTEGER},
        #{accountId,mode=IN, jdbcType=VARCHAR},
        #{accountId,mode=IN, jdbcType=VARCHAR},
        #{startDate,mode=IN,
        jdbcType=DATE},
        #{endDate,mode=IN, jdbcType=DATE},
        #{assetClass,mode=IN, jdbcType=VARCHAR},
        #{transactionType,mode=IN,
        jdbcType=VARCHAR},
        #{cusipId,mode=IN, jdbcType=VARCHAR},
        #{ticker,mode=IN, jdbcType=VARCHAR},
        #{domainList,mode=OUT,jdbcType=CURSOR,javaType=java.sql.ResultSet,resultMap=aaDataMap})}
    </select>

    <resultMap type="com.bnymellon.pwb.pfdetails.domain.AccountActivity"
        id="aaDataMap">
        <result column="TICKER" property="ticker" jdbcType="VARCHAR" />
        <result column="CUSIP" property="cusIp" jdbcType="VARCHAR" />
        <result column="SECURITY_NAME" property="securityName"
            jdbcType="VARCHAR"></result>
        <result column="ASSET_CLASS" property="assetClass" jdbcType="VARCHAR" />
        <result column="TRADE_DATE" property="tradeDate" jdbcType="DATE" />
        <result column="SETTLE_DATE" property="settleDate" jdbcType="DATE" />
        <result column="DESCRIPTION" property="description" jdbcType="VARCHAR" />
        <result column="RECORD_TYPE" property="description" jdbcType="VARCHAR" />
        <result column="ACCOUNT_NUMBER" property="accountNumber"
            jdbcType="VARCHAR" />开发者_开发问答
        <result column="QUANTITY" property="shares" jdbcType="VARCHAR" />
        <result column="LONG_DESC" property="transDesc" jdbcType="VARCHAR" />
        <result column="PORT_NUM" property="indicator" jdbcType="INTEGER" />
        <result column="AMOUNT" property="amount" jdbcType="VARCHAR" />
    </resultMap>
</mapper>

I am using MyBatis version 3.0.4 and my Oracle driver jar is ojdbc14-10.2.0.3.0.jar

The IN and OUT parameters and their data types for Procedure are as below:

P_USER_INST           NUMBER        IN     
P_GROUP_ID            NUMBER        IN 
P_ENTITY_ID           CHAR          IN 
P_ENTITY_NAME         VARCHAR2 (30) IN   
P_START_DATE          DATE          IN      
P_END_DATE            DATE          IN     
P_ASSETCLASS          CHAR          IN        
P_TRAN_TYPE           CHAR          IN   
P_PRIMARY_ASSET_ID    VARCHAR2      IN        
P_TICKER              VARCHAR2      IN   
P_ACCOUNT_DETAIL_CUR  REF CURSOR    OUT    

My DTO in Java is below(omitting the setter/getter methods)

private Integer userInstance;

private Integer accountGroupId;

private String accountId;

private Date startDate;

private Date endDate;

private String transactionType;

private String ticker;

private String cusipId;

private String assetClass;

private List<AccountActivity> domainList;

Any help is highly appreciated as I am clue less what is going on and really stuck on this.

I've just had a similar issue with .net rather than Java.

my problem related to the fact that the cursor opened based on a global temporary table. when we changed the GTT to "on commit preserve rows" from "delete rows" it worked fine.

see if that works for you?

This can be a corruption, physical or logical, in a table or data file. Check with your DBA for recovering the data. You can find steps on how to recover corrupted data from an Oracle table at http://24x7dba.blogspot.com/2011/08/salvage-data-from-corrupted-oracle.html

ORA-8103 "object no longer exists"

Error: ORA 8103

Text: object no longer exists

Cause: The object has been deleted by another user since the operation began. Action: Remove references to the object.

ORA-8103 is reporting that a SQL statement found a block that no longer belongs to the object referenced in the statement. Cause ORA-8103 is caused by an invalid block type. The block header has an invalid block type or the block type inside the block is not expected; e.g. a data block (Type=6) was expected but the actual block information is not a data block (Type!=6). ORA-8103 is also caused by an unexpected data_object_id where it is changing for the involved objects while the affected SQL statement is executed.

After Struggling one week finally I fixed the Issue:

Solution: Most likely that a cursor is opened based on a global temporary table(GTT), which had been created with ON COMMIT DELETE ROWS option. And the cause of the ORA-08103: object no longer exists error is commit statement that followed right after the delete statement. DBA team didn't agree to change the GTT as on commit preserve rows so finally I added code base in Java Service Layer[Implementing the Spring - Programmatic Transaction]

package com.test;

import java.util.List;
import javax.sql.DataSource;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

public class StudentJDBCTemplate implements StudentDAO {
   private DataSource dataSource;
   private JdbcTemplate jdbcTemplateObject;
   private PlatformTransactionManager transactionManager;

   public void setDataSource(DataSource dataSource) {
      this.dataSource = dataSource;
      this.jdbcTemplateObject = new JdbcTemplate(dataSource);
   }
   public void setTransactionManager(PlatformTransactionManager transactionManager) {
      this.transactionManager = transactionManager;
   }
   public void create(String name, Integer age, Integer marks, Integer year){
      TransactionDefinition def = new DefaultTransactionDefinition();
      TransactionStatus status = transactionManager.getTransaction(def);

      try {
         String SQL1 = "insert into Student (name, age) values (?, ?)";
         jdbcTemplateObject.update( SQL1, name, age);

         // Get the latest student id to be used in Marks table
         String SQL2 = "select max(id) from Student";
         int sid = jdbcTemplateObject.queryForInt( SQL2 );

         String SQL3 = "insert into Marks(sid, marks, year) " + "values (?, ?, ?)";
         jdbcTemplateObject.update( SQL3, sid, marks, year);

         System.out.println("Created Name = " + name + ", Age = " + age);
         transactionManager.commit(status);
      } 
      catch (DataAccessException e) {
         System.out.println("Error in creating record, rolling back");
         transactionManager.rollback(status);
         throw e;
      }
      return;
   }
   public List<StudentMarks> listStudents() {
      String SQL = "select * from Student, Marks where Student.id=Marks.sid";
      List <StudentMarks> studentMarks = jdbcTemplateObject.query(SQL, 
         new StudentMarksMapper());

      return studentMarks;
   }
}

In my case, this error was related to a global temporary table. In the table DDL, I changed from "on commit delete rows" to "on commit preserve rows" and the problem has been fixed.

Create your global temporary table as below when you are creating it.

CREATE GLOBAL TEMPORARY TABLE table_name(col-1 type, col-2 type, ...) ON COMMIT PRESERVE ROWS