Decimal overflow: bug of Oracle .NET Managed driver

[giuliohome]

The Decimal column from Oracle DB can produce an overflow of the corresponding .NET decimal.

Eccezione non gestita: System.InvalidCastException: Cast specificato non valido.
   in Oracle.ManagedDataAccess.Client.OracleDataReader.GetDecimal(Int32 i)
   in Oracle.ManagedDataAccess.Client.OracleDataReader.GetValue(Int32 i)
   in Sql2Xlsx.Oracle.<Read>d__3.MoveNext() in C:\dev\SqlServer\Sql2Xlsx\Oracle.cs:riga 102

A workaround is needed.

// decimal overflow workaround 
field.ObjValue =
    field.DataType == "Decimal"?
    DR.GetDouble(i) :
    DR.GetValue(i);

Please forward and fix the code in the Oracle .NET Managed driver, thanks

[alexkeh]

In the next DB release, ODP.NET will introduce a new SuppressGetDecimalInvalidCastException property that will allow suppressing this error and round off the result automatically.

If you wish to preserve the entire NUMBER value, you can do so today with Safe Type Mapping.

[giuliohome]

Thank you very much for your immediate and very interesting answer!
Please close the issue as you prefer.

[giuliohome]

Just a marginal note, so called Oracle driver "safe tape mapping" seems not practical and usable IMO. Having a list of column names defeats the purpose of general programming and using `byte` or `string` is not a consistent choice in a managed .net solution. I found interesting your anticipation about the exception suppression and the automatic rounding. For the rest, I confirm my workaround as today's solution for my business case.

…

On Mon, Jun 15, 2020, 9:42 PM Alex Keh ***@***.***> wrote: Closed #75 <#75>. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#75 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAY6643E7S7EE4YCOB4SO3DRWZ2TZANCNFSM4N6AOBVA> .

[zak-nye]

I just wanted to post a note that I have set this SuppressGetDecimalInvalidCastException property and still get the issue.

Value = reader.GetValue(i) // i is the field index to the offending column

The Value property being assigned to is of type object. The value being read is -

-8.621E+41
or
-862100000000000000000000000000000000000000

The source system I'm reading from is notoriously unreliable in terms of values so I have to code defensively all over but unfortunately in this case the fix does not work. I'm not sure about more "sensible" values.

[alexkeh]

@zak-nye Isn't that number too large to be stored in a .NET Decimal? It would be out or range.

[zak-nye]

@alexkeh actually you're right. I guess if the number could be rounded and fit in to the type it would work but because this number is too large this doesn't make sense. Thanks I can see it now.

[giuliohome]

It should be a double, not a decimal, and it should not throw an exception... Indeed this is why I kept my workaround and not the proposed-above property