Re: update multiple rows continue past exceptions

From: Kenneth Koenraadt <kek_at_mail-online.dk>
Date: Sun, 13 Dec 2009 12:14:17 -0800 (PST)
Message-ID: <6d659742-5f27-40ea-b77d-996abb3c8fd0_at_g26g2000yqe.googlegroups.com>

On 11 Dec., 23:47, Thomas Kellerer <OTPXDAJCS..._at_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

Received on Sun Dec 13 2009 - 14:14:17 CST