How can you tell if a value is not numeric in Oracle?

Question

I have the following code that returns an error message if my value is invalid. I would like to give the same error message if the value given is not numeric.

IF(option_id = 0021) THEN        IF((value<10000) or (value>7200000) or /* Numeric Check */)THEN           ip_msg(6214,option_name);  -- Error Message           return;       END IF; END IF;       

In SQL Server, I simply used ISNUMERIC(). I would like to do something similar in Oracle. Such as,

IF((!ISNUMERIC(value)) or (value<10000) or (value>7200000))     THEN ... 

Answer 1

REGEXP_LIKE(column, '^[[:digit:]]+$') 

returns TRUE if column holds only numeric characters

Answer 2

From Oracle DB 12c Release 2 you could use VALIDATE_CONVERSION function:

VALIDATE_CONVERSION determines whether expr can be converted to the specified data type. If expr can be successfully converted, then this function returns 1; otherwise, this function returns 0. If expr evaluates to null, then this function returns 1. If an error occurs while evaluating expr, then this function returns the error.

 IF (VALIDATE_CONVERSION(value AS NUMBER) = 1) THEN      ...  END IF; 

db<>fiddle demo