Losing Precision when Converting Character String to Float (Doc ID 553827.1)

Last updated on AUGUST 08, 2017

Applies to:

SQL*Plus - Version 9.2.0.5 to 10.2.0.3 [Release 9.2 to Oracle10g]
Information in this document applies to any platform.
This problem can occur on any platform.
***Checked for relevance on 30-Sep-2013***

Symptoms


After converting a character string that represents a number by using the to_number conversion to store it in a float (n) datatype, querying up the float datatype via a SQL statement (i.e. select) loses precision. 

The maximum number of digits (including the decimal) returned is 10.  There are no errors.

Example:

SQL> CREATE TABLE floattest (record_id NUMBER(10), floatie FLOAT(126));

SQL> insert into floattest values (1, to_number('4.6127659699012496'));

SQL> insert into floattest values (2, to_number('999.01234567890123456789'));

SQL> select * from floattest;

RECORD_ID FLOATIE
---------- ----------
2 999.012346
1 4.61276597

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms