1

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?

2 Answers 2

3

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;
2

An alternate expression for Oracle has implemented since regular expressions

REGEXP_REPLACE(SQLERRM, '.*\((.*)\).*','\1')

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.