Re: PLSQL: Closing all open cursors
- From: "Jared Still" <jkstill@xxxxxxxxx>
- To: saints.richard@xxxxxxxxx
- Date: Thu, 24 Jan 2008 11:02:52 -0800
just use cursor attributes to determine if the cursor is open:
declare
cursor c1
is
select table_name from x;
v_table_name x.table_name%type;
v_count integer := 1;
begin
open c1;
loop
fetch c1 into v_table_name;
if c1%notfound or v_count > 2000 then
exit;
end if;
v_count := v_count + 1;
end loop;
-- does cursor need to be closed
if c1%isopen then -- cursor is open
dbms_output.put_line('Closing Cursor C1');
close c1;
end if;
end;
/
On Jan 24, 2008 3:51 AM, Ricardo Santos <saints.richard@xxxxxxxxx> wrote:
>
>
> Hello to you all,
>
> I would like to know if there is any simple way to close all open cursors
> by a PL/SQL procedure when an exception raises and the control goes to the
> EXCEPTION section of the procedure. Should I explicitly use the CLOSE
> statement on the EXCEPTION section for all cursors I use on the procedure ?
>
> I'm using OPEN and FETCH statements to deal with my cursors and not FOR.
>
> During some tests to my application, I got an exception (divide by zero)
> and when I tried to execute the procedure on the same session, I got the
> error, cursor already open. My intention is to avoid this situation.
>
>
>
> I'm developing on Database Server version 10.1.0.5.
>
>
>
>
>
> Thanks for all your attention.
>
>
>
> Best Regards,
>
> Ricardo Santos.
>
>
>
--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Other related posts:
