Nov 24, 2016

Oracle Database 12c Release 2 (12.2) Conversion Function Enhancements


Oracle Database 12c Release 2 (12.2) includes a number of enhancements to datatype conversion functions, making it pretty easier to handle conversion errors.






Setup

The following table is used by the examples in this article.

CREATE TABLE t1 (
  data VARCHAR2(20)
);

INSERT INTO t1 VALUES ('11111');
INSERT INTO t1 VALUES ('01-JAN-2016');
INSERT INTO t1 VALUES ('AAAAA');
COMMIT;


CAST and TO_* Conversion Functions

In previous database versions failure during data type conversions resulted in an error.

SELECT TO_NUMBER(data)
FROM   t1;
ERROR:
ORA-01722: invalid number

no rows selected

SQL>

In Oracle database 12.2 the CAST function and several of the TO_* functions have been amended to include error handling functionality, allowing them to return a default value in the event of a conversion error.

SELECT TO_NUMBER(data DEFAULT -1 ON CONVERSION ERROR)
FROM   t1;
                 *
TO_NUMBER(DATADEFAULT-1ONCONVERSIONERROR)
-----------------------------------------
                                    11111
                                       -1
                                       -1

SQL>


SELECT TO_DATE(data DEFAULT '01-JAN-2000' ON CONVERSION ERROR, 'DD-MON-YYYY' )
FROM   t1;

TO_DATE(D
---------
01-JAN-00
01-JAN-16
01-JAN-00

SQL>


SELECT CAST(data AS TIMESTAMP DEFAULT NULL ON CONVERSION ERROR, 'DD-MON-YYYY')
FROM   t1;

CAST(DATAASTIMESTAMPDEFAULTNULLONCONVERSIONERROR,'DD-MON-YYYY')
---------------------------------------------------------------------------

01-JAN-16 12.00.00.000000000 AM


SQL>


VALIDATE_CONVERSION Function

The VALIDATE_CONVERSION function is used to test if a conversion will be successful, making it possible to exclude data that would cause a problem during an operation. The function returns the value "1" if the conversion is successful and "0" if it fails.

SELECT data
FROM   t1
WHERE  VALIDATE_CONVERSION(data AS NUMBER) = 1;

DATA
--------------------
11111

SQL>



SELECT data
FROM   t1
WHERE  VALIDATE_CONVERSION(data AS DATE, 'DD-MON-YYYY') = 1;

DATA
--------------------
01-JAN-2016

SQL>



Post a Comment

 
TECH SUPPORT © 2012 - Designed by INFOSBIRD