ORACLE CURSOR_ALREADY_OPEN EXCEPTION HANDLING

ORACLE CURSOR_ALREADY_OPEN EXCEPTION HANDLING

Cursors are used to handle multiple rows returned by select statement.

Cursors are 2types

                     1.Implicit Cursors

                      2.Explicit Cursors.

Implicit Cursors are automatically opened & closed.  For ex: in For loop Range Variables.

Explicit Cursors Should be opened with Open method and must be closed with close method before re-opening it.

for ex:  First block of code displays employee name and no,

             Second block of code Updates Employee Salary.

declare
cursor c1 is select * from emp;
r emp%rowtype;
begin
open c1;
dbms_output.put_line('display employee''s name and number');
loop
fetch c1 into r;
exit when c1%notfound;
dbms_output.put_line(r.empno||' '||r.ename);
end loop;
dbms_output.put_line('update employee salary');
open c1;
end;

OUTPUT:

update employee salary
declare
*
ERROR at line 1:
ORA-06511: PL/SQL: cursor already open 

Handling CURSOR_ALREADY_OPEN Exception

               close c1;

               open c1;

It won't generate exception.

It can be handled in exception block

declare
cursor c1 is select * from emp;
r emp%rowtype;
begin
open c1;
dbms_output.put_line('display employee''s name and number');
loop
fetch c1 into r;
exit when c1%notfound;
dbms_output.put_line(r.empno||' '||r.ename);
end loop;
dbms_output.put_line('update employee salary');
open c1;

exception

when cursor_already_open then

         dbms_output.put_line('cursor must be closed before reopening it');

end;

Tags:ORACLE CURSOR_ALREADY_OPEN EXCEPTION HANDLING,Handling CURSOR_ALREADY_OPEN Exception in PL/SQL, ORA-06511: PL/SQL: cursor already open,