Skip to Main Content
  • Questions

  • Skip rows with duplicate primary key or unique values

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Naga.

Asked: February 04, 2016 - 3:00 pm UTC

Last updated: February 04, 2016 - 5:26 pm UTC

Version: 11.0

Viewed 10K+ times! This question is

You Asked

---->I need to process record in anonymous block for insert statement, suppose if any error occur during
inserting a record i want to skip those record and rest of the record need to be inserted .
Table with primary key constraint or unique key constraint will be there.
EMP
EMP_ID
10
Can you please explain with an example?

and Chris said...

From 11gR2 on you could use the IGNORE_ROW_ON_DUPKEY_INDEX hint:

SQL> create table t (
  2    pk integer not null primary key
  3  );

Table created.

SQL>
SQL> insert into t values (1);

1 row created.

SQL> insert into t values (1);
insert into t values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (CHRIS.SYS_C0019473) violated


SQL> insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX (t (pk)) */ into t values (1);

0 rows created.

SQL>
SQL> insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX (t (pk)) */ into t
  2    select rownum from dual connect by level <= 3;

2 rows created.

SQL>
SQL> select * from t;

        PK
----------
         1
         2
         3


This just suppresses the error though. So you don't know there was a duplicate.

DML error logging gives a similar outcome, but stores the failing rows for you:

https://www.youtube.com/watch?v=8WV15BzQIto

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Its not working in plsql

sai, February 27, 2018 - 11:19 am UTC

begin
for i in 1..5
loop
insert /*+ IGNORE_ROW_ON_DUPKEY_INDEX (t (pk)) */ into t values(i);
end loop;
end;

ORA-01400: cannot insert NULL into xyz)
ORA-06512: at line 6
01400. 00000 - "cannot insert NULL into (%s)"
*Cause: An attempt was made to insert NULL into previously listed objects.
*Action: These objects cannot accept NULL values.

Soryy its working

sai, February 27, 2018 - 11:29 am UTC

Am just confused.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions