ODP.NET 無法讀取 Oracle 欄位計算結果

同事報案,使用 Dapper + ODP.NET 呼叫某 Procedure,以 Ref Cursor 取資料時出現型別轉換錯誤,一路深入追查,發現問題跟是否用了 Procedure、Ref Cursor、 Dapper 都沒有關係,錯誤發生在 ODP.NET 層。

有問題的查詢涉及幾個高精確度的欄位運動,經過一番簡化,我先找出用下列查詢可重現問題。

使用 PL/SQL Developer 查詢不會出錯,但使用 ODP.NET OracleDataReader dr["N"]、dr.GetDecimal(0)、dr.GetValue() 都會出現「指定的轉換無效」(Invalid Cast Exception)錯誤,dr.GetDouble(0) 則能讀出結果:

由於出錯案例的爆點在幾個高精確度數字(NUMBER(14)、NUMBER(10,7))的乘除結果,一度以為跟運算成員的欄位精確位數大高造成的。

經過一番調查,我才搞懂背後發生什麼事,並有了新結論:

  1. Oracle NUMBER 的最高精確位數高達 38 位,C# decimal 只有 28-29 位,當 Oracle 欄位值高於 28 位,ODP.NET 祭出 .NET 世界精準度最高的 decimal 也裝不下,引發轉型別轉換失敗錯誤。(延伸閱讀:ODP.NET 如何決定數值欄位的 .NET 對應型別?
  2. 其實不必動用超大位數數字進行運算,只要一個簡單的除法搞出無窮小數就能重現 Oracle 位數超出 decimal 負荷的狀況。
  3. 當未指定型別,Oracle 會以 NUMBER(預設 38 位最高精確度)傳回運算結果,換言之,只要遇到無窮小數就會傳回滿滿 38 位數字讓 decimal 難看。
  4. PL/SQL Developer(或其他 Oracle 查詢軟體)顯示的結果小數位數是經過處理的,並非 Oracle 傳回的原始內容。

讓我用一個例子證明 1、2 兩點:

class Program
{
    static void TryGetValue(OracleDataReader dr, int n)
    {
        try
        {
            Console.WriteLine($"N{n}={dr.GetValue(n)}");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"N{n} GetValue Error = {ex.Message}");
        }
    }
    static void DumpBinData(OracleDataReader dr, int n)
    {
        string binData = BitConverter.ToString(dr.GetOracleDecimal(n).BinData);
        Console.WriteLine($"N{n} .BinData = {binData}");
    }
 
    static void Main(string[] args)
    {
        using (var cn = new OracleConnection(Helper.GetCnnStr()))
        {
 
            cn.Open();
            var cmd = cn.CreateCommand();
            cmd.CommandText = @"
SELECT         
100/17 AS N0,
CAST(100/17 AS NUMBER(*,28)) AS N1,
CAST(100/17 AS NUMBER(*,29)) AS N2,
CAST(100/17 AS NUMBER) AS N4
FROM DUAL";
            var dr = cmd.ExecuteReader();
            dr.Read();
            for(var i = 0; i < 4; i++)
            {
                Console.WriteLine($"---N{i}---");
                TryGetValue(dr, i);
                DumpBinData(dr, i);
            }
        }
        Console.Read();
    }
}

我用 100/17 製造出包含無窮小數的數值,再分別 CAST 換成 NUMBER(*, 28)、NUMBER(*,  29) 及 NUMBER 三種型別。結果只有轉成 NUMBER(*, 28) 的欄位能被正確讀取,其餘的都發生轉型失敗。另外,由 OracleDataReader.GetOracleDecimal().BinData 可以檢視 Oracle 傳回的原始資料,N0(未指定型別)及 N4(轉型為 NUMBER)的 BinData 是相同的,足以證明當未指定型別時,Oracle 採用最大精確度的 NUMBER 型別。

所以,當查詢結果出現除不盡狀況的狀況,其真實數字位數會高達 38 位,由 Oracle 工具軟體看到的結果是經過四捨五入的結果,如第一張圖例的 PL/SQL Developer 取 16 位,不同的工具結果不同,例如改用 SQLPLUS,位數就只有 9 位。

所以,不要拿工具軟體查詢到的數字當成標準答案!

最後,好奇在 SQL Server 上會不會遇到同樣的狀況?答案是不會:

SQL 整數相除結果預設為整數,非整數相除預設精準度預設為 Single,除非刻意轉型讓位數破錶,否則不會出錯,而錯誤訊息為「轉換溢位」較明確。

結論:

當在 Oracle 查詢使用除法運算,請記得轉型讓精準度夠用就好,否則一旦出現除不盡的狀況,你就有得忙了。

歡迎推文分享:
Published 27 April 2017 07:41 AM 由 Jeffrey
Filed under:
Views: 2,977



意見

沒有意見

你的看法呢?

(必要的) 
(必要的) 
(選擇性的)
(必要的) 
(提醒: 因快取機制,您的留言幾分鐘後才會顯示在網站,請耐心稍候)

5 + 3 =

搜尋

Go

<April 2017>
SunMonTueWedThuFriSat
2627282930311
2345678
9101112131415
16171819202122
23242526272829
30123456
 
RSS
創用 CC 授權條款
【廣告】
twMVC

Tags 分類檢視
關於作者

一個醉心技術又酷愛分享的Coding魔人,十年的IT職場生涯,寫過系統、管過專案, 也帶過團隊,最後還是無怨無悔地選擇了技術鑽研這條路,近年來則以做一個"有為的中年人"自許。

文章典藏
其他功能

這個部落格


Syndication