How to catch a unique constraint error in a PL/SQL block?
EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
UPDATE
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!')
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).