IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
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.
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 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.
Do you want to learn Oracle Database for Beginners, then read the following articles.
Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )
