Re: How to exit from pl/sql

From: malcolm <>
Date: Wed, 30 Jan 2008 09:39:31 -0800 (PST)
Message-ID: <>

On Jan 30, 5:01 pm, 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.

Well putting your truncate problems aside, and looking at this as a purely PL/SQL question...

If the truncate is not successful, you can rely on an exception from the server.

Therefore try this:

   str constant VARCHAR2(150) := 'TRUNCATE TABLE t1;    try_again boolean := true;
   while try_again LOOP

             EXECUTE IMMEDIATE str;
             try_again := false;
             when others then


Of course this code is not guaranteed to complete.... Received on Wed Jan 30 2008 - 11:39:31 CST

Original text of this message