使用VALIDATE_CONVERSION函数结合XMLTABLE操作符触发ORA-43909错误

问题背景 #

解析包含日期的XML文本时，为排查日期转换错误，尝试使用VALIDATE_CONVERSION函数筛选异常XML元素，但结合XMLTABLE操作符时触发ORA-43909: invalid input data type错误。数据库版本为Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production。

已尝试将DT字段转为VARCHAR2(20)，但问题依旧。目前临时解决方案是使用临时表，但存在使用不便及性能问题，需更优方案。

测试示例 #

正常执行的查询 #

with q_test as (
  select 1 as id, '2023-08-26' as dt from dual
  union all select 2, 'Invalid date' from dual
)
select q.*, validate_conversion(q.dt as date, 'yyyy-mm-dd')
from q_test q;

执行结果：

1   2023-08-26      1
2   Invalid date    0

执行失败的查询 #

with q_test1 as (
  select XMLTYPE('<?xml version=''1.0'' encoding=''UTF-8''?>
<TEST>
  <RECORD>
    <ID>1</ID>
    <DT>2023-08-26</DT>
  </RECORD>
  <RECORD>
    <ID>2</ID>
    <DT>Invalid date</DT>
  </RECORD>
</TEST>') xml
  from dual
),
q_test2 as (
  select cast(t1.id as number) as id, t1.dt
  from q_test1 q1,
    XMLTABLE('//TEST/RECORD' passing q1.xml
    columns
      ID    path 'ID'
      , DT  path 'DT'
    ) t1
)
select q2.*, validate_conversion(q2.dt as date, 'yyyy-mm-dd') from q_test2 q2;

执行错误：ORA-43909: invalid input data type

解决方案 #

问题根源在于XMLTABLE默认提取的字符类型为CLOB，而12cR2版本的VALIDATE_CONVERSION函数不支持直接对CLOB类型进行日期有效性验证。需在XMLTABLE的列定义中显式指定DT字段为VARCHAR2类型，而非后续转换，修改后的查询如下：

with q_test1 as (
  select XMLTYPE('<?xml version=''1.0'' encoding=''UTF-8''?>
<TEST>
  <RECORD>
    <ID>1</ID>
    <DT>2023-08-26</DT>
  </RECORD>
  <RECORD>
    <ID>2</ID>
    <DT>Invalid date</DT>
  </RECORD>
</TEST>') xml
  from dual
),
q_test2 as (
  select t1.id as id, t1.dt
  from q_test1 q1,
    XMLTABLE('//TEST/RECORD' passing q1.xml
    columns
      ID    NUMBER path 'ID'  -- 直接在此指定ID类型，避免后续cast
      , DT  VARCHAR2(20) path 'DT'  -- 显式指定VARCHAR2类型
    ) t1
)
select q2.*, validate_conversion(q2.dt as date, 'yyyy-mm-dd') from q_test2 q2;

修改后查询可正常执行，返回与第一个测试查询一致的结果。

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