PL/SQL: how to let it continue when unique constraint violated

[Jeff]

Hi,

How to let the PL/SQL continue to run when I select some rows and insert
them to another table, without quited immediately when unique constraint is
violated, such that the other records can still be inserted?

Jeff

[Andreas Michler]

Write a small sql-procedure which reads one record and inserts this one records
into the other table just like this.

declare
cursor f1 is select a from b;
c b.a%TYPE;

begin
open f1;
fetch f1 into c;
while f1%FOUND
loop
insert into b values (c);
fetch f1 into c;
end loop;
commit;
-- Unique .. error will be ignored
excption when DUP_VAL_ON_INDEX NULL;
-- or all error will be ignored
exception then others then NULL
end
/

Jeff wrote:

--
-------------------------------------
ADICOM Informatik GmbH
Andreas Michler
Wiesfleckenstr. 34
72336 Balingen
Tel: 07433/9977-57,Fax: -90
E-Mail: Andreas...@adicom.de
http:\\www.adicom.de
-------------------------------------

[david_g]

In order to ignore the duplicate key value error and continue, you need
the dup_value_in_index as a local exception:

declare cursor c1 is select myvalue from mytable;

begin

for l1 in c1 loop

begin

insert into mynewtable values (l1.myvalue);

exception

when dup_value_in_index then

null;
end;

end loop;

end;

* Sent from RemarQ http://www.remarq.com The Internet's Discussion Network *
The fastest and easiest way to search and participate in Usenet - Free!

[Andy Hardy]

In article <867gaa$g26$1...@justice.csc.cuhk.edu.hk>, Jeff
<k...@cintec.cuhk.edu.hk> writes

>Hi,
>
>How to let the PL/SQL continue to run when I select some rows and insert
>them to another table, without quited immediately when unique constraint is
>violated, such that the other records can still be inserted?
>

You need to look at EXCEPTIONs and DUP_VAL_ON_INDEX

e.g.

PROCURE my_procedure
IS
CURSOR my_from_table
IS
SELECT x,y
FROM table1
;

BEGIN
FOR v_data IN my_from_table LOOP
BEGIN
INSERT
INTO my_to_table
VALUES (v_data.x, v_data.y);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
--
-- ignore any duplicates
--
NULL;
WHEN OTHERS THEN
RAISE;
END;
END LOOP;
END my_procedure;
>Jeff
>
>

--
Andy Hardy. PGP key available on request
===============================================================