Pl Sql Invalid Cursor Error
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 Ion Excel-DB Don Burleson Blog
ORA-01001: invalid cursor tips Oracle Error Tips by 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 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. 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 has been closed and have tried to continue using it. The following checklist may help identify the fault: Make sure you have an OPEN shere for a quick overview of the site Help Center Detailed answers to any questions you might have 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 6.2 million programmers, just like you, helping each other. Join them; it only takes a 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 http://www.dba-oracle.com/t_ora_01001_invalid_cursor.htm 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 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; http://stackoverflow.com/questions/11341166/strange-error-ora-01001-invalid-cursor-in-procedure 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 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production oracle oracle11g share|improve this question asked Jul 5 '12 at 9:08 Akhmed Kharaev 182127 add a comment| 2 Answers 2 active oldest votes up vote 10 down vote accepted This appears to be bug 7174888, or at least something closely related to it. The description for that is 'ORA-6504 raised when sys_refcursor passed to another procedure', but I
and Data Mining options) Show: Today's Messages :: Show Polls :: Message Navigator E-mail to friend Strange Invalid Cursor error with sys_refcursor [message #358346] Mon, 10 http://www.orafaq.com/forum/t/127491/ November 2008 16:21 harishmk Messages: 7Registered: November 2008 Junior Member I've http://www.acehints.com/2012/05/reasons-for-plsql-code-block-failure.html 3 procedures. proc3 calls proc2 calls proc1 proc3 passes a sys_refcursor as an out parameter to proc2 proc2 passes that to proc1 proc1 opens the cursor with a select proc3 simply closes the out cursor i am getting "ORA-01001: invalid cursor" while closing the cursor. any idea? pl sql -- PROC1 CREATE OR REPLACE PROCEDURE TEST_PROC1 ( oCur OUT SYS_REFCURSOR ) AS BEGIN OPEN oCur FOR SELECT entity_id FROM entity; END TEST_PROC1; -- PROC2 CREATE OR REPLACE PROCEDURE TEST_PROC2 ( oCur OUT SYS_REFCURSOR ) AS BEGIN test_proc1(oCur); END TEST_PROC2; -- PROC3 CREATE OR REPLACE PROCEDURE TEST_PROC3 AS oCur SYS_REFCURSOR; BEGIN test_proc2(oCur); CLOSE oCur; -- Invalid cursor error here pl sql invalid END TEST_PROC3; Report message to a moderator Re: Strange Invalid Cursor error with sys_refcursor [message #358356 is a reply to message #358346] Mon, 10 November 2008 19:41 rleishman Messages: 3724Registered: October 2005 Location: Melbourne, Australia Senior Member Make the arguments IN OUT instead of just OUT. See if that helps. Ross Leishman Report message to a moderator Re: Strange Invalid Cursor error with sys_refcursor [message #358384 is a reply to message #358346] Tue, 11 November 2008 01:25 Michel Cadot Messages: 63912Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator Please read OraFAQ Forum Guide, especially "How to format your post?" section. Make sure that lines of code do not exceed 80 characters when you format. Indent the code (See SQL Formatter) and use code tags. Use the "Preview Message" button to verify. Also always post your Oracle version (4 decimals). Use SQL*Plus and copy and paste your session. Regards Michel Report message to a moderator Re: Strange Invalid Cursor error with sys_refcursor [message #358
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