I have a curious issue that I want to make sure I'm making the correct assumptions.
I'm running Oracle XE on windows version 10.2, and the latest jdbc (thin client) drivers - 11.x.
I have a java application that exclusively accesses the database via stored procedure calls whose stored procedures return a SYS_REFCURSOR object. It appears that in the case where the cursor has no result sets, I'm getting an error thrown at the java layer:
ORA-08103: object no longer exists java.sql.SQLException: ORA-08103: object no longer exists. This error is occurring even when the ref_cursor is a simple select (no modification at all in the stored procedure), and the result set is empty. I know that I'm the only person currently accessing the database and that no other modification is occurring at the same time.
It was my assumption that even if the REFCURSOR had no results, the REFCURSOR object would still exist and be returned to the caller (whereby when processing the resultSet, it could then know that there was nothing in the result set). Is that a correct assumption? The stored procedure itself does little to no error handling, simply opens the REFCURSOR for return. Any ideas why I'm getting the
ORA-08103: object no longer exists error? Do I need to add additional error handling in the stored procedure? (this is migrated code from the Oracle Developer Workbench, from TSQL, whic, as we all know, is very loosey-goosey with error handling).
CREATE OR REPLACE PROCEDURE TESTREFCURSOR (
in_string IN VARCHAR2,
cv_1 IN OUT SYS_REFCURSOR
)
AS
BEGIN
OPEN cv_1 FOR
SELECT col1, col2
FROM TESTCUR
WHERE col2 = in_string;
END;
typical Java call I do:
oraStmt = conn.prepareCall("{ call TESTREFCURSOR (?,?)}");
if( oraStmt != null ) {
oraStmt.setInt(1, 0); // ,
oraStmt.registerOutParameter(2,OracleTypes.CURSOR);
oraStmt.execute();
ResultSet resultSet = (ResultSet) oraStmt.getObject(2);
if (resultSet != null) {
while ( resultSet.next() ) {
count = resultSet.getInt("l_count");
}
resultSet.close();
}