Re: problem with exception handling

From: <ford_desperado_at_yahoo.com>
Date: 16 Dec 2004 09:44:14 -0800
Message-ID: <1103219054.656660.115830@c13g2000cwb.googlegroups.com>

>all statements are atomic.
>sql> exec foo;
>that either 100% succeeds or 100% fails. (yes, there is an implicit
>savepoint in
>there to make everything atomic)

Thanks Tom

I have an SP issuing several DML commands. in the procedure that called the failed one, I wanted to figure out which DML failed, and I noticed that changes made by a failed SP are rolled back. I did not know why, and I was very curious. I guessed there is an implicit savepoint. Thank you for confirming that.

Recording which DML has failed is very easy:

V_STEP VARCHAR2(20);
...
V_STEP := 'INSERT INTO TABLE1';
INSERT INTO TABLE1 ... V_STEP := 'INSERT INTO TABLE2';
INSERT INTO TABLE2 ... ...

EXCEPTION
WHEN OTHERS THEN
SD_LOG_SQL_ERROR(SQLCODE, SQLERRM, 'My_SP_Name '|| V_STEP,
TRIM(My_SP_parameters));
ROLLBACK;
RAISE; I've described SD_LOG_SQL_ERROR in my previous post. Received on Thu Dec 16 2004 - 11:44:14 CST