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

Question

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

User · Answer

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

User · Answer

EXCEPTION       WHEN DUP VAL ON INDEX       THEN          UPDATE

User · Answer

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

User · Answer

EXCEPTION       WHEN DUP VAL ON INDEX       THEN          UPDATE

User · Answer

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

User · Answer

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

User · Answer

EXCEPTION       WHEN DUP VAL ON INDEX       THEN          UPDATE

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

EXCEPTION       WHEN DUP VAL ON INDEX       THEN          UPDATE

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

Examples related to oracle

Examples related to plsql