Re: How to exit from pl/sql

From: <fitzjarrell_at_cox.net>
Date: Wed, 30 Jan 2008 09:49:56 -0800 (PST)
Message-ID: <4a42635e-5db9-464b-b172-8030d4bac1e5@y5g2000hsf.googlegroups.com>


On Jan 30, 11:01 am, wagen..._at_yahoo.com wrote:
> Thanks David for responding.
>
> 500 is just a test number. What we are trying to do:
>
> - we have a table that has a high amount of DML all the time (24x7) in
> fact every second
> - at midnight we want to truncate the table. the reason for the pl/sql
> procedure is to get around the ORA-00054 resource busy error.
>
> Your suggestion for select count(*) may not work in this scenario.

Possibly this might be of interest:

DECLARE
   str constant VARCHAR2(150) := 'TRUNCATE TABLE t1';    resource_bzy exception;
   pragma exception_init(resource_bzy, -54);    ctr number;
BEGIN

      FOR i IN 1..500000 LOOP
           ctr := i;
           begin
          EXECUTE IMMEDIATE str;
           if SQLCODE = 0 then
                   exit;
           end if;
           exception
           when resource_bzy then
                   null;
           end;
      END LOOP;

   dbms_output.put_line(ctr);
END;
/

David Fitzjarrell Received on Wed Jan 30 2008 - 11:49:56 CST

Original text of this message