Re: update multiple rows continue past exceptions
From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Sat, 12 Dec 2009 06:41:23 +0000 (UTC)
Message-ID: <hfvduj$1ei$1_at_news.eternal-september.org>
On Fri, 11 Dec 2009 15:43:43 -0800, jimmyb wrote:
>> 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;
Date: Sat, 12 Dec 2009 06:41:23 +0000 (UTC)
Message-ID: <hfvduj$1ei$1_at_news.eternal-september.org>
On Fri, 11 Dec 2009 15:43:43 -0800, jimmyb wrote:
> On Dec 11, 2:41 pm, lora <anjela_..._at_yahoo.com> wrote:
>> 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;
> > http://www.oracle.com/technology/oramag/oracle/09-mar/o29plsql.html > > Scroll half-way down to "DML Error Logging"
Why not simply use the merge statement?
-- http://mgogala.byethost5.comReceived on Sat Dec 12 2009 - 00:41:23 CST