Saturday, February 5, 2011

How to catch a unique constraint error in a PL/SQL block?

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?

  • 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!')
    
    From EvilTeach
  • EXCEPTION
          WHEN DUP_VAL_ON_INDEX
          THEN
             UPDATE
    
  • 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).

    Thiago Arrais : 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.
    Stew S : Ricardo's answer was the correct named exception, but I think William's suggestion will help more people in the long run.
    From William

0 comments:

Post a Comment