【茶包射手日記】 Oracle NUMBER 與 .NET decimal 小數精準度差異地雷

接獲報案，某段 Dapper + Managed ODP.NET 呼叫 Oracle Stored Procedure 程式出錯，語法類似 cn.Query<SomeEntity>("Some_Stored_Procedure", paramObject, commandType: CommandType.StoredProcedure)，錯誤訊息有點奧妙：

[InvalidCastException: Specified cast is not valid.]
   Oracle.ManagedDataAccess.Client.OracleDataReader.GetDecimal(Int32 i} +136@
   Oracle.ManagedDataAccess.Client.OracleDataReader.GetValue(Int32 i) +4353
   Deserializec98dcac5 -@c@1-48f7-9a54-cc68816e8a79(IDataReader ) +196
[DataException: Error parsing column 1 (AVG_VALUE=CC TEST - String)]
   Dapper .SqlMapper.ThrowDataException(Exception ex, Int32 index, IDataReader reader, Object value) +463
   Deserializec98dcac5 -@c@1-48f7-9a54-cc68816e8a79(IDataReader ) +1037
   Dapper. <QueryImp1l>d__125°1.MoveNext() +808

Dapper 在取得 AVG_VALUE 資料欄出錯，乍看錯誤訊息，似乎是原本應是數字的 AVG_VALUE 被誤植了前一欄的名稱字串內容 "CC TEST" 導致轉型 decimal 失敗。但 B 欄塞成 A 欄資料的狀況實在太離奇了，資料庫查詢結果是結構化資料又不是 CSV，要搞到錯抓欄位，程式是要寫到多低級？

分析案情，查詢到特定日期資料時才出錯，比較容易的解釋還是某天的結果數字較特別，剛好觸發錯誤。

爬文在 Dapper Issue 查到相同案例 - Dapper trying to parse wrong column，幾乎一模一樣的狀況，結論如下：

1. AVG_VALUE 顯示前一欄內容是錯誤訊息的 Bug，其實 Dapper 抓的欄位沒錯。

   This error message with "PRICE=L" is just an incorrect error. It was attempting to parse the correct column.

2. OracleDataReader.GetDecimal() 有個行為，decimal 的精準度只到 29 位 (Decimal 資料類型可為數字提供最大的有效位數。其最多可支援 29 個有效位數 參考)，而 Oracle 的 NUMBER 型別可到 38 位，當 OracleDataReader.GetDecimal() 遇到來源資料位數超過 29 位時，便會拋出 InvalidCastException。
   參考：Bug in OracleDataReader causing InvalidCastException?

了解原理，我成功用一小段程式碼重現錯誤：

using Dapper;
using Oracle.ManagedDataAccess.Client;

var cs = "data source=XE;user id=LAB;password=P@ssW0rd";
var cn = new OracleConnection(cs);

var d = cn.Query<SomeEntity>(
    "SELECT 'CC TEST' AS NAME, 1/3 AS AVG_VALUE FROM DUAL").First();
Console.WriteLine(d);

public class SomeEntity {
    public string NAME { get; set; }
    public decimal AVG_VALUE { get; set;}
}

故意用 1/3 產生循環小數[1]，ODP.NET 在讀取數值便會出現 System.InvalidCastException 錯誤[2] (我測的 Dapper 版本是 2.1.35，錯誤訊息 AVG_VALUE 誤植前一欄 NAME 內容的 Bug 仍在[3])。

以下程式則更明確驗證當 NUMBER 小數位數來到 29 位(含 0 共 30 位)，OracleDataReader.GetDecimal() 會拋出 InvalidCastException：

using Oracle.ManagedDataAccess.Client;

var cs = "data source=XE;user id=LAB;password=P@ssW0rd";
var cn = new OracleConnection(cs);
cn.Open();
for (int i = 25; i < 30; i++)
{
    var cmd = cn.CreateCommand();
    cmd.CommandText = $"SELECT ROUND(1/3, {i}) AS D FROM DUAL";
    var dr = cmd.ExecuteReader();
    dr.Read();
    try {
        Console.WriteLine($"ROUND(1/3, {i}) = {dr.GetDecimal(0)}");
    }
    catch (Exception e) {
        Console.WriteLine(e.Message);
        var s = dr.GetString(0);
        Console.WriteLine(s);
        var d = decimal.Parse(s);
        Console.WriteLine(d);
    }
}

利用 ROUND(1/3, i) 指定保留小數位數，i 由 25 遞增到 30，當 i = 29 時就出錯了。29 位小數加上 0.333... 的 0，共 30 位，超過 decimal 精確度上限，OracleDataReader 的做法是拋出例外。

值得注意的是，若我們將 29 位小數 NUMBER 當成字串，再用 decimal.Parse() 可轉成 decimal，但小數部分會少一位。依據文件，這是 Parse() 的預期行為無誤：當 s 具有小數部分並超過 29 位數(且介於 MaxValue 和 MinValue 的範圍)，則數位會四捨五入為最接近的數位而非截斷為 29 位數。

實務上我們很少會需要超過 29 位的精確數字，故這類問題多半可透過在 Oracle 端只留所需小數位數解決，在用到除法或複雜運算的場合留意一下即可，但沒注意到還是可能踩雷。

又學到一些冷知識。

This article explains and analyzes the case where ODP.NET’s GetDecimal() function fails due to excessive decimal places, validates the conversion behavior through experiments, and offers corresponding suggestions.