Oracle EBS视图在Denodo查询时的数值转换报错问题

我在Oracle EBS数据库中基于多表创建了视图，其中两个计算列由VARCHAR类型的attribute7和NUMBER类型的quantity组合计算而来，最终类型为NUMBER。为避免数据转换错误，我使用了TO_NUMBER(attribute7 DEFAULT 0 ON CONVERSION ERROR)语法，在Oracle本地查询视图完全正常，但通过Denodo数据虚拟化平台查询时，触发错误：

ORA-01858: a non-numeric character was found where a numeric was expected

移除这两个计算列后，Denodo查询正常；将视图转为Oracle物理表，Denodo查询也正常，但保留计算列的视图始终报错，尝试多种方法均无效，不清楚问题根源。

计算列代码如下：
第一个计算列：

DECODE (
                SIGN (expenditure_item_date - TO_DATE ('20-JUN-2010')),
                1,    DECODE (
                           attribute7,
                           NULL, DECODE (trans_source,
                                         'Payroll', quantity,
                                         'Eff_Report', quantity,
                                         'Shops', quantity,
                                         0),
                           TO_NUMBER (attribute7 DEFAULT 0 ON CONVERSION ERROR)),
                NVL (quantity, 0))

第二个计算列：

DECODE (
                SIGN (expenditure_item_date - TO_DATE ('20-JUN-2010')),
                1, NVL (quantity, 0),
                DECODE (
                    attribute7,
                    NULL, DECODE (trans_source,
                                  'Payroll', quantity,
                                  'Eff_Report', quantity,
                                  'Shops', quantity,
                                  0),
                   TO_NUMBER(attribute7 DEFAULT 0 ON CONVERSION ERROR)))

注：attribute7为VARCHAR类型，quantity为NUMBER类型。

可能原因 #

1. Denodo对Oracle新语法兼容不足：TO_NUMBER(..., DEFAULT ... ON CONVERSION ERROR)是Oracle 12cR2及以后才支持的容错转换语法，Denodo的Oracle JDBC驱动或查询解析逻辑可能未正确识别该语法，导致查询时未按Oracle的容错逻辑执行，而是直接尝试普通TO_NUMBER转换触发错误。
2. 视图列类型推导异常：Denodo解析Oracle视图元数据时，对嵌套DECODE+TO_NUMBER容错语法的列类型推导有误，进而用错误的类型处理逻辑执行查询，触发数据库端转换错误。
3. 谓词下推干扰：Denodo可能会对视图查询做谓词下推，修改了原始计算逻辑，使得TO_NUMBER的容错分支未被正确执行，直接对非数值的attribute7进行转换。

解决方案 #

方案1：用传统兼容逻辑替代容错语法 #

把TO_NUMBER(attribute7 DEFAULT 0 ON CONVERSION ERROR)替换为兼容性更好的正则判断+CASE逻辑，确保所有环境下都能正确处理非数值数据：

-- 替换后的转换逻辑
CASE 
    WHEN REGEXP_LIKE(attribute7, '^-?\d+(\.\d+)?$') THEN TO_NUMBER(attribute7)
    ELSE 0
END

修改后的第一个计算列示例：

DECODE (
                SIGN (expenditure_item_date - TO_DATE ('20-JUN-2010')),
                1,    DECODE (
                           attribute7,
                           NULL, DECODE (trans_source,
                                         'Payroll', quantity,
                                         'Eff_Report', quantity,
                                         'Shops', quantity,
                                         0),
                           CASE 
                               WHEN REGEXP_LIKE(attribute7, '^-?\d+(\.\d+)?$') THEN TO_NUMBER(attribute7)
                               ELSE 0
                           END),
                NVL (quantity, 0))

方案2：显式指定视图列类型 #

创建视图时，用CAST强制指定计算列的数值类型，避免Denodo推导错误：

CREATE OR REPLACE VIEW your_view_name AS
SELECT
    -- 其他列...
    CAST(
        DECODE (
                SIGN (expenditure_item_date - TO_DATE ('20-JUN-2010')),
                1,    DECODE (
                           attribute7,
                           NULL, DECODE (trans_source,
                                         'Payroll', quantity,
                                         'Eff_Report', quantity,
                                         'Shops', quantity,
                                         0),
                           TO_NUMBER (attribute7 DEFAULT 0 ON CONVERSION ERROR)),
                NVL (quantity, 0)
        ) AS NUMBER(18,2)
    ) AS calc_column1,
    CAST(
        DECODE (
                SIGN (expenditure_item_date - TO_DATE ('20-JUN-2010')),
                1, NVL (quantity, 0),
                DECODE (
                    attribute7,
                    NULL, DECODE (trans_source,
                                  'Payroll', quantity,
                                  'Eff_Report', quantity,
                                  'Shops', quantity,
                                  0),
                   TO_NUMBER(attribute7 DEFAULT 0 ON CONVERSION ERROR))
        ) AS NUMBER(18,2)
    ) AS calc_column2
    -- 其他列...
FROM your_tables;

NUMBER(18,2)可根据实际数据精度调整。

方案3：在Denodo层处理转换逻辑 #

在Denodo中创建基于Oracle视图的包装视图，把转换逻辑移到Denodo端执行，不依赖Oracle的容错语法：

-- Denodo视图中的转换逻辑示例
SELECT
    -- 其他列...
    CASE
        WHEN attribute7 IS NULL THEN 
            CASE trans_source
                WHEN 'Payroll' THEN quantity
                WHEN 'Eff_Report' THEN quantity
                WHEN 'Shops' THEN quantity
                ELSE 0
            END
        WHEN REGEXP_MATCH(attribute7, '^-?\d+(\.\d+)?$') THEN CAST(attribute7 AS NUMERIC)
        ELSE 0
    END AS calc_column1,
    -- 第二个计算列按相同逻辑处理
FROM oracle_view_name;

方案4：升级Denodo的Oracle JDBC驱动 #

确保Denodo使用的Oracle JDBC驱动为ojdbc8及以上版本，低版本驱动可能无法识别Oracle 12cR2后的新语法，导致解析错误。

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