Handling DUP_VAL_ON_INDEX exception in PL/SQL

PL/SQL Exception Handling: Exercise-5 with Solution

Handle the DUP_VAL_ON_INDEX exception when inserting a duplicate value into a table with a unique constraint.

Sample Solution:

Table: employees

employee_id		integer
first_name		varchar(25)
last_name		varchar(25)
email			archar(25)
phone_number		varchar(15)
hire_date		date
job_id			varchar(25)
salary			integer
commission_pct		decimal(5,2)
manager_id		integer
department_id		integer

Example-1

PL/SQL Code:

DECLARE
v_duplicate_value NUMBER := 125;
BEGIN
  BEGIN
    INSERT INTO employees (employee_id) VALUES (v_duplicate_value);
    DBMS_OUTPUT.PUT_LINE('Insertion successful.');
  EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
      -- Handle the exception
      DBMS_OUTPUT.PUT_LINE('Error: Duplicate value already exists.');
      -- You can perform additional error handling or logging here
  END;
END;
/

Sample Output:

Error: Duplicate value already exists.

Explanation:

The said code in Oracle's PL/SQL demonstrates to handle the scenario where a duplicate value is being inserted into a table with a unique constraint and by catching the DUP_VAL_ON_INDEX exception an appropriate error handling message is displayed.

A variable v_duplicate_value is declared and assigned a value of 125, representing a duplicate value that is being attempted to be inserted.

The nested BEGIN...EXCEPTION...END block that performs to insert the value of v_duplicate_value into the "employee_id" column in 'employees' table.

If the insertion is successful, the code prints a successful message and however, if fails due to a violation of the unique constraint, the DUP_VAL_ON_INDEX exception is raised, and the inner EXCEPTION block is triggered and an error message that is, a duplicate value already exists is printed to the console.

Flowchart:

Table: departments

department_id			integer
department_name			varchar(25)
manager_id			integer
location_id			integer

Example-2

PL/SQL Code:

DECLARE
v_department_iddepartments.department_id%TYPE := 90;
v_department_namedepartments.department_name%TYPE := 'Research';
v_manager_idemployees.employee_id%TYPE := 110;
v_location_idlocations.location_id%TYPE := 1700;
BEGIN
  INSERT INTO departments (department_id, department_name, manager_id, location_id)
  VALUES (v_department_id, v_department_name, v_manager_id, v_location_id);

EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    DBMS_OUTPUT.PUT_LINE('Caught DUP_VAL_ON_INDEX exception: Violation of a unique key constraint.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END;

Sample Output:

Caught DUP_VAL_ON_INDEX exception: Violation of a unique key constraint.

Explanation:

The said code in Oracle's PL/SQL that demonstrates how to handle the DUP_VAL_ON_INDEX exception specifically and provides a mechanism for handling other exceptions that might occur during the execution of the INSERT statement.

The variables v_department_id, v_department_name, v_manager_id, and v_location_id are declared. These variables hold the values that will be inserted into the departments table.

The INSERT statement is executes to insert a new record into the departments table using the declared variables.

If the INSERT statement violates a unique key constraint, specifically the duplication of a value in an indexed column, the DUP_VAL_ON_INDEX exception is raised and the corresponding exception handler provides the message indicating the violation of the unique key constraint.

If the exception handler catches any other exceptions that might occur during the execution of the block it outputs a general error message that includes the error information obtained using the SQLERRM function.

Flowchart:

Previous: Handling invalid number exception in PL/SQL.
Next: Handling VALUE_ERROR exception in PL/SQL: Code Example.