Re: ignore_dup_row or ignore_dup_key

From: Andy Hardy <aph_at_ahardy.demon.co.uk>
Date: Tue, 28 Sep 1999 03:35:52 -0400
Message-ID: <+XAtHPAI6G83EwiE@ahardy.demon.co.uk>

In article <7so86f$avh$1_at_adenine.netfront.net>, Norris <johnnie_at_cooper.com.hk> writes
>For Oracle EXCEPTION handling, is there any statement like:
>"On Error Resume Next" ?
>
>For example, statement "Insert into tableA select col1, col2 from tableB"
>will not fail even if there are duplicate keys to be inserted.
>
>Andy Hardy <aph_at_ahardy.demon.co.uk> wrote:
>> In article <7smpp0$294s$1_at_adenine.netfront.net>, Norris
>> <johnnie_at_cooper.com.hk> writes
>>>I need to insert a lot of data into a table and I want to filter out duplicate
>>>records. Is it possible to create index with ignore_dup_row?
>>>
>
>> I don't think so.
>
>> How about running this through PL/SQL and making use of EXCEPTION and
>> the WHEN DUP_VAL_ON_INDEX then NULL?
>
>> Andy
>> --
>> Andy Hardy. PGP key available on request
>> ===============================================================
>
>

Not really. There wouldn't be a resume next from the statement given as it's all part of the same statement.

The sort of thing that I was thinking of was:

PROCEDURE my_proc
IS

CURSOR csr_b
IS
SELECT *
  FROM tableB
;

BEGIN

        FOR v_data IN csr_b LOOP
                BEGIN
                        INSERT 
                          INTO tableA
                                (
                                val1
                                ,val2)
                        VALUES
                                (
                                v_data.val1
                                ,v_data.val2
                                )
                        ;
                EXCEPTION
                        WHEN DUP_VAL_ON_INDEX THEN
                                NULL;
                END;
        END LOOP;

END my_proc;
--
Andy Hardy. PGP key available on request

---

Received on Tue Sep 28 1999 - 02:35:52 CDT