30

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# usingadapter.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#?

Improve this question

4 Answers 4

Reset to default
52

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!

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Thanks for posting a solution to your own problem. probably saved me a couple of hours (and gray hair^^). But is there a way that avoids rounding numbers within the query?
I know this post is kinda old, but this just happened to me, and rounding didn't help. In my case, I had some very high values. For completeness (hopefully not needed for market values :-) ) write: ROUND(GREATEST(LEAST(market_value/mv_total,7.9E28),-7.9E28),8)
You brother saved my day
while I did not follow this fixed. I salute you for posting it. I was running in circles for hours.
Ouch, Bitten again. So I did this: SELECT ...., ,to_char(Round(b.Length,2),'9,999.00') AS Feet,... from <some_table> which not only addressed the overly precise return value but also formatted the data in a tidy way, since I only needed it for reporting purposes.
|
9

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

Improve this answer

Comments

4

A more convenient method to solve this issue is to use SuppressGetDecimalInvalidCastException in the OracleDataAdapter:

var table = new DataTable(); await using var connection = new OracleConnection(connectionString); var command = new OracleCommand(queryString, connection) { CommandTimeout = commandTimeout }; using var adapter = new OracleDataAdapter(command) { SuppressGetDecimalInvalidCastException = true }; await Task.Run(() => adapter.Fill(table));

From the documentation:

This property specifies whether to suppress the InvalidCastException and return a rounded-off 28 precision value if the Oracle NUMBER value has more than 28 precision.

Improve this answer

2 Comments

I ran into this same issue using OracleDataReader. Used the SuppressGetDecimalInvalidCastException property on the reader, and the issue was resolved.
Can't believe this is not enabled by default ....... it literally make no sense to even have this option, but having this disabled by default is just evil!! Thank you!
3

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.

Based on OP's SELECT command:

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
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.