Re: update multiple rows continue past exceptions

lora wrote on 11.12.2009 23:41:
> Hello all,
>
> I need to update as many rows as possible. The issue is that one row
> that fails causes the entire transaction to fail.
>
> How do I get around this? I'd like to complete all UPDATE rows that
> don't have the exception. The below doesn't seem to work.
>
> I'm using Oracle 10g
>
> Thanks a bunch!
>
> BEGIN
> UPDATE MYTAB SET request = REPLACE(request, '$tpsToModify',
> 'tpsToModify') WHERE request like '%$tpsToModify%';
> EXCEPTION
> when dup_val_on_index then
> dbms_output.put_line('DUPLICATE RECORD');
>
> END;
Use the error logging clause:

Details are here:

http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_errlog.htm#ARPLS680
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#ADMIN10261
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm#BCEGDJDJ

The examples all use INSERT, but you can use that with UPDATE just as well http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10007.htm#BCEEAAGC

Thomas Received on Fri Dec 11 2009 - 16:47:41 CST