如何避免Oracle查询中JSON日期转换引发的ORA错误？

无需编写PL/SQL函数，有两种方法可解决日期格式错误导致的报错问题：

方法1：用VALIDATE_CONVERSION判断格式合法性 #

通过Oracle的VALIDATE_CONVERSION函数先校验JSON提取的字符串是否符合目标时间格式，合法则转换，否则返回NULL：

SELECT * 
FROM FUNDING_REQUEST f
ORDER BY 
  CASE 
    WHEN VALIDATE_CONVERSION(
      JSON_VALUE(f.REQUEST_CONTENTS, '$.leasing_information.consumer_request_date_time')
      AS TIMESTAMP WITH TIME ZONE 
      FORMAT 'YYYY/MM/DD"T"HH24:MI:SS.FFTZH:TZM'
    ) = 1 
    THEN TO_TIMESTAMP_TZ(
      JSON_VALUE(f.REQUEST_CONTENTS, '$.leasing_information.consumer_request_date_time'),
      'YYYY/MM/DD"T"HH24:MI:SS.FFTZH:TZM'
    )
    ELSE NULL 
  END ASC;

VALIDATE_CONVERSION返回1表示格式合法，0表示不合法，以此避免转换报错。

方法2：正确使用TO_TIMESTAMP_TZ的错误处理（Oracle 12cR2+） #

Oracle 12cR2及以上版本支持在TO_TIMESTAMP_TZ中直接添加DEFAULT ... ON CONVERSION ERROR子句，你之前的错误是将该子句放错了位置，正确写法如下：

SELECT * 
FROM FUNDING_REQUEST f
ORDER BY 
  TO_TIMESTAMP_TZ(
    JSON_VALUE(f.REQUEST_CONTENTS, '$.leasing_information.consumer_request_date_time'),
    'YYYY/MM/DD"T"HH24:MI:SS.FFTZH:TZM'
    DEFAULT NULL ON CONVERSION ERROR
  ) ASC;

该子句需放在格式模型参数之后，作为TO_TIMESTAMP_TZ函数的一部分，格式错误时会自动返回NULL。

内容的提问来源于stack exchange，提问作者Natty