Re: update multiple rows continue past exceptions

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 -
Received on Mon Dec 14 2009 - 15:01:21 CST