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

From: Andy Hardy <aph_at_ahardy.demon.co.uk>
Date: Fri, 21 Jan 2000 08:39:57 +0000
Message-ID: <uALZyRAdtBi4Ew9o@ahardy.demon.co.uk>

In article <867gaa$g26$1_at_justice.csc.cuhk.edu.hk>, Jeff <kit_at_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

---

Received on Fri Jan 21 2000 - 02:39:57 CST