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 :
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;
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>
ORA-01001, CLOSE cursor, open cursor, Fetch cursor, Oracle cursor
Pingback: ora 06512
Keep up the good work, I like your writing.
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.