Skip to Main Content
Forums

ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

InvalidCastException retrieving a high precision decimal

user4977350Feb 16 2017 — edited Dec 3 2019

If a select statement returns a number field with precision greater then the max precision of a .net decimal it throws an InvalidCastException from Oracle.ManagedDataAccess.Client.OracleDataReader.GetDecimal().

A simple way this can be seen is:

OracleCommand cmd = // get the command

cmd.CommandText = "SELECT 1/3 FROM DUAL";

object x = cmd.ExecuteScalar(); // InvalidCastException

Whilst this can obviously be fixed by using CAST or ROUND in the query are there any plans to address this problem in the driver?

Tested using 64-bit ODAC 12c Release 4 (12.1.0.2.4).
This post has been answered by user8091922 on Jun 27 2017
Jump to Answer

Comments

John Fedak

The issue here is that Oracle supports more precision than the native .Net CLR types

You can work around this using the DataAdapter, specifying ReturnProviderSpecificTypes, and then manually adjusting the precision on the OracleDecimal before conversion.

I would really like to see a setting on the driver that allows you to tell it that you don't care about the precision differences and just to round the value.

This is what users would want outside of extremely rare cases where the extra precision is actually needed.

user8091922
Answer

Same problem here. We just changed our project from Microsoft's deprecated Oracle client to ODP.NET. Once we rolled out the change .. BOOM ... InvalidCastException on Oracle.ManagedDataAccess.Client.OracleDataReader. We fixed that problem by adding a cast to the select statement, but the danger is that this will crop up again anywhere a division operator is used (or perhaps in some other scenario that we currently are not thinking of).

I get the reason why this happens, but I would still consider it a bug. If for no other reason than the fact that the behavior is completely unexpected. Calling ExecuteScalar against a valid SQL statement would not ever be expected to toss an InvalidCastException deep within the Oracle.ManagedDataAccess library. It violates the Principle of Least Astonishment.

===============

This message is from Alex Keh, ODP.NET product manager:

In ODP.NET 20c, there will be a new Boolean property on the DataReader and DataAdapter called SuppressGetDecimalInvalidCastException. When set to true, the exception will be suppressed and the value will be rounded for .NET Decimal. This new property should address this issue.

Marked as Answer by user4977350 · Sep 27 2020
1 - 2
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 25 2017
Added on Feb 16 2017
#odp-net
2 comments
5,390 views