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?
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).
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).
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.
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).
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.
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).
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!')
Source: Stackoverflow.com