Controlled Data Type Conversion With the Cast Function

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

Cast function is an SQL function that transforms between data types, as is known by a majority. This function can convert from built-in or collection-typed data types to other built-in or collection-typed data types. Briefly, let's remember how it works.

-- EXAMPLE
SELECT CAST ('123456' AS NUMBER) + 5 FROM DUAL;

CAST('123456'ASNUMBER)+5
------------------------
                  123461

-- EXAMPLE
SELECT CAST ('10.10.2017' AS DATE) + 5 FROM DUAL;

CAST('10.10.2017'ASDATE)+5
--------------------------
15/10/2017   

-- EXAMPLE
SELECT CAST (SYSDATE AS TIMESTAMP) FROM DUAL;

CAST(SYSDATEASTIMESTAMP)                          
--------------------------------------------------
14/2/2017 08:21:59,000000  

We remembered how the function worked, but when the data types that could not be converted to CAST function before Oracle 12c R2 were sent, the function was getting an error and the program was terminated.

-- EXAMPLE
SELECT CAST ('sdfsj343' AS NUMBER) FROM DUAL; 
--> ERROR : ORA-01722: invalid number

-- EXAMPLE
SELECT CAST ('7878987' AS DATE) castdate FROM DUAL;
--> ERROR : ORA-01847: day of month must be between 1 and last day of month

These kinds of mistakes are the types of errors that we encounter in our codes according to the amount of data we process. Especially in systems where Data Quality problems exist, we often encounter such problems. When using this function with Oracle 12c R2, a mechanism is added to handle the problems that will arise as a result of possible data type mismatches. Along with this mechanism, a new option has been introduced to reduce or completely eliminate errors in type conversions. Let's see how it works now:

-- EXAMPLE
SELECT CAST ('fsweereg' AS DATE default sysdate on conversion error)
castdate1 from dual;

-- EXAMPLE
SELECT CAST ('fsweereg' AS DATE default '10-10-2017' on 
conversion error, 'DD-MM-YYYY') castdate2 from dual;

-- EXAMPLE
SELECT CAST ('fsdfs34' AS NUMBER default -99 on conversion error) 
castnumber from dual;

As can be seen from the examples, we can define the default values in a wrong conversion, so that the function continues in a controlled way. The CONVERSION ERROR clause that comes with Oracle 12c R2 can also be used in other casting functions such as to_number and to_date.

-- EXAMPLE
select to_number('32423fsfs'  default -99 on conversion error)
castdate from dual;

-- EXAMPLE
select to_date('32423fsfs'  default sysdate on conversion error)
castdate from dual;

-- EXAMPLE
select to_date('32423fsfs'  default '10-10-2017' on conversion error
, 'DD-MM-YYYY') castdate from dual;

I believe that this feature will reduce the complexity of the code we write for data type conversions within SQL and PL/SQL code blocks and increase our application performance at this point. Native code support for controlling data type conversion is a very important development in this context.