Handling VALUE_ERROR exception in PL/SQL: Code Example

PL/SQL Exception Handling: Exercise-6 with Solution

Handle the VALUE_ERROR exception when assigning an incompatible value to a variable.

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

PL/SQL Code:

DECLARE
v_employee_id NUMBER;
v_incompatible_valueVARCHAR2(25);
v_result NUMBER;
  BEGIN
    SELECT first_name
    INTO v_incompatible_value
    FROM employees
    WHERE employee_id = 105; 
     BEGIN
v_employee_id := TO_NUMBER(v_incompatible_value);
      DBMS_OUTPUT.PUT_LINE('Assignment successful.');
v_result := v_incompatible_value; 
      DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
    EXCEPTION
      WHEN VALUE_ERROR THEN
        DBMS_OUTPUT.PUT_LINE('Error: VALUE_ERROR exception message:');
        DBMS_OUTPUT.PUT_LINE('Incompatible value assignment.');
     END;
END;
/

Sample Output:

Error: VALUE_ERROR exception message:
Incompatible value assignment.

Explanation:

The said code in Oracle's PL/SQL demonstrates how to handle a VALUE_ERROR exception that may occur when attempting to convert a string value to a number.

Three variables v_employee_id of type NUMBER, v_incompatible_value of type VARCHAR2, and v_result of type NUMBER are declared.

A SELECT statement retrieves the first_name from the 'employees' table where employee_id is 105. The fetched value is stored in the v_incompatible_value variable.

Within the nested block, the code attempts to convert v_incompatible_value to a NUMBER using the TO_NUMBER function and assigns the result to v_employee_id. If the conversion is successful, it outputs a message indicating the successful assignment.

The code then try to assigns v_incompatible_value to v_result and if a VALUE_ERROR exception occurs during the conversion process, the exception block is triggered and outputs an error message indicating the VALUE_ERROR exception and notifies about the incompatible value assignment.

Flowchart:

Previous: Handling DUP_VAL_ON_INDEX exception in PL/SQL.
Next: PL/SQL Program Handling Internal Error.