5 Replies Latest reply: Apr 2, 2013 9:22 AM by Tridus RSS

    Numerical Conversion Error

    939238
      Hello Everyone,

      We have vb.net application and we are upgrading to .net 4.0 from .net 1.1. Since Microsoft will no longer support System.Data.OracleClient after VS 2010, We decided to migrate to Oracle Provided Data Provider (ODP.net).

      We finished all the conversion and everything went smoothly.

      Then we started testing our application (This is where the problem started).

      Since we all know by default DataAdapter.ReturnProviderSpecificTypes is set to FALSE so that it returns .net specific data types.

      Some of the values returning from the database as DOUBLE has numeric conversion error. i.e.

      I am loading a Dataset using DataAdapter.Fill(Dataset) and one of the column is returning as Double datatype (as expected). But certain values in the columns has numeric conversion error. i.e.

      If I am expecting 2.3 from the database it is returning me 2.3000000000000003. But in the same column 2.31, 2.29..... are fine. We do not know why.

      We double checked database table and made sure value is 2.3. Database column data type is set to NUMBER(10,5) it also happens on the column set to NUMBER(11,6) and so on.....

      This happens only on the DOUBLE datatype. Of-course 2.3 is not the only number for example to name few

      0.94 returned as 0.94000000000000006
      2.8 returned as 2.8000000000000003
      3.05 returned as 3.0500000000000003
      1.4 returned as 1.4000000000000001
      and so on................

      By setting DataAdapter.ReturnProviderSpecificTypes to TRUE seems to fix the problem, but then we need to change our entire application to accept oracle datatype such as ORACLDECIMAL, ORACLESTRING, ORACLEDATE, etc. Which is like rewriting the entire application.


      My question is does any one have the same problem and what is the fix you have implemented?

      Any help would be greatly appreciated.

      Thanks in advance.

      -Pavan-
        • 1. Re: Numerical Conversion Error
          636190
          Pavan,

          Looks like you may have to do some rounding fixups.

          Try something like:
          double x = Math.Round(0.94000000000000006, 5);
          double y = Math.Round(0.94000000000000006, 6);

          r,
          dennis
          • 2. Re: Numerical Conversion Error
            939238
            Dennis,

            Thank you for the Info. We are already doing this as a workaround until we find a proper solution.

            Our application is a financial application and it is all about numbers (Currency, interest....) which are all Double variable and we are using Dataset throughout our application.

            We will load different columns or tables based on different conditions and calculations. So it will be very difficult to know which column will be what.

            I was just hoping there is permanent fix, as this is clearly a ODP.net issue.

            I am even more surprised that no one has seen this problem


            Thank you for time Dennis, I appreciate it.

            Regards,
            Pavan

            Edited by: 936235 on May 29, 2012 8:56 AM
            • 3. I solve this problem. ^^
              810518
              Under solution has a bug.
              fetch twice.

              new solution
              http://www.devart.com/dotconnect/oracle/docs/DataTypeMapping.html#n1
              from this site
              oracle datatype : Number(p,s) : 0 < s < p < 16 ==> Double
              so, modify table datatype to another(p >= 16)
              Thanks ^^

              =================================================
              This is has a bug.

              1. dataadapter fillschema
              2. double column datatype convert decimal
              3. dataAdapter fill

              vb.NET ex)

              ' 먼저 Schema 만 읽어서
              ' Column Type Double 인것을 Decimal 로 변경 ============================
              da.FillSchema(dt_schema, SchemaType.Mapped)
              For Each dc As DataColumn In dtschema.Columns
              If _dc.DataType.Equals(GetType(Double)) Then
              _dc.DataType = GetType(Decimal)
              End If
              Next
              da.Fill(dt_schema)

              Have a nice day^^
              =========================================================

              Edited by: nami on 2013. 4. 1 오전 12:17
              • 4. Re: Numerical Conversion Error
                Tridus
                At the heart of it, the problem is conversions between decimal and double, as they're not the same thing. Double (and all floating point math) have certain numbers that they can't precisely represent. (See this for the gory details: http://stackoverflow.com/questions/1089018/why-cant-decimal-numbers-be-represented-exactly-in-binary )

                If you need the exact value, you want to use decimal as the type. I don't use DataAdapter.Fill(), so I'm not sure how you tell it to do that. (I tend to populate objects myself by reading from the reader and using the various get methods, in which case I can just tell it I want a decimal.)

                edit - Oh yeah, I just saw the part about you doing financial stuff. Definitely use decimal then. Financial math is the main reason it was created.

                Edited by: Tridus on Apr 2, 2013 11:05 AM
                • 5. Re: Numerical Conversion Error
                  Tridus
                  Just as a further comment on this, if you're really using double everywhere in your application you probably already have this problem without realizing it. Try this:
                              double i = 0.1 + 0.1 + 0.1; // 0.30000000000000004
                              decimal j = 0.1m + 0.1m + 0.1m;  // 0.3
                  The problem there is that 0.1 is one of the numbers that double can't represent, so you get something close to it but not quite right.

                  I'm kind of surprised it even gave you doubles by default if the Oracle columns have a fairly small precision. I know EF will give you a decimal by default for something like NUMBER(11,5), AFAIK the provider specific value is an OracleDecimal, if that setting is on, and according to the documentation it's also supposed to give you a decimal.