当我在查询中使用一个 where 子句时，会出现ORA-01858错误；而如果将查询输出放在WHERE子句中则可以正常工作。

查询#1：

SELECT DISTINCT 
    TO_CHAR(p.nm_snapshot_table) 
FROM 
    TABLE_4 p 
JOIN 
    TABLE_5 j ON (j.nm_job = p.nm_snapshot_table AND j.f_active = 'Y');

输出：

'TABLE_1'  
'TABLE_2'  
'TABLE_3'

查询#2：

WITH partition_dates AS 
(
    SELECT 
        utp.table_name,
        utp.partition_name,
        utp.partition_position,
        TO_DATE(
            TRIM('''' FROM REGEXP_SUBSTR(
                EXTRACTVALUE(
                    DBMS_XMLGEN.GETXMLTYPE(
                        'SELECT high_value FROM dba_tab_partitions ' ||
                        'WHERE table_name = ''' || utp.table_name || ''' ' ||
                        'AND partition_name = ''' || utp.partition_name || ''' ' ||
                        'AND table_owner = ''' || utp.table_owner || ''''
                    ),
                    '//text()'
                ),
                '''.*?'''
            )),
            'SYYYY-MM-DD HH24:MI:SS'
        ) - 1 AS partition_date
    FROM 
        dba_tab_partitions utp
    WHERE 
        utp.table_name IN ('TABLE_1', 'TABLE_2', 'TABLE_3')
        AND utp.partition_name NOT LIKE '%OLD'
        AND utp.table_owner = 'SCHEMA_1'
)
SELECT 
    table_name,
    COUNT(*) as partition_count,
    TO_CHAR(MIN(partition_date), 'YYYY-MM-DD') as oldest_partition,
    TO_CHAR(MAX(partition_date), 'YYYY-MM-DD') as newest_partition
FROM 
    partition_dates
WHERE 
    partition_date IS NOT NULL
GROUP BY 
    table_name
ORDER BY 
    table_name;

即使在我的SQL Developer中选择最后一行，也能得到正确的输出。

查询#3：

WITH q AS 
(
    SELECT DISTINCT 
        TO_CHAR(p.nm_snapshot_table) 
    FROM 
        TABLE_4 p 
    JOIN 
        TABLE_5 j ON (j.nm_job = p.nm_snapshot_table AND j.f_active = 'Y')
),
partition_dates AS 
(
    SELECT 
        utp.table_name,
        utp.partition_name,
        utp.partition_position,
        TO_DATE(
            TRIM('''' FROM REGEXP_SUBSTR(
                EXTRACTVALUE(
                    DBMS_XMLGEN.GETXMLTYPE(
                        'SELECT high_value FROM dba_tab_partitions ' ||
                        'WHERE table_name = ''' || utp.table_name || ''' ' ||
                        'AND partition_name = ''' || utp.partition_name || ''' ' ||
                        'AND table_owner = ''' || utp.table_owner || ''''
                    ),
                    '//text()'
                ),
                '''.*?'''
            )),
            'SYYYY-MM-DD HH24:MI:SS'
        ) - 1 AS partition_date
    FROM 
        dba_tab_partitions utp
    WHERE 
        utp.table_name IN (SELECT * FROM q)
        AND utp.partition_name NOT LIKE '%OLD'
        AND utp.table_owner = 'SCHEMA_1'
)
SELECT 
    table_name,
    COUNT(*) as partition_count,
    TO_CHAR(MIN(partition_date), 'YYYY-MM-DD') as oldest_partition,
    TO_CHAR(MAX(partition_date), 'YYYY-MM-DD') as newest_partition
FROM 
    partition_dates
WHERE 
    partition_date IS NOT NULL
GROUP BY 
    table_name
ORDER BY 
    table_name;

这个查询返回一个错误：

ORA-01858：在需要数字的位置发现了非数字字符

1. 00000 - "在需要数字的位置发现了非数字字符"

*Cause: 要使用日期格式模型进行转换的输入数据不正确。输入数据在格式模型要求的位置没有包含数字。

*Action: 修正输入数据或日期格式模型，确保元素在数量和类型上匹配。然后重试该操作。

解决方案

该查询没有使用类型安全的数据，当优化器对查询进行转换时，可能以意外的顺序执行，从而把错误的日期通过 TO_DATE 函数。为避免此错误，最安全的方法是使用 DEFAULT NULL ON CONVERSION ERROR 子句，如下所示：

SELECT TO_DATE('ASDF' DEFAULT NULL ON CONVERSION ERROR, 'YYYY-MM-DD')
FROM DUAL;

使用这种动态 DBMS_XMLGEN.GETXMLTYPE 技巧的查询通常也会变慢，因为优化器无法准确预测XML处理的基数和耗时。你也许可以通过一种更高风险的解决方案来同时解决性能和类型转换错误，即添加一个 ROWNUM 虚拟列来防止转换。大致如下：

WITH q AS 
(
    ...
    -- Prevent optimizer transformations:
    WHERE ROWNUM >= 1
),
...