43

Say I have an Oracle PL/SQL block that inserts a record into a table and need to recover from a unique constraint error, like this:

begin
    insert into some_table ('some', 'values');
exception
    when ...
        update some_table set value = 'values' where key = 'some';
end;

Is it possible to replace the ellipsis for something in order to catch an unique constraint error?

Thiago Arrais
  • 33,360
  • 7
  • 30
  • 34
  • To use exceptions this way is a bit slow because raising exceptions takes quite a lot of time. Try merge. – tuinstoel Jan 13 '09 at 20:33
  • 1
    Agreed. But keep in mind that this example was just one of many possible use cases. The question really is "what is the id for unique constraint error?". That's why I voted William's answer up but accepted Ricardo's. – Thiago Arrais Jan 14 '09 at 11:34
  • 2
    Exceptions in PL/SQL code are not as expensive as in managed or high level languages (C#, Java). In a DB application the real "slowness" is caused by db access, a PL/SQL exception cost is insignificant in this context – Ricardo Villamil Jan 16 '09 at 16:25

4 Answers4

84
EXCEPTION
      WHEN DUP_VAL_ON_INDEX
      THEN
         UPDATE
Ricardo Villamil
  • 5,031
  • 2
  • 30
  • 26
28

I'm sure you have your reasons, but just in case... you should also consider using a "merge" query instead:

begin
    merge into some_table st
    using (select 'some' name, 'values' value from dual) v
    on (st.name=v.name)
    when matched then update set st.value=v.value
    when not matched then insert (name, value) values (v.name, v.value);
end;

(modified the above to be in the begin/end block; obviously you can run it independantly of the procedure too).

William
  • 791
  • 4
  • 5
  • 1
    This helps with this specific use case, but it was an example only. The question is really about the id for an unique constraint error, so I'm upvoting this answer because it is indeed helpful, but Ricardo's will be the accepted one. – Thiago Arrais Jan 14 '09 at 11:36
  • 1
    Ricardo's answer was the correct named exception, but I think William's suggestion will help more people in the long run. – Stew S Jan 16 '09 at 12:41
  • Executing merge in parallel DB sessions can also lead to unique constraint violation in one session – Sergey94 Jun 18 '20 at 06:42
15

I suspect the condition you are looking for is DUP_VAL_ON_INDEX

EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        DBMS_OUTPUT.PUT_LINE('OH DEAR. I THINK IT IS TIME TO PANIC!')
EvilTeach
  • 28,120
  • 21
  • 85
  • 141
0

As an alternative to explicitly catching and handling the exception you could tell Oracle to catch and automatically ignore the exception by including a /*+ hint */ in the insert statement. This is a little faster than explicitly catching the exception and then articulating how it should be handled. It is also easier to setup. The downside is that you do not get any feedback from Oracle that an exception was caught.

Here is an example where we would be selecting from another table, or perhaps an inner query, and inserting the results into a table called TABLE_NAME which has a unique constraint on a column called IDX_COL_NAME.

INSERT /*+ ignore_row_on_dupkey_index(TABLE_NAME(IDX_COL_NAME)) */ 
INTO TABLE_NAME(
    INDEX_COL_NAME
  , col_1
  , col_2
  , col_3
  , ...
  , col_n)
SELECT 
    INDEX_COL_NAME
  , col_1
  , col_2
  , col_3
  , ...
  , col_n);

This is not a great solution if your goal it to catch and handle (i.e. print out or update the row that is violating the constraint). But if you just wanted to catch it and ignore the violating row then then this should do the job.

Clay
  • 577
  • 3
  • 8
  • 14