oracle 从 OracleDataAdapter.Fill() 填充 DataTable 时“指定的强制转换无效”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23935716/
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
"Specified cast is not valid" when populating DataTable from OracleDataAdapter.Fill()
提问by namford
I can't seem to find this question anywhere on Google (or StackOverflow), which really surprised me, so I'm putting it on here to help others in the same situation.
我似乎无法在 Google（或 StackOverflow）上的任何地方找到这个问题，这真的让我感到惊讶，所以我把它放在这里以帮助处于相同情况的其他人。
I have a SQL query which runs fine on Oracle Sql Developer, but when I run it through C#usingadapter.Fill(table)to get the results, I get Specified cast is not validerrors (System.InvalidCastException).
我有一个 SQL 查询，它在 Oracle Sql Developer 上运行良好，但是当我通过C#using运行它adapter.Fill(table)以获取结果时，Specified cast is not valid出现错误 ( System.InvalidCastException)。
Here is cut-down version of the C# code:
这是 C# 代码的简化版本：
var resultsTable = new DataTable();
using (var adapter = new OracleDataAdapter(cmd))
{
var rows = adapter.Fill(resultsTable); // exception thrown here, but sql runs fine on Sql Dev
return resultsTable;
}
And here is a simplified version of the SQL:
这是 SQL 的简化版本：
SELECT acct_no, market_value/mv_total
FROM myTable
WHERE NVL(market_value, 0) != 0
AND NVL(mv_total, 0) != 0
If I remove the division clause, it doesn't error - so it's specific to that. However, both market_value and mv_totalare of type Number(19,4) and I can see that the Oracle adapter is expecting a decimal, so what cast is taking place? Why does it work on SqlDev but not in C#?
如果我删除除法子句，它不会出错 - 所以它是特定的。但是，market_value 和mv_totalNumber(19,4) 类型都属于类型，我可以看到 Oracle 适配器需要一个小数，那么发生了什么转换？为什么它适用于 SqlDev 而不适用于 C#？
回答by namford
Answering my own question:
回答我自己的问题：
So it seems that the Oracle number type can hold many more decimal places than the C# decimal type and if Oracle is trying to return more than C# can hold, it throws the InvalidCastException.
因此，似乎 Oracle 数字类型可以容纳比 C# 小数类型多得多的小数位，如果 Oracle 试图返回的数量超过 C# 可以容纳的数量，则会引发 InvalidCastException。
Solution?
解决方案？
In your sql, round any results that might have too many decimal places to something sensible. So I did this:
在您的 sql 中，将任何可能有太多小数位的结果四舍五入为合理的值。所以我这样做了：
SELECT acct_no, ROUND(market_value/mv_total, 8) -- rounding this division solves the problem
FROM myTable
WHERE NVL(market_value, 0) != 0
AND NVL(mv_total, 0) != 0
And it worked.
它奏效了。
The take away is: Incompatibility between Oracle number type and C# decimal. Restrict your Oracle decimal places to avoid the invalid cast exceptions.
要点是：Oracle 数字类型和 C# 十进制之间不兼容。限制您的 Oracle 小数位以避免无效的强制转换异常。
Hope this helps someone else!
希望这对其他人有帮助！
回答by AAGF
I know this thread is really old.. However I had a similar issue.
我知道这个线程真的很老..但是我有一个类似的问题。
The best solution I have to use the Oracle method TO_BINARY_DOUBLE on Oracle Decimal column
我必须在 Oracle Decimal 列上使用 Oracle 方法 TO_BINARY_DOUBLE 的最佳解决方案
回答by FrankTheTank
I know this has been answered already, but I also found another alternative that I use as well. I used a CAST on the field that was giving me troubles.
我知道这已经得到了回答，但我也找到了另一个我使用的替代方案。我在给我带来麻烦的场地上使用了 CAST。
Based on OP's SELECT command:
基于 OP 的 SELECT 命令：
SELECT acct_no, CAST((market_value/mv_total) AS DECIMAL(14,4)) -- CAST to decimal here
FROM myTable
WHERE NVL(market_value, 0) != 0
AND NVL(mv_total, 0) != 0
