Using VALIDATE_CONVERSION function in 12cR2

In 12cR2, there is a new function introduced to determine that whether a given input can be converted to a given data type or not. Using this function , it’s easy to determine that whether the column values can be converted or not. If the conversion is possible, the function returs 1 else it returns 0.

Let’s see it in action. We shall start by creating a sample table.
SQL> CREATE TABLE aman_val (c_to_nbr VARCHAR2(10), c_to_char NUMBER, c_to_date CHAR(40));
INSERT INTO aman_val VALUES (‘1’, 100, ‘July 20, 1969, 20:18’);
INSERT INTO aman_val VALUES (‘A’, 200, ‘May 10, 2016, 14:18’);
INSERT INTO aman_val VALUES (‘0’, 300, ‘Janvier 31, 2015, 10:20’);
INSERT INTO aman_val VALUES (”, 400, ‘February 30, 2016, 4:18’);
Table created.

SQL>
1 row created.

SQL>
1 row created.

SQL>
1 row created.

SQL>

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL> desc aman_val
Name Null? Type
—————————————– ——– —————————-
C_TO_NBR VARCHAR2(10)
C_TO_CHAR NUMBER
C_TO_DATE CHAR(40)

SQL> select * from aman_val;

C_TO_NBR C_TO_CHAR C_TO_DATE
———- ———- —————————————-
1 100 July 20, 1969, 20:18
A 200 May 10, 2016, 14:18
0 300 Janvier 31, 2015, 10:20
400 February 30, 2016, 4:18

SQL>

select * from aman_val where validate_conversion(aman_val.c_to_nbr as NUMBER) = 0;

C_TO_NBR C_TO_CHAR C_TO_DATE
———- ———- —————————————-
A 200 May 10, 2016, 14:18

Let’s see what will happen if we convert the column values to insert them in a different table?

Create table test
as select to_number(c_to_nbr) nbr from aman_val;SQL> 2
as select to_number(c_to_nbr) nbr from aman_val
*
ERROR at line 2:
ORA-01722: invalid number

Since the conversion wasn’t possible, we receieve the error.

Let’s create a compatible table .
SQL> create table test
as select to_number(c_to_nbr) nbr from aman_val where validate_conversion(aman_val.c_to_nbr as NUMBER)=1; 2

Table created.

So in the same way, we can also check the CHAR and Date datatypes as well. Following is the list of the data types supported by this function.
Number
Date
Timestamp
Timestamp with TimeZone
Interval Day to Second
Interval Year to Month

Hope that helps.

For more information, check,
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/VALIDATE_CONVERSION.html#GUID-DC485EEB-CB6D-42EF-97AA-4487884CB2CD

Aman….