Re: Dynamic tables in PL/SQL proc

From: Arjan van Bentem <avbentem_at_DONT-YOU-DAREdds.nl>
Date: Sat, 6 Mar 1999 11:50:00 +0100
Message-ID: <36e1085a$0$8428@newton>

Thomas Kyte wrote
>no, you don't need the exception handlers. a procedure is treated as
>an atomic statement -- it either SUCCEEDS and all statements
>succeed or it fails (and all statements fail).

Thomas, thanks again!

I got a bit worried about my knowledge. However, I have come up with an excuse that I'd like to share here :-)) Being a bit more awake now, I actually did know that a procedure is treated as a single statement. I got confused as I almost always have an exception handler. I know you'll know, but just for the sake of www.dejanews.com :

If one sets up an exception handler, one should also rollback any transactions within that block (if needed, of course), or re-raise the error. For example:

    create function my_function

        return varchar2
    as
    begin

        savepoint my_savepoint;
        delete from emp;

        select 1/0
        from dual; -- force exception

        return 'All rows deleted';
    exception
        when others then
            rollback to my_savepoint
            return 'No rows deleted';

    end;

In this function, it would be wrong to ommit the rollback, unless returning 'No rows deleted' is simply replaced with raise.

Arjan. Received on Sat Mar 06 1999 - 04:50:00 CST