2

A string value 58.83 is changed to 56,829999999999998 when inserted into a Oracle database FLOAT(126) column.

We are using:

  • .NET Entity Framework 6,
  • Devart dotConnect for Oracle Professional version 8.4.171.0 and
  • Oracle 11g Enterprise Edition Release 11.2.0.3.0.

The string gets inserted within a CLOB field in Oracle then transformed to into a decimal in C# (the value still reads 58.83 after conversion to a decimal) before finally being inserted in a FLOAT field via Devart. The observed value stored in the column is 56,829999999999998.

When the value is read from the database it is still the wrong value.

What is causing the value to change from 58.83 to 56,829999999999998, Entity Framework, Devart or Oracle and how can we correct this behaviour?

UPDATE When testing from SQL Developer the numbers get inserted correctly.

The solution for the problem is to indeed, as comments suggested, change the data type to Number. I set the precision to * and scale (digits to the right of the decimal point) as 10.

Oracle docs state "This data type should never be used for precise values, such as currency."

This post decimal(s,p) or number(s,p)? helped explaining the difference between Number and Decimal data types when deciding an alternative to using a Float.

Community
  • 1
  • 1
Deakus
  • 51
  • 4
  • 5
    [What Every Computer Scientist Should Know About Floating-Point Arithmetic](http://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html). Simple answer Float is inaccurate datatype use `NUMERIC/DECIMAL` instead. – Lukasz Szozda Dec 18 '15 at 10:52
  • I marked the question as a duplicate; please follow the link to the other question for a thorough explanation of why this happens. The solution is not to use the FLOAT data type but another one, as suggested by lad2025. – Heinzi Dec 18 '15 at 11:02
  • Not seeing this problem: `CREATE TABLE T ( val FLOAT(126) );`, `INSERT INTO T VALUES ( '58.83' );` then `SELECT * FROM T;` outputs `58.83` on Oracle 11.2.0.1.0 – MT0 Dec 18 '15 at 11:06
  • @Heinzi You're right that the underlying problem is to do with using a float and yes a solution to the problem is to use another data type such as Number but I disagree with the question being a duplicate. My problem distinctly mentions the technologies used and as I and MT0 discovered when you run the SQL command directly, from SQL Developer for example, the values get inserted correctly. – Deakus Dec 18 '15 at 14:17
  • @Deakus: I see your point, I'll reopen the question (for reference: [this was the proposed duplicate](http://stackoverflow.com/questions/588004/is-floating-point-math-broken)). I suggest that you add the text that you have updated your question with as an answer. – Heinzi Dec 18 '15 at 14:34

0 Answers0