C# 填充数据集时 ODP.net Oracle 十进制数精度问题。异常：算术运算导致溢出
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1043626/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ODP.net Oracle Decimal Number precision problem when filling a dataset. Exception: Arithmetic operation resulted in an overflow
提问by m3ntat
I am working in c# .net 2 (Visual Studio 2005 SP1) attempting to fill a datasetwith the results from a select * from table from an Oracle10g database. The .net framework, IDE and database cannotbe changed at this client site.
我在 c# .net 2 (Visual Studio 2005 SP1) 中工作，试图用来自 Oracle10g 数据库的 select * from table 的结果填充数据集。无法在此客户端站点更改.net 框架、IDE 和数据库。
I'm connecting using the ODP.net providerthe dll version is 2.102.2.20
我正在使用ODP.net 提供程序进行连接，dll 版本为2.102.2.20
When I run the fill command I get an Exception:
当我运行填充命令时，出现异常：
Arithmetic operation resulted in an overflow
算术运算导致溢出
Also if I attempt to view the offending column in the Visual Studio designer (Show Table Data) I get for every row for this column in the table. The code works perfectly if my query selects other columns with integers for example omitting this column.
此外，如果我尝试在 Visual Studio 设计器（显示表数据）中查看有问题的列，我会得到 对于表中此列的每一行。如果我的查询选择其他带有整数的列（例如省略此列），则代码可以完美运行。
The column in question looks fine when I view it in the database from Toad, data looks like:
当我在 Toad 的数据库中查看时，有问题的列看起来不错，数据如下所示：
919.742866695572
919.742866695572
I need the precisionas it's required for a monte carlo simulation.
我需要精确度，因为它是蒙特卡罗模拟所需要的。
If instead of using a data adapter to fill the datatable I use a datareader and call dataReader.getValue(columnIndex) I get the same error but if I call dataReader.GetOracleDecimal(columnIndex) then I get the result I am looking for, no error.
如果不是使用数据适配器来填充数据表，而是使用数据读取器并调用 dataReader.getValue(columnIndex) 我得到同样的错误，但如果我调用 dataReader.GetOracleDecimal(columnIndex) 然后我得到我正在寻找的结果，没有错误.
I would rather use data adapter and filling a dataset (note these are untyped datasets as I couldn't get autogenerated strongly typed datasets to work from an oracle db). I don't want to use datareader and walk through the results (pick out the column values) as I am trying to write this as a generic method to work for many scenarios regardless of number of columns, index of decimal columns that would require specific get calls by datatype.
我宁愿使用数据适配器并填充数据集（请注意，这些是无类型数据集，因为我无法从 oracle db 中获取自动生成的强类型数据集）。我不想使用 datareader 并遍历结果（挑选列值），因为我试图将其编写为适用于许多场景的通用方法，而不管列数、十进制列的索引需要特定的按数据类型获取调用。
Can anyone help? Can I use new versions of the ODP.net dlls to connect to the older Oracle10gdatabase? am wondering if this will help.
任何人都可以帮忙吗？我可以使用新版本的 ODP.net dll 连接到旧的Oracle10g数据库吗？我想知道这是否会有所帮助。
Thanks
谢谢
回答by jle
You can try the latest version of ODP.Net (11g). It is backwards compatible. I use it to connect to a 10g database just fine. I think it should work with VS 2005 too. For clickonce deployment, just add the dlls referenced by this question: What is the minimum client footprint required to connect C# to an Oracle database?An important note is that if you have the latest version of odp.net, all of the dlls are included in the install directory. You don't need to download instant client separately. Just search for them.
您可以尝试最新版本的 ODP.Net (11g)。它向后兼容。我用它连接到一个 10g 的数据库就好了。我认为它也应该适用于 VS 2005。对于 clickonce 部署，只需添加此问题引用的 dll：将 C# 连接到 Oracle 数据库所需的最小客户端占用空间是多少？一个重要的注意事项是，如果您拥有最新版本的 odp.net，则所有 dll 都包含在安装目录中。您无需单独下载即时客户端。只需搜索它们。
回答by Chris R. Donnelly
The problem is that the precision of the result value is too high to convert to a System.Decimal without some data loss. I forget the exact number of digits allowed, but it is around 18 or so. Is it acceptable to round() the result value to that many digits? In the example you gave, a round(MyColumn, 15)or so should be sufficient...
问题是结果值的精度太高，无法在不丢失一些数据的情况下转换为 System.Decimal。我忘记了允许的确切位数，但大约是 18 位左右。将结果值四舍五入到那么多位数是否可以接受？在你给出的例子中，一个round(MyColumn, 15)左右应该就足够了......
回答by Jana Sattainathan
If you move to a higher version of ODP.NET like 12.x, the error just changes to "Invalid Cast Exception" from "Arithmetic overflow". You would have to CAST numeric values to cut-down insane precision values like 29 or 30 decimal places to make it more practical like 2 to 4 decimals.
如果您移动到更高版本的 ODP.NET，例如 12.x，错误只会从“算术溢出”更改为“无效的强制转换异常”。您将不得不 CAST 数值来减少疯狂的精度值，如 29 或 30 位小数，使其更实用，如 2 到 4 位小数。
To identify the columns and rows which have excessively big decimal values, you can run the SQL below after substituting the MY_SCHEMA, MY_TABLE and the number 10 with say 25 to identify columns that have values over 25 decimal places. This SQL will generate a SQL that should be run to identify the problem columns.
要识别具有过大十进制值的列和行，您可以在将 MY_SCHEMA、MY_TABLE 和数字 10 替换为 25 后运行以下 SQL，以识别具有超过 25 个小数位的值的列。此 SQL 将生成应运行以识别问题列的 SQL。
SELECT 'SELECT ' || LISTAGG('MAX(LENGTH(TO_CHAR(ABS(' || column_name || ') - FLOOR(ABS(' || column_name || '))))) - 1 AS decimals_' || column_name || CHR(13)
, CHR(9)|| ', ') WITHIN GROUP (ORDER BY rn) ||
' FROM ' || owner || '.' || table_name || CHR(13) ||
' WHERE ' || CHR(13) ||
LISTAGG('(LENGTH(TO_CHAR(ABS(' || column_name || ') - FLOOR(ABS(' || column_name || ')))) - 1) > 10 ' || CHR(13)
, CHR(9)|| ' OR ')
WITHIN GROUP (ORDER BY rn) AS Nasty_Numbers_Finder_Query
FROM
(
SELECT owner, table_name, column_name,
row_number() OVER ( PARTITION BY table_name ORDER BY rownum) rn
FROM dba_tab_columns
WHERE
OWNER = 'MY_SCHEMA'
AND table_name = 'MY_TABLE'
AND (data_type LIKE '%FLOAT%'
OR data_type LIKE '%NUMERIC%')
) a
GROUP BY owner, table_name
For more information, I have blogged about it here.
欲了解更多信息，我的博客上讲述它在这里。
