Re: PLSQL: Closing all open cursors

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;

• 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_at_gmail.com> 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

--
http://www.freelists.org/webpage/oracle-l