.NET使用Oracle.ManagedDataAccess时日期运算引发类型转换无效异常

问题背景 #

将旧VBScript转换为.NET Framework 4.7控制台应用时，原SQL语句在VBScript、Oracle SQL Developer及ODBC连接的MS Access中均可正常运行，但使用Oracle.ManagedDataAccess NuGet包21.12.0时，抛出“指定的转换无效”异常。经排查，问题出在SQL中的日期运算：CURRENT_DATE - PROGRAM_CHANGE_LOG.MODIFY_DATE_TIME AS days，其中CURRENT_DATE是Oracle内置函数，MODIFY_DATE_TIME为Oracle DATE类型。

原因分析 #

Oracle中两个DATE类型相减返回NUMBER类型（值为天数，含小数部分表示时分秒），但Oracle.ManagedDataAccess对该类型的默认映射逻辑，与ODBC/VBScript的处理方式存在差异，导致OracleDataAdapter.Fill(dataTable)时发生类型转换失败。

解决方法 #

方法一：修改SQL明确返回数值类型（推荐） #

通过CAST或ROUND函数，将日期运算结果明确转换为指定精度的数值类型，消除隐式转换的不确定性：

• 保留小数精度：

  CAST(CURRENT_DATE - PROGRAM_CHANGE_LOG.MODIFY_DATE_TIME AS NUMBER(10,2)) AS days
  

• 仅保留整数天数：

  ROUND(CURRENT_DATE - PROGRAM_CHANGE_LOG.MODIFY_DATE_TIME) AS days
  

方法二：在.NET代码中调整读取逻辑 #

若无法修改SQL，可通过以下方式规避转换问题：

1. 开启BindByName配置：

   oracleCommand.BindByName = True
   

2. 手动读取并转换字段值（不推荐，效率低于修改SQL）：

   Using reader As OracleDataReader = oracleCommand.ExecuteReader()
       While reader.Read()
           Dim days As Double = Convert.ToDouble(reader("days"))
           ' 其他字段读取逻辑
       End While
   End Using
   

完整修改后的SQL示例 #

将原SQL中的日期运算部分替换为明确的类型转换，完整SQL如下：

SELECT DISTINCT
    CAST(CURRENT_DATE - PROGRAM_CHANGE_LOG.MODIFY_DATE_TIME AS NUMBER(10,2)) AS days,
    PROGRAM_CHANGE_LOG.SEQUENCE_NBR,
    PROGRAM_CHANGE_LOG.FORM_NAME,
    FUNCTION.DESCRIPTION,
    USER_LIST.LAST_NAME,
    USER_LIST.FIRST_NAME,
    USER_LIST.USERID,
    PROGRAM_CHANGE_LOG.INITIATED_BY,
    PROGRAM_CHANGE_PROJECT.DESCRIPTION Project,
    cnt.C - 1 AS C,
    PROGRAM_CHANGE_LOG.STATUS
FROM
     ((((PROGRAM_CHANGE_LOG 
LEFT JOIN 
    PROGRAM_CHANGE_PROJECT ON PROGRAM_CHANGE_LOG.PROJECT_CD = PROGRAM_CHANGE_PROJECT.PROJECT_CD) 
LEFT JOIN 
    FUNCTION ON PROGRAM_CHANGE_LOG.FORM_NAME = FUNCTION.FUNCTION_NAME) 
INNER JOIN 
    USER_LIST ON PROGRAM_CHANGE_LOG.REQUEST_BY = USER_LIST.USERID)
INNER JOIN 
    (SELECT Count(PROGRAM_CHANGE_LOG.SEQUENCE_NBR) C, 
        PROGRAM_CHANGE_LOG.FORM_NAME
    FROM 
        PROGRAM_CHANGE_LOG 
    WHERE 
        PROGRAM_CHANGE_LOG.STATUS IN ('OPEN','TEST','QUES') 
    GROUP BY 
        PROGRAM_CHANGE_LOG.FORM_NAME) cnt ON PROGRAM_CHANGE_LOG.FORM_NAME = cnt.FORM_NAME) 
INNER JOIN 
    PROGRAM_CHANGE_NOTES ON PROGRAM_CHANGE_LOG.SEQUENCE_NBR = PROGRAM_CHANGE_NOTES.SEQUENCE_NBR
GROUP BY 
    CAST(CURRENT_DATE - PROGRAM_CHANGE_LOG.MODIFY_DATE_TIME AS NUMBER(10,2)), 
    PROGRAM_CHANGE_LOG.SEQUENCE_NBR, 
    PROGRAM_CHANGE_LOG.FORM_NAME, 
    FUNCTION.DESCRIPTION, 
    USER_LIST.LAST_NAME, 
    USER_LIST.FIRST_NAME, 
    PROGRAM_CHANGE_LOG.INITIATED_BY, 
    PROGRAM_CHANGE_PROJECT.DESCRIPTION, 
    cnt.C, 
    PROGRAM_CHANGE_LOG.STATUS, 
    USER_LIST.USERID
HAVING 
    PROGRAM_CHANGE_LOG.STATUS IN ('TEST', 'QUES') 
ORDER BY 
    days DESC

验证 #

修改后，Oracle会明确返回NUMBER类型的days字段，Oracle.ManagedDataAccess可正确映射为.NET的Double或Decimal类型，OracleDataAdapter.Fill(dataTable)操作将正常执行，不再抛出转换异常。

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