advertisements
_____________________________________________________________________________________________________________________
Error Description
PL/SQL block (procedure, function, package etc) execution failing with ORA-01001: invalid cursor error message.
Reasons 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 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
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment