In a procedure I'm inserting values into different tables, whenever a unique/primary key violation occurs for a table, I want to handle the exception specifically for that table. How can I catch different duplicate value exceptions?
Add a comment
|
2 Answers
Check some tutorials like this.
Your unique/primary key violation will be a DUP_VAL_ON_INDEX exception.
4 Comments
Rnet
Hmm, but how will I know from which table insert it was thrown?
Udo Held
Then you probably have to wrap each of your statments with such an exception block or you set yourself a marker so that you know which has been the last successful insert.
Rnet
Hmm, this will be for primary key violations, but can I handle foreign key errors differently?
Udo Held
This should throw an ORA-02291 check the last section. You can get the error code after doing a WHEN OTHERS. Make your behavior dependent on that error code.
When you had created your table, you should have created a TRIGGER and a SEQUENCE to run Before Insert on your table and to catch if :NEW.PRIMAY_KEY is null then get it from the sequence.
Using this method you will always avoid DUP_VAL_ON_INDEX.
I would avoid using WHEN OTHERS as this has no real use in a real error handling logic.
