Re: problem with exception handling

In article <1103219054.656660.115830_at_c13g2000cwb.googlegroups.com>, ford_desperado_at_yahoo.com says...
>
>>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.
>

this would be an appropriate use of an autonomous transaction.

create procedure SD_LOG_SQL_ERROR( .... ) as

   pragma autonomous_transaction;
   ...
begin

   insert into log_table;
   ....
   commit;
end;

it will operate as an independent subtransaction - it will only see its work, it will only commit its work.

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation