Oracle programmer's blog

I support this blog for my own fun. It is dedicated to the Oracle programming aspects I believe worth to publish and keep in memory.

Blog Archive

Friday, June 15, 2007

Cursor FOR LOOP vs. OPEN-CLOSE in relation with exceptions

One of the advantage of cursor FOR-LOOP cycle over ordinar OPEN-FETCH-CLOSE method
is the automatic close the cursor even it's scope is global. In other words FOR-LOOP
automatically closes the cursor in case of exit from the cycle over the exception:

SQL> create or replace package my_pkg
2 is
3 cursor s is select * from emp;
4 end;
5 /

Package created.

SQL> create or replace function f1
2 return number
3 is
4 begin
5 for v in my_pkg.s loop
6 dbms_output.put_line(v.ename);
7 raise no_data_found;
8 end loop;
9 return 1;
10 end;
11 /

Function created.

SQL> declare
2 c int;
3 begin
4 c := f1;
5 exception
6 when others then
7 if my_pkg.s%isopen then
8 dbms_output.put_line('Open');
9 elsif not my_pkg.s%isopen then
10 dbms_output.put_line('Closed');
11 end if;
12 end;
13 /
SMITH
Closed

PL/SQL procedure successfully completed.

SQL> create or replace function f1
2 return number
3 is
4 v my_pkg.s%ROWTYPE;
5 begin
6 open my_pkg.s;
7 fetch my_pkg.s into v;
8 dbms_output.put_line(v.ename);
9 raise no_data_found;
10 close my_pkg.s;
11 return 1;
12 end;
13 /

Function created.

SQL> declare
2 c int;
3 begin
4 c := f1;
5 exception
6 when others then
7 if my_pkg.s%isopen then
8 dbms_output.put_line('Open');
9 elsif not my_pkg.s%isopen then
10 dbms_output.put_line('Closed');
11 end if;
12 end;
13 /
SMITH
Open

PL/SQL procedure successfully completed.

No comments: