原文作者：Jonathan lewis
原文地址：https://jonathanlewis.wordpress.com/2020/04/24/conversion-errors/

Conversion Errors

— Jonathan Lewis @ 11:03 am BST Apr 24,2020
我想写这篇文章已经有至少三年了，今天早上我又写了一篇草稿，这是昨天的一篇关于将to_date()函数应用到日期列的危险性的文章的后续文章。但随后我看了看Oracle开发人员论坛上最近的问题，发现蒂姆·霍尔（@oraclebase）（不可避免地）已经做了必要的回复，所以我在这里留了一个简短的笔记（更多的是为了我自己的利益，而不是其他任何事情）的重点与他的网页的链接。

12.2中提供的避免转换错误的关键特性有:

• validate_conversion()函数返回1或0，具体取决于表达式是否可以使用特定的类型成功地转换为指定的类型——如果表达式的计算结果为null，则返回null。
• 扩展到通用的转换函数（例如to_date()），如果尝试的转换引起转换错误，允许使用“默认”值来替换提供的值。
  代码样本-报告行，其有效日期（法语）早于今年（英语）纳税年度的开始日期:

rem
rem     Script:         validate_conversion.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Mar 2017
rem     Purpose:
rem
rem     Last tested
rem             12.2.0.1
rem
 
create table t1 (v1 varchar2(42));
insert into t1 values('15-June-2016');
insert into t1 values('15-Juin-2016');
commit;
 
prompt  ==================================================================
prompt  Single predicate test - only rows that validate as dates in French
prompt  ==================================================================
 
select  *
from    t1
where   validate_conversion(v1 as date, 'dd-month-yyyy' , 'nls_date_language=french') = 1
/
 
prompt  ===========================================================
prompt  Valid French dates that are before 6th April 2017 (English)
prompt  ===========================================================
 
select
        *
from    t1
where
        validate_conversion(v1 as date, 'dd-month-yyyy' , 'nls_date_language=french') = 1
and     to_date(v1, 'dd-month-yyyy' , 'nls_date_language=french') < to_date('06-Apr-2017','dd-mon-yyyy')
/
 
prompt  =========================================
prompt  Repeat the above with reversed predicates
prompt  =========================================
 
select
        *
from    t1
where
        to_date(v1, 'dd-month-yyyy' , 'nls_date_language=french') < to_date('06-Apr-2017','dd-mon-yyyy')
and     validate_conversion(v1 as date, 'dd-month-yyyy' , 'nls_date_language=french') = 1
/
 
prompt  ====================================================
prompt  Repeat the above but force the order of evaluation
prompt  This will raise error ORA-01843: not a invalid month
prompt  ====================================================
 
select
        /*+ ordered_predicates */
        *
from    t1
where
        to_date(v1, 'dd-month-yyyy' , 'nls_date_language=french') < to_date('06-Apr-2017','dd-mon-yyyy')
and     validate_conversion(v1 as date, 'dd-month-yyyy' , 'nls_date_language=french') = 1
/
 
prompt  ========================================================================
prompt  Handle the requirement with the 12.2 extended "to_date()" functionality
prompt  ========================================================================
 
select
        *
from
        t1
where
        to_date(
                v1 default '06-Avril-2017' on conversion error,
                'dd-month-yyyy',
                'nls_date_language=french'
        ) < to_date(
                '06-Apr-2017',
                'dd-mon-yyyy',
                'nls_date_language=English'
        )
/
复制

是否可以保证总是在一个调用尝试在谓词中使用转换之前调用validate_conversion()函数。我对此表示怀疑，因为在手册中没有任何指示，而且Oracle也没有关于谓词执行顺序的一般保证，所以我不会冒这个险。也许唯一安全的用法是用一个CASE表达式（一个捷径）加上一个“when successful then”子句来评估实际的转换。要记住正确地处理空值，您需要多加注意。
也许可以用一个不可合并的内联视图替换该表，从而消除失败？可能不会，优化器可能仍然会执行一个简单的过滤器下推。