11

I know there are several threads and posts regarding this issue in the internet and I've read them (not every article, I have to admit) but none of them did fully satisfy me.

My situation:
I'm using ODP.net (dll version 2.111.6.0) to access the Oracle DB (version 10 + 11) and a DataReader to retrieve the data (.NET 3.5, C#).

Using this code results in a 'System.OverflowException (Arithmetic operation resulted in an overflow.)'


decimal.TryParse(oraReader.GetOracleDecimal(0).Value.ToString(), 
  NumberStyles.Any, null, out parsedOraDecimal)

and this one results in a value of '3,000000000000000000000000000000000000000000000000000000000E-126'


decimal.TryParse(oraReader.GetOracleValue(0).ToString(), 
  NumberStyles.Any, null, out parsedOraDecimal)

Now I have to find some way to retrieve and evaluate this value properly - the DB is also used from other apps which are out of my control so changes there are not possible.

Converting the types in my C# code from 'decimal' to 'double' is also not really an option.

Any ideas?

Kit
  • 15,260
  • 3
  • 47
  • 87
robert.oh.
  • 636
  • 2
  • 5
  • 12

2 Answers2

17

OracleDecimal has a larger precision than decimal. For that reason, you have to reduce the precision first. Forget all the parsing, use implicit conversion. Try something along the lines of (untested):

decimal d = (decimal)(OracleDecimal.SetPrecision(oraReader.GetOracleDecimal(0), 28));
Erich Kitzmueller
  • 34,314
  • 6
  • 72
  • 98
  • 6
    I hate Oracle; So everyone just wants a regular Decimal, yet Oracle makes us jump through hoops. – Eric Schneider Nov 30 '12 at 15:53
  • 1
    Thanks, I used this as my solution. my first time working with the Oracle provider has not been a pleasant one.... – Etch Feb 08 '13 at 14:24
  • 1
    eschneider: Oracle had the larger precission before C# was invented, so they have to do something that allows using the full precision the database has to offer. – Erich Kitzmueller Feb 13 '13 at 21:59
9

I just had a similar issue, and tried the approach of changing the OracleDataAdapter to return Oracle specific types ( data_adapter.ReturnProviderSpecificTypes = true; ), but this is just a PITA, you end up casting OracleStrings back to strings, etc.

In the end I solved it by doing the precision rounding in the SQL statement using Oracle's round function:

SELECT round( myfield, 18 ) FROM mytable

Dotnet will then happily convert the figure to a decimal.

Dave Glassborow
  • 2,707
  • 1
  • 24
  • 24