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.
What is the difficulty level of this exercise?
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
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics