Forum Stats

  • 3,781,547 Users
  • 2,254,529 Discussions
  • 7,879,751 Comments

Discussions

InvalidCastException retrieving a high precision decimal

user4977350
user4977350 Member Posts: 1
edited Dec 3, 2019 1:58PM in ODP.NET

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).

user8091922

Best Answer

  • user8091922
    user8091922 Member Posts: 1
    edited Dec 3, 2019 1:58PM Accepted 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.

Answers

  • John Fedak
    John Fedak Member Posts: 11
    edited Mar 2, 2017 8:45PM

    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
    user8091922 Member Posts: 1
    edited Dec 3, 2019 1:58PM Accepted 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.

This discussion has been closed.