Re: update multiple rows continue past exceptions
Date: Sun, 13 Dec 2009 12:14:17 -0800 (PST)
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 -
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;
- Now try to violate unique constraint with an UPDATE
SQL> select object_id from daf order by 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;
- Kenneth Koenraadt