update multiple rows continue past exceptions
in [Oracle]
|
From: lora on 11 Dec 2009 17: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;
From: Thomas Kellerer on 11 Dec 2009 17:47 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 11 Dec 2009 18:43 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 12 Dec 2009 01:41 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 13 Dec 2009 15:14 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
|
Next
|
Last
Pages: 1 2 3 4 Prev: Optimizer 10053 trace file, strange choises made by Oracle?? Next: Compression |