【茶包射手日記】OracleDataReader數字型別對應陷阱(ODP.NET)

接獲報案，由Oracle資料庫讀取NUMBER(14,6)欄位，原值為1.997748，JSON轉換送至Client端變成1.9977479999999999!

判斷這是典型浮點數問題(順便複習: 算錢用浮點，遲早被人扁)，但檢視程式，OracleCommand執行查詢，由NUMBER(14,6)欄位直接SELECT取值，未經過任何計算，資料由OracleDataReader讀取交給JsonConvert轉換，程式寫法沒有可議之處，決定使用浮點型別(double)的犯人--顯然是OracleDataReader! 試著CAST將該欄位轉型成NUMBER(20,6)，OracleDataReader便會視為decimal，不再有浮點誤差問題。

用一小段程式重現問題: (.NET 4 Console Appliation，使用 Oracle.DataAccess.Client 2.112.3/4.112.3/4.121.1 測試結果相同)

        static void Main(string[] args)

        {

            OracleConnection cn = new OracleConnection(strConnString);

            cn.Open();

            var cmd = cn.CreateCommand();

            cmd.CommandText = @"select 

cast(1.997748 as number(14,6)) as n1,

cast(1.997748 as number(20,6)) as n2

from dual";

            OracleDataReader dr = cmd.ExecuteReader();

            dr.Read();

            Console.WriteLine("ToString()\t{0}\t{1}", 

                dr["n1"].ToString(), dr["n2"].ToString());

            Console.WriteLine("JSON\t{0}\t{1}",

                JsonConvert.SerializeObject(dr["n1"]),

                JsonConvert.SerializeObject(dr["n2"]));

            Console.WriteLine("DataType\t{0}\t{1}",

                dr.GetFieldType(0), dr.GetFieldType(1));

            Console.Read();

        }

執行結果如下:

ToString()      1.997748        1.997748
JSON    1.9977479999999999      1.997748
DataType        System.Double   System.Decimal

由此驗證，同樣的數字，對於NUMBER(14,6)、NUMBER(20,6)兩種型別，OracleDataReader會選用System.Double或System.Decimal承接。另外還有一項有趣發現 -- double 1.9977479999999999的ToString()結果是"1.997748"，害我一開始被假象唬得團團轉。

查了Oracle官方文件，上面是這麼說的:

Certain methods and properties of the OracleDataReader object require ODP.NET to map a NUMBER column to a .NET type based on the precision and scale of the column. These members are:

Item property, GetFieldType method, GetValue method, GetValues method

ODP.NET determines the appropriate .NET type by considering the following .NET types in order, and selecting the **first** .NET type from the list that can represent the entire range of values of the column:

System.Byte, System.Int16, System.Int32, System.Int64, System.Single, System.Double, System.Decimal

If no .NET type exists that can represent the entire range of values of the column, then an attempt is made to represent the column values as a System.Decimal type. If the value in the column cannot be represented as System.Decimal, then an exception is raised.

For example, consider two columns defined as NUMBER(4,0) and NUMBER(10,2). The first .NET types from the previous list that can represent the entire range of values of the columns are System.Int16 and System.Double, respectively. However, consider a column defined as NUMBER(20,10). In this case, there is no .NET type that can represent the entire range of values on the column, so an attempt is made to return values in the column as a System.Decimal type. If a value in the column cannot be represented as a System.Decimal type, then an exception is raised.

依此推論，呼叫GetValue()、GetFieldType()讀取NUMBER型別欄位，ODP.NET會依byte, short, long, single, double, decimal的順序，使用第一個能完整容納精確數字的型別。

寫一小段程式驗證:

        static void Main(string[] args)

        {

            OracleConnection cn = new OracleConnection(strConnString);

            cn.Open();

            var cmd = cn.CreateCommand();

            cmd.CommandText = @"select 

cast(1 as number(1,0)) as n1,

cast(12 as number(5,0)) as n2,

cast(12 as number(5,1)) as n3,

cast(12 as number(10,0)) as n4,

cast(12 as number(10,1)) as n5,

cast(12 as number(15,0)) as n6,

cast(12 as number(15,1)) as n7,

cast(12 as number(20,0)) as n8,

cast(12 as number(20,1)) as n9

from dual";

            OracleDataReader dr = cmd.ExecuteReader();

            dr.Read();

            for (int i = 0; i < 9; i++)

            {

                Console.WriteLine("T:{0} V:{1}",

                    dr.GetFieldType(i), dr.GetValue(i));

            }

            Console.Read();

        }

隨著小數位數增加，OracleDataReader陸續選用Int16, Int32, Int64, Single, Double, Decimal(但試不出文件說的Byte)

T:System.Int16 V:1
T:System.Int32 V:12
T:System.Single V:12
T:System.Int64 V:12
T:System.Double V:12
T:System.Int64 V:12
T:System.Double V:12
T:System.Decimal V:12
T:System.Decimal V:12

OracleDataReader確實會依數字精確度決定型別，但官方文件並未說明規則(或許也意味著隨時可能修正，應該視為黑盒子)，熬不過好奇心驅使，我還是反組譯OracleDataAccess把演算法挖了出來: (以4.112.3版本為例)

假設數字精準度為NUMBER(Precision, Scale)，不考慮Scale < 0的罕見情境

• Scale = 0 && Precision < 5 –> short
• Scale = 0 && Precision < 10 –> int
• Scale = 0 && Precision < 19 –> long
• Precision < 8 && 0 < Scale < 50 –> float
• Scale > 0 &&  Precision < 16 –> double
• 其他 –> decimal

回到NUMBER(14,6)被轉成double的問題上。自動判斷型別發生於GetValue()、ToString()，OracleDataReader在底層仍明確知道真實型別，用dr.GetProviderSpecificFieldType(0)可取得Oracle.DataAccess.Types.OracleDecimal型別，而dr.GetDecimal(0)也能正確讀出1.997748。但大部分通用型的轉換程式，多半只用DataReader.GetValue()直接取回object型別，若加入GetProviderSpecificFieldType()邏輯將會對資料庫種別產生依賴。

找不到改變ODP.NET OracleDataReader自勳判斷行為的方法(OracleDataAdapter有所謂Safe Type Mapping，但不能直接用於OracleDataReader)，要避免NUMBER()被轉成浮數，我想到以下幾種做法:

1. SELECT時用CAST轉型為16位以上的精確度，例如: NUBMER(16,n)，強迫OracleDataReader以decimal處理
2. 改用Entity Framework等ORM做法
3. 資料讀取程式針對ODP.NET加入邏輯，以GetProviderSpecificFieldType()偵測OracleDecimal，一律用.GetDecimal()取值傳回decimal

PS: System.Data.OracleClient有來自火星的外電消息: 我的OracleDataReader遇到NUMBER一律傳回decimal...