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
From Ricardo Villamil -
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