Re: ignore error

In article <86rvvh$q6e$1_at_justice.csc.cuhk.edu.hk>,   "Jeff" <kit_at_cintec.cuhk.edu.hk> wrote:
>
> How do you ignore the unique constraint error when inserting tables
from a
> selected results, such that all records are inserted with duplicates
records
> left behind?
>
> Thanks.
>
> Jeff
>

SQL deals in set operations and a set is either all inserted sucessfully or the entire set fails so in native SQL you can not do the insertions such that some of the rows are inserted but the duplicates are ignored. You can do this is pl/sql as:

declare
cursor c1 is select ..... -- rows to be inserted r_row c1%rowtype;
begin
open c1;
loop
  fetch c1 into r_row ;
  exit when c1%notfound ;
  insert into ......
exception
  when dup_val_on_index then
    null;
end loop;
end;
/

I types this on the fly so syntax error are possible. The logic should be good so see the pl/sql manual when you get a compile error and you should be OK.
--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Jan 28 2000 - 12:12:02 CST