CODEWITHSUNDEEP
sqloracle-databaseplsql
jimbo R
jimbo R

Reputation: 253

How to catch a unique constraint error for each Index?

I have a data table with many columns unique indexed. When I update, there may be some columns duplicates. I am using the following way to catch errors

 EXCEPTION
     WHEN DUP_VAL_ON_INDEX
        THEN OPEN cur FOR SELECT 'DUP' result from dual;

But now i want catch exception on each index, like

 EXCEPTION
         WHEN DUP_VAL_ON_INDEX1
            THEN OPEN cur FOR SELECT 'DUP1' result from dual; 
         WHEN DUP_VAL_ON_INDEX2
            THEN OPEN cur FOR SELECT 'DUP2' result from dual;

So how can i do that ?

Upvotes: 1

Views: 592

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

You could get the index_name from user_constraints by parsing the error message SQLERRM

WHEN DUP_VAL_ON_INDEX 
THEN
v_errm := SQLERRM;
SELECT INDEX_NAME into v_INDEX_NAME 
FROM user_constraints WHERE INDEX_OWNER||'.'||constraint_name = 
REGEXP_SUBSTR(v_errm,'unique constraint +\((.+)\) +violated',1,1,'i',1);

CASE  v_INDEX_NAME 
     WHEN 'INDEX1' THEN OPEN cur FOR SELECT 'DUP1' result from dual; 
     WHEN 'INDEX2' THEN OPEN cur FOR SELECT 'DUP2' result from dual;
     ELSE DBMS_OUTPUT.PUT_LINE(SQLERRM);
END CASE;

Do note that you may also have to handle NO_DATA_FOUND by putting the select statement inside another BEGIN..EXCEPTION..END or using other methods. But, this would not be required if your user_constraints has the correct details of the raised constraint.

Upvotes: 3

Related Questions