Oracle INSERT INTO SELECT(...) DUP_VAL_ON_INDEX exception behavior

Question

I have a stored procedure which looks like this:

BEGIN
  INSERT INTO result_table
  (SELECT (...) FROM query_table);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    NULL;
END;

I'm doing it in a loop which passes multiple parameters to the SELECT statement and in some cases some of the values might duplicate that is why I have to catch the DUP_VAL_ON_INDEX exception.

My question is that if the SELECT statement returns more rows and only one from them exists already in *result_table*, f. ex.

1 'A'
2 'B'
3 'C'

And first row (1 'A') would already be in the table, would other rows which don't exist (second and third from case above) be inserted? Or none of them would be inserted at all?

I'm afraid that none of them would be inserted (and my test case partially confirms that)... If so, what option do I have to achieve desired bahavior? Is there a good way to insert the rows that don't violate the primary key using the construction above?

score 2 · Answer 1 · answered Feb 01 '13 at 15:38

2

You are right, if one record violates constraint, none will be inserted. I'd do

INSERT INTO result_table
(SELECT (...) FROM query_table a WHERE NOT EXISTS
(SELECT NULL FROM result_table b WHERE b.b_unique_key = a.b_unique_key)
)

Another option is to use error logging

INSERT INTO result_table
SELECT ... FROM query_table 
LOG ERRORS INTO err$_dest ('INSERT') REJECT LIMIT UNLIMITED;

Note: you have to create error table prior to run this query.

answered Feb 01 '13 at 15:38

a1ex07

36,826
12
90
103

But I'm afraid that because of multiple simultaneous transactions there can be a way that this select statement fetches data which could be inserted in other transaction before this insert statement and that's why it gets this exception. I've already written the SELECT query in a way that it shouldn`t fetch rows that would violate the index. So my hypothetical scenario looks like this: transaction A fetches rows 1,2,3, transaction inserts row 1 fetched before, transaction A tries to insert 3 rows and raises exception because row 1 is already inserted. How could I handle that? – Ziouas Feb 01 '13 at 16:20
I thought of maybe adding the same SELECT statement in the exception handler, but this could also raise the DUP_VAL_ON_INDEX exception. I don't see any good and valid solution here... :/ – Ziouas Feb 01 '13 at 16:23
@Ziouas: Then I have couple other suggestions. 1. Insert rows individually ( for instance, using cursors ). 2. `LOCK result_table IN SHARE ROW EXCLUSIVE MODE` and then do your insert (beware : nobody else can update/delete/insert row while you holding lock). – a1ex07 Feb 01 '13 at 17:05
That is not an option unfortunately in my case. – Ziouas Feb 03 '13 at 21:24

score 2 · Accepted Answer · answered Feb 02 '13 at 09:54

2

You can use the MERGE statement. Insert the records if they don't exist and do nothing if they already exist.

http://psoug.org/reference/merge.html

answered Feb 02 '13 at 09:54

Alen Oblak

3,285
13
27

That is a really helpful answer and it looks like it shold work the way i'd like it to. I'll try it on monday and post the feedback. – Ziouas Feb 02 '13 at 12:44
It works, but it works noticeably slower, but in my case it'll do for now. – Ziouas Feb 04 '13 at 09:23
1

On many threads it produces the same errors as my original statement unfortunately. I'll have to go with cursor :/ – Ziouas Feb 05 '13 at 08:27
What do you mean "many threads"? Do you have multiple sessions inserting at the same time? In that case, you need to have some sort of serialization. One session must finish the insert, only after that the next session can start, and so on. But, this will not scale. – Alen Oblak Feb 05 '13 at 08:43
We have multiple workers running that procedure simultaneously and trying to make that as user-friendly and scalable as possible. But I believe that this task must be fully serialised unfortunately, so that's a bummer. – Ziouas Feb 05 '13 at 09:20

score 0 · Answer 3 · answered Feb 01 '13 at 15:43

If you're using 11g, then you can use the ignore_row_on_dupkey_index hint to suppress the errors:

create table tab (id integer);

alter table tab add constraint tab_pk primary key (id);

insert into tab
  select rownum from dual connect by level <= 1;

1 rows inserted.
        ID
----------
         1 

SELECT * FROM tab;

insert into tab
  select rownum from dual connect by level <= 3;

SQL Error: ORA-00001: unique constraint (CAM_OWNER.TAB_PK) violated

insert /*+ ignore_row_on_dupkey_index(tab, tab_pk) */into tab
  select rownum from dual connect by level <= 3;

SELECT * FROM tab;

2 rows inserted.
        ID
----------
         1 
         2 
         3

Oracle INSERT INTO SELECT(...) DUP_VAL_ON_INDEX exception behavior

3 Answers3

Linked