Oracle Tips and Techniques

HTML clipboardCause: Unique Index constraint violated during=
insert or update operation.

Explanations:
This is a system defiend exception in PL/SQL – the Oracle programming langu=
age. These=20
exceptions are
raised when a certain conditions are met – in this case when the unique con=
straint=20
condition is violated.

Suppose you have the EMP table as

CREATE TABLE emp(
empno&nbsp=3B&nbsp=3B&nbsp=3B NUMBER(5)=2C
ename&nbsp=3B&nbsp=3B&nbsp=3B VARCHAR2(40)=2C
age NUMBER(3)=2C
dob&nbsp=3B&nbsp=3B&nbsp=3B DATE
)=3B

There is an unique index on the table’s empno column defiend as:

CREATE UNIQUE INDEX emp_ui1 on emp (empno)

Now if you try to run this script

set serverout on
declare
begin
insert into emp values (11=2C ‘Tom D’=2C 40=2C sysdate)=3B
insert into emp values (11=2C ‘Tom D’=2C 40=2C sysdate)=3B
exception when DUP_VAL_ON_INDEX THEN
dbms_output.put_line(‘DUP_VAL_ON_INDEX error**************’)=3B
end=3B
/

You will see that the output as

DUP_VAL_ON_INDEX error**************

SQL&gt=3B
Hotmail: Trusted email with power=
ful SPAM protection. Sign up now.
=