Reasons for PL/SQL Code Block Failure with ORA-01001: invalid cursor

Error Description
PL/SQL block (procedure, function, package etc) execution failing with ORA-01001: invalid cursor error message.

Reasons for this error.
  1. 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.
  2. If you try to close a cursor which is already closed, you get the error message.

Examples

SQL> create or replace procedure pr_sal is
cursor 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 loop
end; 

Procedure created.

SQL>  exec pr_sal;
Rupal
Hero
Jain
John
Riju
Sam
Kiran
Peter
King
Roshan
Roshan
David
BEGIN pr_sal; END;

*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at "SCOTT.PR_SAL", line 7
ORA-06512: at line 1


SQL> create or replace procedure pr_sal is
cursor 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 on
SQL> exec pr_sal;
Rupal
Hero
Jain
John
Riju
Sam
Kiran
Peter
King
Roshan
Roshan
David

PL/SQL procedure successfully completed.

SQL> create or replace procedure pr_sal is
cursor 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 cursor
ORA-06512: at "SCOTT.PR_SAL", line 6
ORA-06512: at line 1
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google