New function VALIDATE_CONVERSION has been introduced in Oracle Database Release 12.2.It determines wether a given expression can be converted to the requested data type. It can return:

• 1 – if conversion is possible or expression evaluates to NULL
• 0 – if conversion is not possible
• Error is expression returns error

Example for 1

SELECT VALIDATE_CONVERSION(1000 AS BINARY_DOUBLE)
  FROM DUAL;

SELECT VALIDATE_CONVERSION(null AS BINARY_FLOAT)
  FROM DUAL;

Examples for 0

SELECT VALIDATE_CONVERSION('$29.99' AS BINARY_FLOAT)
  FROM DUAL;

Examples for error

 SELECT VALIDATE_CONVERSION(1/0 AS BINARY_FLOAT)
  FROM DUAL;

Have a fun 🙂

Tomasz