From: lora on
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;
From: Thomas Kellerer on


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



From: jimmyb on
On Dec 11, 2:41 pm, lora <anjela_...(a)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"
From: Mladen Gogala on
On Fri, 11 Dec 2009 15:43:43 -0800, jimmyb wrote:

> On Dec 11, 2:41 pm, lora <anjela_...(a)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.com
From: Kenneth Koenraadt on
On 11 Dec., 23:47, Thomas Kellerer <OTPXDAJCS...(a)spammotel.com> wrote:
> 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_e...http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14231/tab...http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/sta...
>
> The examples all use INSERT, but you can use that with UPDATE just as wellhttp://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/sta...
>
> Thomas- Skjul tekst i anførselstegn -
>
> - Vis tekst i anførselstegn -

Hi Thomas,

That's interesting : The online doc clearly states that UPDATE works
just as INSERT's regarding error logging..however, I have this simple
test case, which works with INSERT's, but not UPDATE's. The OP wanted
just that, so it may not work for him. Any thoughts ?

-------------------------

SQL> create table daf as select * from dba_objects where rownum <=5;

Tabel er oprettet.

SQL> create unique index daf_object_id_idx on daf(object_id);

Indeks er oprettet.

SQL> exec DBMS_ERRLOG.CREATE_ERROR_LOG('DAF','DAF_ERR');

PL/SQL-procedure er udf°rt.

-- Now try to violate unique constraint with an INSERT

SQL> insert into daf select * from daf where rownum <=1 log errors
into daf_err ('INSERTING') reject limit unlimited;

0 rµkker er oprettet.

-- Correct, row isn't inserted, and error is recorded in the error log
table
SQL> select count(*) from daf_err;

COUNT(*)
----------
1

-- Now try to violate unique constraint with an UPDATE

SQL> select object_id from daf order by object_id;

OBJECT_ID
----------
15
20
28
29
44

SQL> update daf set object_id = 20 where object_id = 15 log errors
into daf_err ('UPDATING') reject limit unlimited;
update daf set object_id = 20 where object_id = 15 log errors into
daf_err ('UPDATING') reject limit unlimited
*
FEJL i linie 1:
ORA-00001: unik begrµnsning (A.DAF_OBJECT_ID_IDX) er overtrÕdt

-- Wrong, Got execption, and row isn't recorded in DAF_ERR
SQL> select count(*) from daf_err;

COUNT(*)
----------
1

- Kenneth Koenraadt