PL/SQL Dup_val_on_index

The dup_val_on_index exception is an predefined exception of PL/SQL language and catch error when pl/sql program try to store duplicate values in a database column that is constrained by a unique index.

STUDENT_ID FIRST_NAME LAST_NAME CITY
1 Daniel SCOTT New York
2 Anthony SIMMONS Chicago
3 Sophia THOMPSON Los Angeles




Dup_val_on_index example

declare
begin
	insert into students(student_id, first_name, last_name, city)
	values(1,'Tom','PETERSON', 'Boston');
	dbms_output.put_line('Row inserted!');
exception
	when dup_val_on_index then
		dbms_output.put_line('DUP_VAL_ON_INDEX: '||sqlerrm);
		dbms_output.put_line('Solution: change value of student_id.');
end;

Output:

DUP_VAL_ON_INDEX: ORA-00001: unique constraint (SYSTEM.PK_STUDENT_ID) violated

Solution: change value of student_id.