OracleDataReader.GetValue() throws InvalidCastException

User_TB37Y

Hello,

I'm using Oracle.ManagedDataAccess.dll in version 4.122.19.1.

I found that in some edge cases, the OracleDataReader throws InvalidCastException when calling GetValue() or GetDecimal(). GetFieldType returns System.Decimal. This behavior seems to be incorrect.

The code below allows to reproduce this problem:

private static void TestAvg(DbConnection connection)
      {
          using (var command = connection.CreateCommand())
          {
              command.CommandText = @"SELECT AVG(VAL) AS AVG
FROM
(
   SELECT CAST(9 AS NUMBER(5, 0)) AS VAL FROM DUAL
   UNION ALL
   SELECT CAST(13 AS NUMBER(5, 0)) AS VAL FROM DUAL
   UNION ALL
   SELECT CAST(1 AS NUMBER(5, 0)) AS VAL FROM DUAL
)";
                  

              using (var reader = command.ExecuteReader())
              {
                  reader.Read();

                  var dataTypeName = reader.GetDataTypeName(0); // Returns "Decimal"
                  Debug.WriteLine(dataTypeName);
                  
                  var fieldType = reader.GetFieldType(0); // Returns Type System.Decimal
                  Debug.WriteLine(fieldType);
                  
                  var isDbNull = reader.IsDBNull(0); // Returns false
                  Debug.WriteLine(isDbNull);
                  
                  var doubleValue = reader.GetDouble(0); // Returns double value successfully
                  Debug.WriteLine(doubleValue);
                  
                  var objectValue = reader.GetValue(0); // Invalid cast exception
                  Debug.WriteLine(objectValue);
                  
                  var decimalValue = reader.GetDecimal(0); // Invalid cast exception
                  Debug.WriteLine(decimalValue);
              }
          }
      }

Checked on the following server versions:

SELECT * FROM v$version

1. Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
2. Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Alex Keh-Oracle

I've created an Enhancement Request (ER 33124759). You can open a support to add more business case details, as well as adding the customer name. Right now, I just populated it with your user name.

For Entity Framework, referencing provider-specific APIs is rare. It is not an issue unless your EF code must remain data source neutral.

Alex Keh-Oracle

Add the following code ahead of the GetValue and GetDecimal methods:

          reader.SuppressGetDecimalInvalidCastException = true;

Once you do that, there will no longer be an error. You will get a rounded off value from GetValue and GetDecimal.

This property is part of ODP.NET 19.10 and higher.

The fundamental issue is that the returned value is too big for OracleDecimal to represent. That leads to an error. This property when set to true tells ODP.NET if the value is too big, then round it off and return that value as OracleDecimal.

User_TB37Y

Thank you for answer.

Is it possible to set this flag globally, without affecting each reader?

I'm asking, because for example I can't access reader and change this flag when executing SQL via ORM like Entity Framework:

private static void TestAvg(DbContext dbContext)
      {
          var sql = @"SELECT AVG(VAL) AS AVG
FROM
(
   SELECT CAST(9 AS NUMBER(5, 0)) AS VAL FROM DUAL
   UNION ALL
   SELECT CAST(13 AS NUMBER(5, 0)) AS VAL FROM DUAL
   UNION ALL
   SELECT CAST(1 AS NUMBER(5, 0)) AS VAL FROM DUAL
)";

          try
          {
              var result = dbContext.Database.SqlQuery<TestEntry>(sql).FirstOrDefault();
              Debug.WriteLine("AVG = " + result?.AVG);
          }
          catch (Exception exception)
          {
              Debug.WriteLine(exception);
          }
      }

private class TestEntry
{
    public decimal? AVG { get; set; }
}

Alex Keh-Oracle

There is no global setting. Having a global setting would be convenient, but that would make data loss the default, which is generally not a good practice.

Some alternatives would be to round the value or to retrieve it as a double instead.

User_TB37Y

The system we develop has huge amount of SQL statements that are stored in the configuration. The configuration is often managed by our customers. Now we are trying to switch from third party driver (Devart) to ODP.NET.

In our scenario it would be really helpful to have such a global setting - especially because in some cases we can't modify the statements to return rounded numbers.

That's why it would be perfect for us to have this flag available in OracleConfiguration class.

Should we create a ticket by using Oracle support portal?

Alex Keh-Oracle

I've created an Enhancement Request (ER 33124759). You can open a support to add more business case details, as well as adding the customer name. Right now, I just populated it with your user name.

For Entity Framework, referencing provider-specific APIs is rare. It is not an issue unless your EF code must remain data source neutral.