Handling invalid number exception in PL/SQL

PL/SQL Exception Handling: Exercise-4 with Solution

Handle the INVALID_NUMBER exception when converting a non-numeric value to a number.

Sample Solution:

PL/SQL Code:

DECLARE
  v_input VARCHAR2(10) := 'abc';
v_number NUMBER;
BEGIN
  BEGIN
    v_number := TO_NUMBER(v_input);
    DBMS_OUTPUT.PUT_LINE('Conversion successful. Number: ' || v_number);
  EXCEPTION
    WHEN INVALID_NUMBER THEN
      DBMS_OUTPUT.PUT_LINE('Error: Invalid number');
  END;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

Sample Output:

Error: ORA-06502: PL/SQL: numeric or value error: character to number conversion error

Explanation:

The said code in Oracle's PL/SQL demonstrates the usage of exception handling INVALID_NUMBER to gracefully handle the scenario where a non-numeric value is encountered during a conversion operation and provide an appropriate feedback or take additional error handling actions as required.

A variable v_input is declared and initialized with the value 'abc', which is a non-numeric string.

A nested BEGIN...EXCEPTION...END block that attempts to convert v_input to a number using the TO_NUMBER function and assigns the result to the v_number variable.

If the conversion is successful, the code prints a message to the console indicating that the conversion was successful, along with the converted number and if fails due to an invalid number, the INVALID_NUMBER exception is raised, and the inner EXCEPTION block executes.

Within the exception block, an error message is displays to the console, indicating that an invalid number was encountered.

The outer EXCEPTION WHEN OTHERS block executes when catch any other unhandled exceptions that might occur during the execution of the code. In such cases, the error message, obtained from SQLERRM, is printed to the console.

Flowchart:

Previous: Handling TOO_MANY_ROWS Exception in PL/SQL.
Next: Handling DUP_VAL_ON_INDEX exception in PL/SQL.