4

We have a setup where a remote C++ client invokes an Oracle function (using OCCI library) passing some data. This Oracle function then inserts the data into a table. One of the data which gets passed to the function is AMOUNT, which is a floating point number.

In C++ client, this AMOUNT variable is defined as a "double". Both the Oracle function and the table define the data type for this column as "NUMBER" (without any precision or scale defined explicitly).

We are seeing a lot of inconsistency between what the client is sending and what we are receiving and storing at our database. For example, the client claim that they are passing a value “35.6”, but what we are seeing in our table is “35.59999847”.

We are supposed to store the values with an accuracy of up to 6 decimal places. If I change the data type definition of the AMOUNT column from NUMBER to NUMBER(38,6), I get “35.599998”. If I change it to NUMBER (38,5), I end up getting “35.6”.

Can someone suggest what might be happening here? I know that it is not advisable to treat the amount or price values as floating point numbers and should use whole numbers instead, but in this case we don’t have any control over the C++ client. And we are seeing this issue for a very large set of data (more than 50% of the data suffers this issue).

Also, this issue is not just limited to floating point numbers. We can see inconsistencies even with large whole numbers (for example, a value passed as 1000000000 gets stored as 1000000004).

cdoe
  • 447
  • 5
  • 13
  • http://floating-point-gui.de/ – Oliver Charlesworth Jun 14 '13 at 11:44
  • possible duplicate of [Floating point error in representation?](http://stackoverflow.com/questions/7581741/floating-point-error-in-representation) – Oliver Charlesworth Jun 14 '13 at 11:45
  • If the C++ client uses DECIMAL and th database uses NUMBER, where exactly does floating-point come into it? – user207421 Jun 14 '13 at 11:56
  • @EJP - sorry, meant to say 'double' and not 'decimal'. Edited my post. – cdoe Jun 14 '13 at 12:20
  • @OliCharlesworth, yes I have seen that site before. But I never thought that the issue would be too widespread. Also, the same C++ client is making calls to other systems as well (MySQL database, for example). And they are in sync. So far this issue is seen only with C++ and Oracle integration. – cdoe Jun 14 '13 at 12:35
  • @cdoe: Floating-point precision affects everything that uses floating-point! The unavoidable problem is that 35.6 cannot be exactly represented in binary FP; the number you're seeing is probably the nearest representable value. It sounds like this has already occurred inside the client application, so there's nothing you can do about it if you are unable to modify that. – Oliver Charlesworth Jun 14 '13 at 14:14

1 Answers1

1

You may not have control over the client but can you change the interface?

Passing a floating point to Oracle will inevitable cause this problem, because they are an inherently imprecise datatype. But if you can amend the interface you could pass two integers to Oracle: the AMOUNT's integer and the AMOUNT's fractional part. Oracle can then combine these two integers to make a single NUMBER variable which it can use happily.

Since I wrote this you have edited your question, and as Oli points out, this suggestion is no longer valid.

What can you do instead?

  1. Fake it in the Oracle layer by enforcing a decimal precision there. That may create a few rounding errors but much fewer than you have now. Although it won't solve the problem with large whole numbers.
  2. Raise this as a bug with the developers of the C++ client. (Pro tip: Try not to call them blithering imbeciles when you submit the bug report).
  3. Tell your users they'll just have to live with it.

As for why this is a problem for Oracle and not your other systems, well MySQL supports FLOAT and DOUBLE, and presumably those other systems do as well. Oracle has always been very strict on data integrity, and floating points are just too slack.

APC
  • 144,005
  • 19
  • 170
  • 281
  • 1
    But it's already too late. The OP stated that the client stores the value in a `double`, so the precision has already been lost. – Oliver Charlesworth Jun 14 '13 at 12:19
  • @OliCharlesworth - the OP changed that after I posted this. Although I agree it does tend to invalidate my suggestion. – APC Jun 14 '13 at 13:00