Thanks for the question, Fabian.

Asked: October 07, 2011 - 3:47 am UTC

Last updated: November 04, 2019 - 1:19 pm UTC

Version: 10.2.0.5

Viewed 10K+ times! This question is

You Asked

Hallo!
in PL/sQL is it possible to get name of index on which we get DUP_VAL_ON_INDEX exception?
Regards,

and Tom said...

You can get the constraint that was violated

ops$tkyte%ORA11GR2> create table t ( x int constraint t_pk primary key, y int constraint y_unique unique );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t values ( 1, 1 );

1 row created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> begin
  2          insert into t values ( 1, 0 );
  3  exception
  4          when dup_val_on_index then
  5                  dbms_output.put_line( substr( sqlerrm, instr(sqlerrm, '(')+1,
  6                                    instr( sqlerrm, ')') - instr( sqlerrm,'(') -1 )        );
  7  end;
  8  /
OPS$TKYTE.T_PK

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> begin
  2          insert into t values ( 0, 1 );
  3  exception
  4          when dup_val_on_index then
  5                  dbms_output.put_line( substr( sqlerrm, instr(sqlerrm, '(')+1,
  6                                    instr( sqlerrm, ')') - instr( sqlerrm,'(') -1 )        );
  7  end;
  8  /
OPS$TKYTE.Y_UNIQUE

PL/SQL procedure successfully completed.

Typically the constraint name IS the index name - but it doesn't have to be. You can query user_constraints to get the index_owner/index_name if necessary.

If you just have a unique index and no unique constraint - then this will already be the index name for sure.

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

thank you

Fabian, December 20, 2011 - 3:04 am UTC

ok, that is the way I was using (with dbms_utility instead of sqlerrm) but thought about something more internal. Your answer means to me -- do not waste the time for further investigations, So thank you.

If you are logged in via Hungarian this solution does not work.

Kosala De Silva, November 04, 2019 - 11:22 am UTC

Hi,
I have used the same code and it works fine for English. But when I log in from Hungarian then i cannot get the correct constraint name.
The reason is the error message contains extra brackets.

e.g.: hu: ORA-00001: a(z) (INVENTORY_PARTPK) egyediségre vonatkozó megszorítás nem teljesül

I understand that I can search through the string to find the correct name. But I want to check if there is more sophisticated way to do that.
Thank You,
Kosala

November 04, 2019 - 1:19 pm UTC

Interesting, I didn't know that!

The forth argument of instr allows you to find the Nth occurrence of the string. So you could look for the second instance of parentheses:

with errmsg as (
  select 'ORA-00001: a(z) (CHRIS.SYS_C009827) egyediségre vonatkozó megszorítás nem teljesül' e
  from   dual
)
  select substr( e, instr(e, '(', 1, 2)+1,
           instr( e, ')', 1, 2) - instr( e,'(', 1, 2) -1 ) c
  from   errmsg;
  
C                   
CHRIS.SYS_C009827

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

thank you

If you are logged in via Hungarian this solution does not work.

More to Explore

PL/SQL demos

PL/SQL docs

catching DUP_VAL_ON_INDEX

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

thank you

If you are logged in via Hungarian this solution does not work.

More to Explore

PL/SQL demos

PL/SQL docs