ORA-01001: invalid cursor

I got ” ORA-01001: invalid cursor ”  error in Oracle database.

 

ORA-01001: invalid cursor

 

Details of error are as follows.

ORA-01001 invalid cursor
Cause: Either a host language program call specified an invalid cursor or the value of the
 MAXOPENCURSORS option in the precompiler command were too small. All cursors must be opened 
using the OOPEN call before being referenced in any of the following calls: SQL, DESCRIBE,
 NAME, DEFINE, BIND, EXEC, FETCH, and CLOSE. The Logon Data Area (LDA) must be defined by
 using OLON or OLOGON. If the LDA is not defined, this message is issued for the following 
calls: OPEN, COM, CON, ROL, and LOGOFF.

Action: Check the erroneous call statement. Specify a correct LDA area or open the cursor as 
required. If there is no problem with the cursor, it may be necessary to increase the 
MAXOPENCURSORS option value before precompiling.

 

 

 

invalid cursor

This ORA-01001 error is related with the Either a host language program call specified an invalid cursor or the value of the MAXOPENCURSORS option in the precompiler command were too small. All cursors must be opened using the OOPEN call before being referenced in any of the following calls: SQL, DESCRIBE, NAME, DEFINE, BIND, EXEC, FETCH, and CLOSE. The Logon Data Area (LDA) must be defined by using OLON or OLOGON. If the LDA is not defined, this message is issued for the following calls: OPEN, COM, CON, ROL, and LOGOFF.

 

Check the erroneous call statement. Specify a correct LDA area or open the cursor as required. If there is no problem with the cursor, it may be necessary to increase the MAXOPENCURSORS option value before precompiling.

 

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
Errors started after upgrading the Oracle Database to version 11.1.0.7.0 or 11.2.0.1.0.
They are experiencing Bug 9110335 “ORA-1001 WITH TYPED REF CURSOR AND EXECUTE IMMEDIATE AFTER 11107 UPGRADE”.
There are several coding workarounds to this issue:
1. Use a local ref cursor in the execute immediate statement and assign the value to the ref cursor that is being passed back.

Change the procedure get_det to the following, the code changes are in bold:

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

2. Use SYS_REFCURSOR instead of ref cursor.
Note: This workaround did not resolve the error for the code used in this note. It did resolve the error for the code in Bug 9110335.

To solve this error, You should increase the MAXOPENCURSORS option value before precompiling.

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

 

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *