When Trying to Return a Ref Cursor Results in Ora-01001 Or Ora-00604 After Upgrade to 11.1.0.7 / 11.2 (Doc ID 1106914.1)

Last updated on JULY 05, 2017

In this Document
  Symptoms
  Changes
  Cause
  Solution
  References


Symptoms

When trying to pass back a ref cursor from a stored procedure results in the errors:
ORA-01001 Invalid cursor
ORA-00604 recursive cursor error

Sql Code that Reproduces the problem:
DROP TABLE emp;
DROP PACKAGE emp_pkg;

CREATE TABLE emp
(empno VARCHAR2(10),
empname VARCHAR2(30),
deptno VARCHAR2(5),
salary NUMBER(10,2));

INSERT INTO emp VALUES ('A001','TESTA1','A', 1000);
INSERT INTO emp VALUES ('B001','TESTB1','B', 1000);
INSERT INTO emp VALUES ('C001','TESTC1','C', 1000);
INSERT INTO emp VALUES ('A002','TESTA2','A', 2000);
INSERT INTO emp VALUES ('B002','TESTB2','B', 2000);
INSERT INTO emp VALUES ('C002','TESTC2','C', 2000);
INSERT INTO emp VALUES ('A003','TESTA3','A', 3000);
INSERT INTO emp VALUES ('B003','TESTB3','B', 3000);
INSERT INTO emp VALUES ('C003','TESTC3','C', 3000);
COMMIT;

CREATE OR REPLACE PACKAGE emp_pkg AS TYPE Ref_Cursor IS REF CURSOR;
PROCEDURE get_empdata2(i_tid VARCHAR2, o_cur OUT ref_cursor);
PROCEDURE get_det(i_tid VARCHAR2, o_cur1 OUT ref_cursor);
END emp_pkg;
/

CREATE OR REPLACE PACKAGE BODY emp_pkg AS v_sql VARCHAR2(2000);

PROCEDURE get_empdata2(i_tid VARCHAR2, o_cur OUT ref_cursor)
IS
BEGIN
OPEN o_cur FOR
SELECT '' empno, '' empname, deptno, SUM(salary) salary
FROM (SELECT deptno, empno, empname, salary FROM emp WHERE empno LIKE '%1'
UNION ALL SELECT deptno, empno, empname, salary FROM emp WHERE empno LIKE '%2'
UNION ALL SELECT deptno, empno, empname, salary FROM emp WHERE empno LIKE '%3')
GROUP BY deptno;
END get_empdata2;

PROCEDURE get_det(i_tid VARCHAR2,
o_cur1 OUT ref_cursor)
IS
BEGIN
v_sql := 'BEGIN emp_pkg.get_empdata2(:tid, :o_cur); END;';
EXECUTE IMMEDIATE v_sql USING i_tid, OUT o_cur1;
END get_det;

END emp_pkg;
/

To reproduce the error execute the following in sqlplus:

SET SERVEROUTPUT ON SIZE 10000
DECLARE
TYPE Ref_Cursor IS REF CURSOR;
o_cur2 ref_cursor;
c_cur emp%ROWTYPE;
v_tid VARCHAR2(10);
BEGIN
EMP_PKG.get_det(v_tid, o_cur2);

FETCH o_cur2 INTO c_cur;
WHILE o_cur2%FOUND
LOOP
DBMS_OUTPUT.PUT_LINE(c_cur.deptno || ' * ' || c_cur.salary);
FETCH o_cur2 INTO c_cur;
END LOOP;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error - ' || SQLERRM);
END;
/

Results seen in sqlplus:

DECLARE
*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at line 9


Changes

Errors started after upgrading the Oracle Database to version 11.1.0.7.0 or 11.2.0.1.0.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms