Re: Dynamic tables in PL/SQL proc

From: Arjan van Bentem <avbentem_at_DONT-YOU-DAREdds.nl>
Date: Fri, 5 Mar 1999 22:42:05 +0100
Message-ID: <36e04faf$0$6298@newton>

Thomas Kyte wrote
>if any of the delete or inserts fail -- the original data will be there. the
>procedure will run as a single statement, either they ALL work or none do.

Really? The procedure will run as a single statement?

If so, then I never realized that! I have to check at the office on Monday... I always figgered that if, for example, the second INSERT fails, the DELETE and the first INSERT still need to be rollbacked manually, like below (uhhh, I do not recall the exact syntax of setting the start of a transaction):

    begin

        set transaction my_start;
        delete from t;
        insert into t select * from t1;
        insert into t select * from t2;
        insert into t select * from t3;
        insert into t select * from t4;
        insert into t select * from t5;
        commit;
    exception
        when others then
            rollback to my_start;

    end;

Arjan. Received on Fri Mar 05 1999 - 15:42:05 CST