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:
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.