Invalid Cursor Error In 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 Ion Excel-DB Don Burleson Blog
Ora-01001 Invalid Cursor Ref Cursor
ORA-01001: invalid cursor tips java.sql.sqlexception: ora-01001: invalid cursor Oracle Error Tips by Burleson Consulting The Oracle docs note this on the ora-01001 error*: ORA-01001 invalid cursor
Invalid Cursor Exception Example In Oracle
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 ora-01001 invalid cursor for loop 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 sql error invalid cursor halt application 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 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 ne
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 this site About Us Learn more about
Maxopencursors Oracle 11g
Stack Overflow the company Business Learn more about hiring developers or posting ads with invalid cursor sql us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a
Increase The Area Size And Maxopencursors Options
community of 4.7 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 http://www.dba-oracle.com/t_ora_01001_invalid_cursor.htm 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 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 http://stackoverflow.com/questions/11341166/strange-error-ora-01001-invalid-cursor-in-procedure 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 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 71
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 this site About Us Learn more about Stack Overflow http://stackoverflow.com/questions/17602347/pl-sql-invalid-cursor-error 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 http://plsql.globinch.com/ora-01001-invalid-cursor/ million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up PL/SQL invalid cursor error up vote 0 down vote favorite So I really don't have any idea why my invalid cursor cursor isn't working. The select statement works fine, so I'm not sure why the SQL server is throwing the error. The For loop has an implicit open and close, so that shouldn't be the problem either. Any ideas? DECLARE var_lname customer.c_last%TYPE; var_fname customer.c_first%TYPE; var_addr customer.c_address%TYPE; var_phone customer.c_dphone%TYPE; CURSOR expl_cursor IS SELECT c_last, c_first, c_address, c_dphone from customer; cust_record expl_cursor%ROWTYPE; BEGIN DBMS_OUTPUT.PUT_LINE('ClearWater Traders Mailing List'); FOR cust_record IN expl_cursor LOOP FETCH expl_cursor INTO ora-01001 invalid cursor cust_record; var_lname := cust_record.c_last; var_fname := cust_record.c_first; var_addr := cust_record.c_address; var_phone := cust_record.c_dphone; DBMS_OUTPUT.PUT_LINE(var_lname || ' ' || var_fname || ' ' || var_addr || ' ' || var_phone); END LOOP; END; Here's the error: ERROR at line 1: ORA-01001: invalid cur ORA-06512: at line 15 sql plsql oracle10g share|improve this question asked Jul 11 '13 at 19:59 Scuba Steve 491316 add a comment| 1 Answer 1 active oldest votes up vote 2 down vote accepted Ah I solved this. Can't do a FETCH inside a for loop. Since the cursor is incremented automatically. share|improve this answer answered Jul 11 '13 at 20:03 Scuba Steve 491316 1 Yes. You were mixing the two ways to loop around a cursor result set. You either explicitly open/fetch/close, or use the implicit for ... in ... loop version. More in the documentation. –Alex Poole Jul 11 '13 at 20:30 +1 - solved own problem. –Bob Jarvis Jul 11 '13 at 21:13 Lol thanks Bob. –Scuba Steve Jul 15 '13 at 6:51 I actually solved this like 10 seconds after I posted the question. Sometimes writing out the problem helps more than any answers you might get. –Scuba Steve Jul 15 '13 at 6:52 add a comment| Your Answer draft saved draft
2 ORA-01001: invalid cursor TweetSumoMe Tweet ORA-01001: invalid cursor error occurs when you tried to reference a cursor that does not yet exist. A few scenarios given below. 1. FETCH cursor before opening the cursor. 2. CLOSE cursor before opening the cursor. 3. FETCH cursor after closing the cursor. See the blow example: When you write generic cursor you can either use FETCH..,OPEN… and CLOSE cursor statements Or you can use the FOR LOOP for iterating through the cursor. When you use FOR LOOP for iteration no need of Explicit use of FETCH..,OPEN… and CLOSE cursor statements. The cursor will open automatically when entering FOR LOOP and will close the cursor once the loop ends. Read:Cursor | Oracle PL/SQL Cursors and example. Read:ORA-01000: maximum open cursors exceeded. Read:Oracle Cursors | OPEN ,FETCH and CLOSE Cursor statements. If you use CLOSE statement after the FOR LOOP Oracle will throw the error : ORA-01001:Invalid Cursor See the example below create or replace procedure Param_Cursor as CURSOR PERSON_CUR(pAge NUMBER) is Select * from Person p where p.age = pAge; BEGIN for i in PERSON_CUR(13) loop dbms_output.put_line(i.firstname); end loop; close PERSON_CUR; END Param_Cursor; Execute this: SQL> exec Param_Cursor; begin Param_Cursor; end; ORA-01001: invalid cursor ORA-06512: at "TEST.PARAM_CURSOR", line 10 ORA-06512: at line 1 SQL> Technorati Tags: ORA-01001, CLOSE cursor, open cursor, Fetch cursor, Oracle cursor Be Sociable, Share! Tweet Posted by Binu George Cursors, Error Codes, Fundamentals, Oracle, SQL Error Subscribe to RSS feed Pingback: ora 06512() http://www.studentloansadvice.org student loans Keep up the good work, I like your writing. João So much thanks. You save me from a big problem. The solution is very simple, but I never find out. Thanks again. And sorry for my bad english. PL/SQL and SQL Tips in Email: Google+ Post-Plugin Library missing Categories Constraints (12) Cursors (5) Data types (7) Error Codes (10) Functions (19) Fundamentals (27) Key Words (23) Oracle (55) Oracle Date (10) Schema (1) SQL Error (10) SQL Tips (32) SQL Transactions (3) Tables (19) Tablespaces (