I wrote the post Write Less with More – Part 8 – PL/SQL in the WITH Clause in November 2015, when the latest released Oracle version was 12.1.
In that post I explained about PL/SQL in the WITH Clause – a new 12.1 feature – and demonstrated it using the following example:
Since then Oracle 12.2 was released, and introduced a new feature that enables solving this task in a simpler way – the VALIDATE_CONVERSION function. This function gets an expression and a data type, and returns 1 if the expression can be converted to the data type and 0 if not.
Using the same setup from the original post, the requested query becomes as simple as:
> select *
from people
where general_info is not null
and validate_conversion(general_info as date, 'dd/mm/yyyy') = 1;
PERSON_ID FIRST_NAME LAST_NAME GENERAL_INFO
---------- ---------- --------------- --------------------
102 Paul McCartney 18/6/1942
202 Ella Fitzgerald 15/6/1996
203 Etta James 20/1/2012
In addition to introducing the new VALIDATE_CONVERSION function, the older CAST and some of the TO_* conversion functions have been enhanced in Oracle 12.2 and include a DEFAULT ON CONVERSION ERROR clause, so when data type conversion fails we can get some default value instead of an error.
> select p.person_id,
p.first_name,
p.last_name,
to_date(p.general_info default null on conversion error, 'dd/mm/yyyy') my_date
from people p;
PERSON_ID FIRST_NAME LAST_NAME MY_DATE
---------- ---------- --------------- ----------
101 John Lennon
102 Paul McCartney 18/06/1942
103 Ringo Starr
104 George Harisson
201 Louis Armstrong
202 Ella Fitzgerald 15/06/1996
203 Etta James 20/01/2012
317 Julie Andrews
8 rows selected.
Thanks for the hint.