IS_NUMBER()

If we you are storing NUMBER and character in a VARCHAR2 column and you want to validate if the value is number or not you can use the following regex based SQL.

SELECT 
 nvl(regexp_substr(:test_number,
     '^(\+|-)?([0-9])*(.)?([eE])?(\+|-)?([0-9])+$'),              
    'Not a number') 
FROM   DUAL 

You can read more about it in asktom