The Oracle Validate_Conversion Function

In this article, I will talk about Oracle 12c Release 2 with a new feature on the SQL side. 

Let's start with describing the validate_conversion function. We often encounter data type conversion problems in SQL and PL/SQL code that we write. When we encounter such situations, we often crash and end up in an uncontrolled situation. The validate_conversion function is running at the point where we have problems with data type conversions. The logic of the function is actually quite simple. We send two parameters to the function. The first one is the data to be converted, and the other is the type of data to be converted. The function returns 1 or 0 as a result of the given parameters. If there is no discrepancy between the data and the data type, 1 is returned. 0 is returned if there is an inconsistency between the data and the data type.

We can use the validate_conversion function to test the following data type conversions:

• Number.

• Date.

• Timestamp.

• Timestamp with time zone.

• Binary_float.

• Binary_double.

• Interval day to second.

• Interval year to month.

Now let's see some examples of how it is used.

select validate_conversion('test123' as number)                 test1 from dual;--0 
select validate_conversion(300 as number)                       test2 from dual;--1
select validate_conversion('01012017' as date,'mmddyyyy')       test3 from dual;--1
select validate_conversion('31012017' as date,'mmddyyyy')       test4 from dual;--0
select validate_conversion('31-jan-2017' as date,'dd-mon-yyyy') test5 from dual;--1

Now, let's look at different forms of use.

SELECT *
FROM hr.employees emp
WHERE VALIDATE_CONVERSION(emp.employee_id AS NUMBER) = 1;

SELECT VALIDATE_CONVERSION(emp.employee_id AS NUMBER) emp_id_test,
employee_id
FROM hr.employees emp;

SELECT DECODE(VALIDATE_CONVERSION(emp.employee_id AS NUMBER),0,
'NOT NUMBER',1,'NUMBER','N/A') emp_id_test,employee_id
FROM hr.employees emp;

SELECT case when VALIDATE_CONVERSION(emp.employee_id AS NUMBER) = 1 
then 
emp.employee_id else -1 end emp_id_test,
employee_id
FROM hr.employees emp;

This feature, which comes with Oracle 12c R2, will reduce the complexity of the code we write for data validation in SQL and PL/SQL code blocks and improve our application performance. Native code support for data conversion is a significant development in this context.