Validating Dates and Numbers in Oracle 12.2

Prior to 12.2 validating dates and numbers was a bit of a pain as you had to write your own custom PL/SQL function. OOS-Utils has a quick solution to this issue and I recommend using oos_util_validation.is_number and oos_util_validation.is_date.

If you’re using Oracle 12.2 or above you can (and should) use validation_conversion instead. Here are some examples of how to use it along with results:

1
2
3
4
5
6
7
8
9
10
11
select
  validate_conversion('123.34' as number, '999.00') valid_number,
  validate_conversion('abc' as number) invalid_number,
  validate_conversion('01-Jan-19' as date, 'DD-MON-YYYY') valid_date,
  validate_conversion('01-BAD-19' as date, 'DD-MON-YYYY') invalid_date
from dual
;

VALID_NUMBER INVALID_NUMBER VALID_DATE INVALID_DATE
------------ -------------- ---------- ------------
           1              0          1            0

validate_conversion will return 1 for valid conversions and 0 for invalid conversions.

Note: in the next release of OOS Utils, the validation functions will use validate_conversion if your database is 12.2 or older internally.