"Specified cast is not valid" when populating DataTable from OracleDataAdapter.Fill()

c#, casting, datatable, decimal, oracle

Solution

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.

Solution?

In your sql, round any results that might have too many decimal places to something sensible. So I did this:

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.

Hope this helps someone else!

Problem

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. I have a SQL query which runs fine on Oracle Sql Developer, but when I run it through `C#` using`adapter.Fill(table)` to get the results, I get `Specified cast is not valid` errors (`System.InvalidCastException`). Here is cut-down version of the C# code: ``` 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: ``` 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_total` are 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#?

Original source