Find the violated index on ORA-00001 (dup_val_on_index)

error handlingoracle

I have a table with several indexes. In my application code there could be instances where any of these indexes could be violated, depending on user input.

I would like to log exactly which index was violated when my PL/SQL code stumbles upon an ORA-00001.

Is there a way to know which index violation caused Oracle to raise the dup_val_on_index?

Best Answer

AskTom has already discussed this.

This could be achieved in following manner:

BEGIN
   -- code containing INSERT operation
EXCEPTION
   WHEN dup_val_on_index THEN
      Trace_SYS.Message(substr(sqlerrm, instr(sqlerrm, '(')+1,
                                        instr( sqlerrm, ')') - instr( sqlerrm,'(') -1 ));
END;
Related Question