Re: update multiple rows continue past exceptions
Date: Fri, 11 Dec 2009 23:47:41 +0100
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!
> UPDATE MYTAB SET request = REPLACE(request, '$tpsToModify',
> 'tpsToModify') WHERE request like '%$tpsToModify%';
> when dup_val_on_index then
> dbms_output.put_line('DUPLICATE RECORD');
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