JAVA调用oracle存储过程返回游标出现“对象不再存在”问题

运行在was5.1上的应用某个功能在DAO中调用存储过程返回游标时出现“java.sql.SQLException:ORA-08103:对象不再存在”的错误,

系统开发框架:spring+hibernate+struts

系统运行环境:jdk1.5+wabsphere5.1+oracle9i

以下是调用存储过程的JAVA代码和过程内容

callableStatement = connection.prepareCall("CALL GET_GRADE(?,?,?,?,?)");

callableStatement.setObject(1, parameterArray.getOracleARRAY(connection));
callableStatement.registerOutParameter(2, OracleTypes.CURSOR);
callableStatement.registerOutParameter(3, OracleTypes.NUMBER);
callableStatement.registerOutParameter(4, OracleTypes.VARCHAR);
callableStatement.execute();

if (callableStatement.getInt(3) != 0) {
    String error = callableStatement.getString(5);
    throw BaseException.systemException("系统错误!" + error);
}

resoultSet = (ResultSet) callableStatement.getObject(2);
if (logger.isDebugEnabled()) {
	logger.debug("tree execute before");
}

执行到callableStatement.getObject(2)就出错了。

PROCEDURE GET_STANDARDGRADE(
       P_PARAMETERLIST IN OUT TYPE_PARAMETERLIST,
        P_HEAD OUT	T_CURSOR, -- 返回游标
        P_B_EXCEPTION IN OUT	NUMBER, -- =0:正常 非0 错误
        P_B_EXCEPTION_DESCRIPTION IN OUT VARCHAR2) -- 错误描述
IS
	W_SQLSTR		LONG;
	W_SUBQUERYSTRING	LONG;
	W_D_COLUMNSTRING	LONG;

	W_LOOPPOINT		NUMBER := 0;
	W_NAME			VARCHAR2(30);
	W_YEAR			NUMBER;
	CURSOR W_CURSOR IS
	SELECT GID,NAME
	FROM GRADE
	WHERE YEAR = W_YEAR
	ORDER BY GID;
	W_CURSOR_ROW W_CURSOR%ROWTYPE;
BEGIN
	P_B_EXCEPTION := -1;
	W_YEAR := PKG_PARAMETER.FNC_GETINTVALUEBYNAME('YEAR',P_PARAMETERLIST,1);

	IF P_HEAD%ISOPEN THEN
		CLOSE P_HEAD;
	END IF;

	delete from TMPTAB_RPT_TITLE;
	--临时表插入两行数据
         INSERT INTO TMPTAB_RPT_TITLE(ID,PID,COLUMNNAME)
	VALUES(2,NULL,'NAME');
	INSERT INTO TMPTAB_RPT_TITLE(ID,PID,COLUMNNAME)
	VALUES(1,NULL,'STDID');
         --获取临时表数据,返回游标
 	OPEN P_HEAD FOR SELECT * FROM TMPTAB_RPT_TITLE ORDER BY ID;
	P_B_EXCEPTION := 0;
	<<PROCEDURE_EXIT>>
	P_B_EXCEPTION := P_B_EXCEPTION +0;
END GET_STANDARDGRADE;
CREATE GLOBAL TEMPORARY TABLE YSBZ.TMPTAB_TREE_CONTAINER
(
    ID                             VARCHAR2(100),
    PID                            VARCHAR2(100),
    COLUMNNAME                     VARCHAR2(100)
)
ON COMMIT DELETE ROWS
NOCACHE
/

奇怪的是hibernate连接方式如果是DatasourceConnection会出现以上的错误,改成DriverManagerConnection方式运行又是正常的。

后来怀疑是临时表的问题,将ONCOMMITDELETEROWS改成ONCOMMITPRESERVEROWS

后问题解决了。

相关推荐