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