原文地址：https://oracle-base.com/articles/12c/conversion-function-enhancements-12cr2

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

Oracle Database 12c Release 2（12.2）对数据类型转换功能进行了许多增强，使处理转换错误变得更加容易。

配置

本文中的示例使用了下表。

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和TO_*转换函数

在以前的数据库版本中，数据类型转换期间的失败导致错误。

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

no rows selected

SQL>

在Oracle数据库12.2中，CAST函数和几个TO_*函数被修改为包含错误处理功能，允许它们在发生转换错误时返回默认值。

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函数

VALIDATE_CONVERSION函数用于测试转换是否成功，从而可以排除在操作期间可能导致问题的数据。如果转换成功，则函数返回值“1”，如果转换失败，则返回值“0”。

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>