java ORA-08103：对象不再存在：Oracle 过程从 MyBatis 返回 Refcursor 时发生此错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5961610/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-08103: object no longer exists: This error is occuring for Oracle Procedure returning Refcursor from MyBatis
提问by chotachetan
When calling a Stored Procedure in Oracle returning refcursor I am getting an error
在 Oracle 中调用存储过程返回 refcursor 时出现错误
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
我正在使用 Spring MyBatis 集成项目。MyBatis 的版本是 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:
我的 Mapper XML 如下：
<?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
我使用的是 MyBatis 3.0.4 版，我的 Oracle 驱动程序 jar 是 ojdbc14-10.2.0.3.0.jar
The IN and OUT parameters and their data types for Procedure are as below:
Procedure 的 IN 和 OUT 参数及其数据类型如下：
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)
我在 Java 中的 DTO 如下（省略了 setter/getter 方法）
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.
任何帮助都受到高度赞赏，因为我不知道发生了什么并且真的坚持这一点。
回答by Andrew
I've just had a similar issue with .net rather than Java.
我刚刚在 .net 而不是 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.
我的问题与游标基于全局临时表打开的事实有关。当我们将 GTT 从“删除行”更改为“提交时保留行”时，它运行良好。
see if that works for you?
看看这对你有用吗？
回答by george
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
这可能是表或数据文件中的物理或逻辑损坏。请咨询您的 DBA 以恢复数据。您可以在http://24x7dba.blogspot.com/2011/08/salvage-data-from-corrupted-oracle.html找到有关如何从 Oracle 表中恢复损坏数据的步骤
回答by ParnassusData
ORA-8103 "object no longer exists"
ORA-8103 "对象不再存在"
Error: ORA 8103
错误：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.
ORA-8103 报告 SQL 语句发现了一个不再属于语句中引用的对象的块。原因 ORA-8103 是由无效的块类型引起的。区块头具有无效的区块类型或区块内的区块类型不是预期的；例如，一个数据块（类型= 6）是预期的，但实际的块信息不是数据块（类型！= 6）。ORA-8103 也是由意外的 data_object_id 引起的，在执行受影响的 SQL 语句时，它正在更改相关对象。
回答by user1765387
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]
解决方案：很可能是基于全局临时表 (GTT) 打开了游标，该表已使用 ON COMMIT DELETE ROWS 选项创建。ORA-08103：对象不再存在错误的原因是紧跟在删除语句之后的提交语句。DBA 团队不同意在提交保留行时更改 GTT，所以最后我在 Java 服务层中添加了代码库[实现 Spring - 程序化事务]
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;
}
}
回答by Genivan Silveira de Souza
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.
就我而言，此错误与全局临时表有关。在表 DDL 中，我从“提交时删除行”更改为“提交时保留行”，问题已解决。
回答by CRV
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
CREATE GLOBAL TEMPORARY TABLE table_name(col-1 type, col-2 type, ...) ON COMMIT PRESERVE ROWS
