w3resource

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:

Flowchart: PL/SQL Exception Handling Exercises - Handling VALUE_ERROR exception in PL/SQL: Code Example

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

What is the difficulty level of this exercise?



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/plsql-exercises/exception-handling/plsql-exception-handling-exercise-6.php