5 Replies Latest reply on Mar 20, 2010 10:30 PM by MichaelS

    specific duplicate exception error number

    user4900730
      If I get a DUPS_ON_INDEX exception on a table that has a PK and a secondary unique index is there any way to find out which index caused the dups? Is there two distinct SQL error numbers or is there any other way?

      Edited by: user4900730 on Mar 19, 2010 9:26 PM
        • 1. Re: specific duplicate exception error number
          fsitja
          The message itself will tell you which constraint was violated. To verify it you can query the view USER_INDEXES (or ALL_INDEXES).
          Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 
          Connected as fsitja
           
          SQL> 
          SQL> alter session set nls_language = 'ENGLISH';
           
          Session altered
          SQL> create table t1 (pk_col number(8) primary key, uk_col varchar2(8), index_col number(8));
           
          Table created
          SQL> alter table t1 add constraint uk_col_cons unique (uk_col);
           
          Table altered
          SQL> create unique index ix_unique_index_col on t1 (index_col);
           
          Index created
          SQL> insert into t1 (pk_col, uk_col, index_col) values (1, 'A', 100);
           
          1 row inserted
          SQL> insert into t1 (pk_col, uk_col, index_col) values (1, 'XX', 200);
           
          insert into t1 (pk_col, uk_col, index_col) values (1, 'XX', 200)
           
          ORA-00001: unique constraint (FSITJA.SYS_C0011844) violated
          SQL> insert into t1 (pk_col, uk_col, index_col) values (20, 'BOO', 100);
           
          insert into t1 (pk_col, uk_col, index_col) values (20, 'BOO', 100)
           
          ORA-00001: unique constraint (FSITJA.IX_UNIQUE_INDEX_COL) violated
          SQL> insert into t1 (pk_col, uk_col, index_col) values (60, 'A', 3000);
           
          insert into t1 (pk_col, uk_col, index_col) values (60, 'A', 3000)
           
          ORA-00001: unique constraint (FSITJA.UK_COL_CONS) violated
          SQL> select * from t1;
           
             PK_COL UK_COL   INDEX_COL
          --------- -------- ---------
                  1 A              100
           
          SQL> 
          SQL> select u.INDEX_NAME, u.TABLE_OWNER, u.TABLE_NAME, u.TABLE_TYPE, u.UNIQUENESS
            2    from user_indexes u
            3   where u.index_name = 'SYS_C0011842'
            4         and uniqueness = 'UNIQUE';
           
          INDEX_NAME                     TABLE_OWNER                    TABLE_NAME                     TABLE_TYPE  UNIQUENESS
          ------------------------------ ------------------------------ ------------------------------ ----------- ----------
           
          SQL> 
          • 2. Re: specific duplicate exception error number
            user4900730
            Thanks for your response.

            My question was when I am in a PL/SQL proc and I detect the exception via a WHEN DUPS_ON_INDEX how can I now which unique constraint caused the violation, Your response showed ORA error 1 in all the cases so I cannot distinguish between the possible causes of the dups. Here is a skeleton of the PL/SQL code

            INSERT INTO table_x .....

            EXCEPTION
            WHEN DUPS_ON_INDEX
            I need to find out here which unique constraint caused the violation
            • 3. Re: specific duplicate exception error number
              MichaelS
              how can I now which unique constraint caused the violatio
              as fsitja said: check the error message itself:
              SQL> create table t (a int constraint t_pk primary key , b int constraint t_uk unique)
              /
              Table created.
              
              SQL> begin
                 insert into t
                 values (1, 1);
              exception
                 when dup_val_on_index
                 then
                    dbms_output.put_line (sqlerrm);
              end;
              /
              PL/SQL procedure successfully completed.
              
              SQL> begin
                 insert into t
                 values (2, 1);
              exception
                 when dup_val_on_index
                 then
                    dbms_output.put_line (sqlerrm);
              end;
              /
              ORA-00001: unique constraint (MICHAEL.T_UK) violated
              PL/SQL procedure successfully completed.
              
              SQL> begin
                 insert into t
                 values (1, 2);
              exception
                 when dup_val_on_index
                 then
                    dbms_output.put_line (sqlerrm);
              end;
              /
              ORA-00001: unique constraint (MICHAEL.T_PK) violated
              PL/SQL procedure successfully completed.
              • 4. Re: specific duplicate exception error number
                user4900730
                Thanks for responding.

                I need to make a program logic decision in the exception handler so if SQL ERR MSG is the only iformation available I would have to scan it and look for whats in the paranthesis. This is possible and not hard but the shortcoming is that I am dependent on the DBA not changing the constraint name.

                Actually I only need to distinguish between whether it is a PK violation or not so I could scan the SQL ERR MSG for the text 'PK'. but this is still dependent on a naming convention for the PK constraints.

                Any comments?
                • 5. Re: specific duplicate exception error number
                  MichaelS
                  Actually I only need to distinguish between whether it is a PK violation or not
                  You can query user_constraints for that:
                  SQL>create table t
                    2  (
                    3     a   int constraint t_pk primary key,
                    4     b   int constraint t_uk unique
                    5  )
                    6  /
                  
                  Table created.
                  
                  SQL>
                  SQL>
                  SQL>begin
                    2     insert into t
                    3     values (1, 1);
                    4  exception
                    5     when dup_val_on_index
                    6     then
                    7        dbms_output.put_line (sqlerrm);
                    8  end;
                    9  /
                  
                  PL/SQL procedure successfully completed.
                  
                  SQL>
                  SQL>begin
                    2     insert into t
                    3     values (2, 1);
                    4  exception
                    5     when dup_val_on_index
                    6     then
                    7        declare
                    8           err_msg   varchar2 (500) := sqlerrm;
                    9           c_name    varchar2 (30);
                   10           c_type    varchar2 (30);
                   11        begin
                   12           select constraint_name, decode(constraint_type, 'P', 'Primary Key', 'U', 'Unique Key') into c_name, c_type
                  
                   13             from user_constraints
                   14            where '(' || user || '.' || constraint_name || ')' =
                   15                     regexp_substr (err_msg, '\(.*\)');
                   16           dbms_output.put_line (c_type || ' ' || c_name || ' violated');
                   17        end;
                   18  end;
                   19  /
                  Unique Key T_UK violated
                  
                  PL/SQL procedure successfully completed.
                  
                  SQL>
                  SQL>begin
                    2     insert into t
                    3     values (1, 2);
                    4  exception
                    5     when dup_val_on_index
                    6     then
                    7        declare
                    8           err_msg   varchar2 (500) := sqlerrm;
                    9           c_name    varchar2 (30);
                   10           c_type    varchar2 (30);
                   11        begin
                   12           select constraint_name, decode(constraint_type, 'P', 'Primary Key', 'U', 'Unique Key') into c_name, c_type
                  
                   13             from user_constraints
                   14            where '(' || user || '.' || constraint_name || ')' =
                   15                     regexp_substr (err_msg, '\(.*\)');
                   16           dbms_output.put_line (c_type || ' ' || c_name || ' violated');
                   17        end;
                   18  end;
                   19  /
                  Primary Key T_PK violated
                  
                  PL/SQL procedure successfully completed.
                  
                  SQL>