Re: ignore_dup_row or ignore_dup

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 27 Sep 1999 16:50:11 -0400
Message-ID: <ZNjvNzKmHlR80gWcOZiOpvnx8kzR@4ax.com>

A copy of this was sent to Norris <johnnie_at_cooper.com.hk> (if that email address didn't require changing) On 27 Sep 1999 03:54:07 GMT, you wrote:

>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?

No -- not directly. SQL Server as I recall has a mode whereby if I insert a duplicate record (one that would violate a primary key for example) it'll silently ignore this. Oracle does not have such a mode directly.

Starting with Oracle8 (8.0 and up) there is a way to do this. It would look like this:

tkyte_at_8.0> create table t_table ( x int primary key ); Table created.

tkyte_at_8.0> create or replace view t as select * from t_table; View created.

tkyte_at_8.0> create or replace trigger t_trigger   2 INSTEAD OF INSERT ON T
  3 for each row
  4 begin
  5 insert into t_table values ( :new.x );   6 exception

  7          when DUP_VAL_ON_INDEX then
  8                  null;

9 end;
10 /
Trigger created.

tkyte_at_8.0> insert into t values ( 1 );

1 row created.

tkyte_at_8.0> insert into t values ( 1 );

1 row created.

tkyte_at_8.0> insert into t values ( 1 );

1 row created.

tkyte_at_8.0> insert into t values ( 1 );

1 row created.

tkyte_at_8.0> insert into t values ( 1 );

1 row created.

tkyte_at_8.0> insert into t values ( 2 );

1 row created.

tkyte_at_8.0>
tkyte_at_8.0> select * from t;

         1
         2

You would expose to your developers the view T -- not the table T_TABLE. T will behave like 'NO_DUP_KEY' is on. You could setup a package to allow you to enable/disable this behaviour -- for example:

tkyte_at_8.0> create or replace package globals   2 as
  3 no_dup_key boolean default FALSE;   4 end;
  5 /

Package created.

tkyte_at_8.0> 
tkyte_at_8.0> 
tkyte_at_8.0> create or replace trigger t_trigger

  2 INSTEAD OF INSERT ON T
  3 for each row
  4 begin
  5 insert into t_table values ( :new.x );   6 exception

  7          when DUP_VAL_ON_INDEX then
  8                  if ( globals.no_dup_key )
  9                  then
 10                          null;
 11                  else
 12                          raise;
 13                  end if;

14 end;
15 /

Trigger created.

tkyte_at_8.0> 
tkyte_at_8.0> 
tkyte_at_8.0> insert into t values ( 1 );
insert into t values ( 1 )
            *

ERROR at line 1:

ORA-00001: unique constraint (TKYTE.SYS_C007832) violated
ORA-06512: at "TKYTE.T_TRIGGER", line 9
ORA-04088: error during execution of trigger 'TKYTE.T_TRIGGER'

tkyte_at_8.0>
tkyte_at_8.0> exec globals.no_dup_key := TRUE;

PL/SQL procedure successfully completed.

tkyte_at_8.0> insert into t values ( 1 );

1 row created.

tkyte_at_8.0> insert into t values ( 1 );

1 row created.

tkyte_at_8.0> insert into t values ( 1 );

1 row created.

tkyte_at_8.0>
tkyte_at_8.0> select * from t;

         1
         2

so, using the package you must enable the NO_DUP_KEY behaviour explicitly by setting the package variable (affects your SESSION only -- other sessions have their own package states)

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Sep 27 1999 - 15:50:11 CDT

Re: ignore_dup_row or ignore_dup_key