Error Ora-01001 Invalid Cursor
Contents |
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp Wanted! Oracle PostersOracle Books oracle error ora-01001 invalid cursor Oracle Scripts Ion Excel-DB Don Burleson Blog oracle ora-01001 invalid cursor
ORA-01001: invalid cursor tips Oracle Error Tips by ora-01001 invalid cursor for loop Burleson Consulting The Oracle docs note this on the ora-01001 error*: ORA-01001 invalid cursor Cause: Either a host language program call specified an invalid cursor invalid cursor error in pl/sql 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 LDAOra-01001 Invalid Cursor In Package
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. The ORA-01001 error occurs when: a host language program call gave an invalid cursor for use the value of the MAXOPENCURSORS option in the precompiler command was too small You can fix the ORA-01001 error by: Check your problematic call statement for any issues Specify a correct LDA area or open the cursor as required As a last resort, increase the MAXOPENCURSORS option value before precompiling As a note, the ORA-01001 error does not exist in Oracle 10g, according to the Oracle documentation. OraFaq.com has the following to say about the ORA-01001 error: This is 100% a program logic problem. You have either forgotten to code an open statement before using
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies
Invalid Cursor In Oracle
of this site About Us Learn more about Stack Overflow the company invalid cursor exception in oracle Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users ora 1001 Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a http://www.dba-oracle.com/t_ora_01001_invalid_cursor.htm minute: Sign up Strange error “Ora-01001 Invalid cursor” in procedure up vote 7 down vote favorite Yesterday I worked on a strange bug in our production procedure. Execution failed on statement if v_cursor%isopen then close v_cursor; -- here was an error end if; After some digging into I discovered that problem was in subprogram that opened this cursor. I fixed bug by http://stackoverflow.com/questions/11341166/strange-error-ora-01001-invalid-cursor-in-procedure adding output parameter sys_refcursor in subprogram. To clarify situation consider following test code: procedure nested_test(test number, p_cur out sys_refcursor) is procedure nested_procedure_fail is begin open p_cur for select 1, 2, 3, 4 from dual where 1 = 0; end; procedure nested_procedure_success(p_cur out sys_refcursor) is begin open p_cur for select 1, 2, 3, 4 from dual where 1 = 0; end; begin if test = 1 then nested_procedure_fail; else if test = 2 then nested_procedure_success(p_cur => p_cur); else open p_cur for select 6, 7, 8, 9 from dual where 1 = 1; end if; end if; end; procedure test_fail is v_cur sys_refcursor; begin nested_test(test => 1, p_cur => v_cur); if v_cur%isopen then close v_cur; end if; end; procedure test_success is v_cur sys_refcursor; begin nested_test(test => 2, p_cur => v_cur); if v_cur%isopen then close v_cur; end if; end; If I try to run test_success everything is OK, but on test_fail I receive a message ORA-01001: Invalid cursor I cannot find any information about this. Can anyone explain why this code fails? Oracle version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Releas
here for a quick overview of the site Help Center Detailed answers to any questions you might have http://stackoverflow.com/questions/14229188/ora-01001-invalid-cursor-error Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just invalid cursor like you, helping each other. Join them; it only takes a minute: Sign up ORA-01001 Invalid cursor error up vote 0 down vote favorite I have written two database procedures in Oracle that are using multiple cursors. The problem faced is that while the procedures are getting executed without any errors in 98% of the cases, for ora-01001 invalid cursor few cases, the error - ORA-01001 invalid cursor is being thrown. Since the problem occurs at runtime, it is getting difficult to identify the cause. I am unable to replicate the scenario wherein the procedure throws the error. As per my understanding, since 98% of the cases are executing both the procedures without any problem, I don't think the cause is due to invalid Open-Fetch-Close commands. Any idea what could be the possible reason for the exception being thrown? I am adding a sample code: CURSOR curWork(vCaseId VARCHAR2) IS SELECT w.X_WORKFUNCTION_ID FROM table_case c, table_queue w WHERE c.id_number = vCaseId AND c.CASE_CURRQ2QUEUE = w.objid; CURSOR curPart(vCaseId VARCHAR2) IS SELECT p.x_part_number, p.X_FAULT_CATEGORY, p.X_FORW_GRP_ID FROM table_case c, table_mod_level m, table_x_part_forw_grp p, table_part_num n WHERE c.id_number =vCaseId AND c.CASE_PRT2PART_INFO =m.objid And m.part_info2part_num=N.OBJID AND N.S_PART_NUMBER=P.X_PART_NUMBER; PROCEDURE UpdateAddressXY(vCaseId IN VARCHAR2, nStatus IN OUT NUMBER, vComment OUT VARCHAR2) IS bContinue boolean; nJobCode VARCHAR2(4); cnt number; CURSOR curAddress(vCaseId VARCHAR2) IS select a.X_COORDINATE_X , a.X_COORDINATE_Y, A.X_XY_ORIGIN from table_address a, table_case c where C.ID_NUMBER =vCaseId a