• Sharebar

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:
, , , ,

 
Be Sociable, Share!
  • 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.

SEO Powered by Platinum SEO from Techblissonline