At 05:00 AM 11/08/2012, Rick Debay wrote:

>FOR ... BEGIN
> INSERT_PROCEDURE RETURNING VALUE
> UPDATE TABLE WITH VALUE
> WHEN ... BEGIN
> LOG
> END
>END
>
>If the insert procedure directly or indirectly causes an exception the
>insert will be abandoned, the update will be skipped, and all data from
>previous or subsequent non-erroneous loop iterations will be preserved.
>
>Now if the update fails, would the inserted data within the same loop
>iteration be preserved? I've been assuming that everything within the
>BEGIN...END was abandoned.

The handler is not embedded in the loop that it handles: it follows directly after. The flow should be:

FOR ... do
BEGIN
INSERT_PROCEDURE RETURNING VALUE
UPDATE TABLE WITH VALUE
end
WHEN ... do
BEGIN
LOG
END

If an exception occurs anywhere in an iteration of that loop, everything within that iteration is abandoned, control drops into the WHEN block and executes that before returning to the top of the loop.

If a handler block is not found directly after the looping block, control moves out through the layers until one is found that fits the exception. Everything enclosed by the block whose handler is found is abandoned.

Ultimately, if no explicit handler is found in the enclosing layers that fits the exception, control drops to the EXIT statement and the entire work of the procedure is abandoned.

./heLen