Re: update multiple rows continue past exceptions

From: jimmyb <jimmybrock_at_gmail.com>
Date: Mon, 14 Dec 2009 13:28:43 -0800 (PST)
Message-ID: <8c106929-6c7c-43e1-ae73-804246344e20_at_1g2000vbe.googlegroups.com>

On Dec 14, 1:01 pm, lora <anjela_..._at_yahoo.com> wrote:
> Yikes, the DBMS_ERRLOG is not working on my end.
>
> SQL> EXEC dbms_errlog.create_error_log( 'MYTAB');
> BEGIN dbms_errlog.create_error_log( 'MYTAB'); END;
>
> *
> ERROR at line 1:
> ORA-20069: Unsupported column type(s) found: REQUEST2 RESPONSE
> ORA-06512: at "SYS.DBMS_ERRLOG", line 233
> ORA-06512: at line 1
>
> On Dec 13, 3:14 pm, Kenneth Koenraadt <k..._at_mail-online.dk> wrote:
>
>
>
> > 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......
>
> > > 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- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

Are you using large objects or other object types? I don't think those are supported. Received on Mon Dec 14 2009 - 15:28:43 CST