Re: How to exit from pl/sql

On Jan 30, 10:21 am, wagen..._at_yahoo.com wrote:
> Hi,
>
> Oracle10g 10.2.0.2
>
> DECLARE
>    str constant VARCHAR2(150) := 'TRUNCATE TABLE t1;
> BEGIN
>    FOR i IN 1..500 LOOP
>        EXECUTE IMMEDIATE str;
>        ???
>    END LOOP;
> END;
> /
>
> How do I exit from the loop (???) if the EXECUTE statement is
> successful?
>
> Any pointers?
>
> Thanks
> wagen

Why you'd loop 500 times to truncate a table is beyond the scope of my comprehension, however this might provide a clue:

set serveroutput on size 1000000
DECLARE
   str constant VARCHAR2(150) := 'TRUNCATE TABLE t1';    rowct number:=9999999;
   has_foreign_key exception;
   pragma exception_init(has_foreign_key, -2266); BEGIN

           FOR i IN 1..500 LOOP
               EXECUTE IMMEDIATE str;
               select count(*) into rowct from t1;
               if rowct = 0 then
                        dbms_output.put_line('Passes: '||i);
                        exit;
               end if;
           END LOOP;
exception
           when has_foreign_key then
                raise_application_error(-20999, SQLERRM);
           when others then
                raise_application_error(SQLCODE, SQLERRM);

David Fitzjarrell Received on Wed Jan 30 2008 - 10:49:04 CST