ORA-08103: object no longer exists: This error is occuring for Oracle Procedure returning Refcursor from MyBatis
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?
Admin
Updated on June 27, 2022Comments
-
Admin almost 2 years
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.
-
nMoncho over 10 yearsI was forgetting to set autocommit to false, when I changed that configuration it worked fine.
-
Qantas 94 Heavy over 10 yearsWhile this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes.