Re: trying to understand transaction control in pl/sql

Hi Manfred,

Thanks for your new post. I must point out however that in the case of the unhandled error in the unnamed block, both of the unnamed block's inserts get rolled back, whereas when the error is handled, the only thing that happens is that the insert that causes the error doesn't get executed - of course, because it's an invalid insert. SO in a sense, this invalid insert isn't rolled back, since it was never inserted in the first place. SO when the error is handled, nothing rolls back, technically speaking. When the error is NOT handled in the unnamed block, the two 'good' inserts DO get rolled back - which seems like, as a number of posts seem to suggest, that there's that 'invisible' savepoint when the unnamed block starts, to which the transaction rolls back. As you point out however the transaction is still active, and since it hasn't yet been committed, no OTHER session can see those two inserts that 'took'.

It occurs to me that what's happening is that, in it's effort to find a handler for the error, PL/SQL HAS to roll back the two inserts in the unnamed block when there is no handler in the unnamed block, since it has to 'leave' the unnamed block looking for a handler, and the only way it can do this is by rolling back everything the unnamed block has done.

How does that sound? Any thoughts? Manfred? Anyone?

Thanks all.

Ken

m.peter_at_alligatorsql.com (Manfred Peter) wrote in message news:<a3199c1.0410310312.e91785_at_posting.google.com>...
> Dear Ken,
>
> there is no inconsistency. When you start you example without an error
> handler,
> the first two insert will be executed and the thrid causes an
> exception.
> You do not catch the execption and therefore you will see the error in
> the
> caller program like sql plus or whatever. The transaction, that your
> anonymous
> block have stared is still active since you have end your transaction
> (with commit or rollback) - or you close the connection, which will
> cause a standard rollback (sometime you IDE will send a commit - when
> you close the application).
> So, when the transaction is still open, no other connection can see
> the inserted records.
> When you start you´re program with an exception handler, the third
> insert will also cause the exception, but you have catched this
> exception with your exception block.
> PL/SQL jumps into the exception block, but there is only a NULL
> statement, so PL/SQL does nothing and returns to the caller. In this
> example, the first
> row insert are executed and the third isn´t.
> There is no difference regarding the transaction handling.
> Try this:
>
> .
> .
> .
> EXCEPTION
> WHEN OTHERS THEN
> DBMS_OUTPUT.PUT_LINE('Error : ' || SQLERRM || ' Code: ' ||
> SQLCODE);
> END;
>
> And you will see the error in your output window of your IDE.
>
> Hope this was helpful ?
>
> Best regrads
> Manfred Peter
> Alligator Company Software GmbH
> http://www.alligatorsql.com
Received on Sun Oct 31 2004 - 18:16:55 CST