Invalid Cursor Error Pl Sql
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 Scripts
Ora 01001 Invalid Cursor In Oracle 11g
Ion Excel-DB Don Burleson Blog
sql error invalid cursor halt application ORA-01001: invalid cursor tips Oracle Error Tips by Burleson Consulting ora-01001 invalid cursor ref cursor 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 or the valueInvalid Cursor Exception Example In Oracle
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
Java.sql.sqlexception: Ora-01001: Invalid Cursor
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 a cursor, or have not noticed that the cursor
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of ora-01001 invalid cursor for loop this site About Us Learn more about Stack Overflow the company Business Learn
Maxopencursors
more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question invalid cursor sql 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 minute: Sign up Strange http://www.dba-oracle.com/t_ora_01001_invalid_cursor.htm 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 adding output parameter sys_refcursor in http://stackoverflow.com/questions/11341166/strange-error-ora-01001-invalid-cursor-in-procedure 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 Release 11.2.0.3.0 - Production CORE 11.2.0.3.0 Production TNS for Solaris: Version 1
already been closed. How to fix it[edit] This is 100% a program logic problem http://www.orafaq.com/wiki/ORA-01001 (unless an Oracle bug, such as bug 6823287). Mostly, you http://www.acehints.com/2012/05/reasons-for-plsql-code-block-failure.html will have either forgotten to code an open statement before using a cursor, or have not noticed that the cursor has been closed and have tried to continue using it. The following checklist may help identify the fault: Make sure you have invalid cursor an OPEN statement prior to using any explicit cursors. Make sure that you do not have a misplaced CLOSE statement. If you need to do a sequence of OPEN...CLOSE...OPEN...CLOSE (perhaps because you need to reset bind variables or to commit updates inside a loop) check your logic flow and make sure there are 01001 invalid cursor no fetches between the 1st CLOSE and the 2nd OPEN. If you have nested loops, check that a condition in an inner loop is not being missed which allows control to pass unexpectedly to a CLOSE in an outer loop Remember that a PL/SQL FOR loop does an implicit OPEN and CLOSE. If you take a routine with a FOR loop and change it to a WHILE loop you must remember to code the OPEN and CLOSE. Retrieved from "http://www.orafaq.com/wiki/index.php?title=ORA-01001&oldid=14974" Category: Errors Navigation menu Views Page Discussion Edit History Personal tools Log in / create account Site Navigation Wiki Home Forum Home Blogger Home Site highlights Blog Aggregator FAQ's Mailing Lists Usenet News RSS Feeds Wiki Navigation Categories Recent changes Random page Help Search Tools What links here Related changes Special pages Printable version Permanent link Page information This page was last modified on 23 September 2013, at 16:18. Privacy policy About Oracle Wiki Disclaimers
for this error. You have declared one cursor and opened with FOR LOOP. In this case you don't need to specify explicit close for the cursor. By mistake if you mention the close cursor command it will through this error. If you try to close a cursor which is already closed, you get the error message. Examples SQL> create or replace procedure pr_sal iscursor c1 is select emp_name from emp;begin for rec1 in c1 loop dbms_output.put_line(rec1.emp_name); end loop; close c1; --Close statement after for loopend; Procedure created. SQL> exec pr_sal;RupalHeroJainJohnRijuSamKiranPeterKingRoshanRoshanDavidBEGIN pr_sal; END; *ERROR at line 1:ORA-01001: invalid cursorORA-06512: at "SCOTT.PR_SAL", line 7ORA-06512: at line 1 SQL> create or replace procedure pr_sal iscursor c1 is select emp_name from emp;begin for rec1 in c1 loop dbms_output.put_line(rec1.emp_name); end loop;end;/ Procedure created. SQL> set serveroutput onSQL> exec pr_sal;RupalHeroJainJohnRijuSamKiranPeterKingRoshanRoshanDavid PL/SQL procedure successfully completed. SQL> create or replace procedure pr_sal iscursor c1 is select emp_name from emp;begin open c1; close c1; close c1; --Close statement for already closed cursor.end; / Procedure created. SQL> exec pr_sal;BEGIN pr_sal; END; *ERROR at line 1:ORA-01001: invalid cursorORA-06512: at "SCOTT.PR_SAL", line 6ORA-06512: at line 1 Datapump Tuning FeaturesDatapump vs EXP/IMPDatapump AdvantagesDatapump Exclude Table PartitionDatapump Exclude and Include ParameterDatapump NetworkLink ParameterDatapump SQLFILE ParameterDatapump Reuse_dumpfiles ParameterDatapump Compression ParameterDatapump Jobname ParameterDatapump Remapdata ParameterDatapump Sample ParameterDatapump Query ParameterDatapump Transportable Tablespace Option Previous Article Next Article 0 comments: Post a Comment Older Post Newer Post Home Translator Get This Translator DbaTopics Archive ► 2016 ► October ► September ► August ► July ► June ► May ► April ► March ► January ► 2015 ► December ► November ► September ► August ► July ► June ► May ► April ► February ► January ► 2014 ► December ► November ► October ► September ► August ► July ► June ► May ► April ► March ► February ► January ► 2013 ► December ► November ► October ► September ► August ► July ► June ► May ► April ► March ► February ► January ▼ 2012 ► December ► November ► Octobe