Re: problems with float data using C/Oracle ... please help

dpunjabi_at_nyx10.cs.du.edu (DINESH PUNJABI) wrote:
>I am using a third party tool called persistence which interfaces with
>the oracle database.
>
>I am having problems inserting floating point numbers in an Oracle table:
>
>Here is what I enter in my sample program and here is what gets stored in
>the Oracle table:
>
>Input Ascii Data file:
>======================
>
>% cat Commission.Table.Data
>CommRate, Months
>51.2312345, 22
>32.1789, 12
>123.998, 2
>11.2223, 1
>
>Oracle Table:
>=============
>
>SQL> select * from corpapp_comm;
>
> COMMRATE MONTHS
> ---------- ----------
> 51.2312355 22
> 32.1789017 12
> 123.998001 2
> 11.2222996 1
>
>SQL> desc corpapp_comm;
> Name Null? Type
> ------------------------------- -------- ----
> COMMRATE NOT NULL FLOAT(126)
> MONTHS NOT NULL NUMBER(38)
>
>SQL>
>
>
>
>Essentially what I want is to store EXACTLY what is in the input data file.
>
>Your email responses will be highly appreciated .....
>
>Rgds
>
>Dinesh
>--
>
>Dinesh Punjabi
>dpunjabi_at_nyx.cs.du.edu Work: (303)-624-9812

C Floats have a precision of 6 digits while I believe C doubles have a precision of 13. Only 6 of the significant digits are getting stored in the C float when you read them in, rounding and other errors will ocurr. The numbers you have are not exactly representable by Floats or doubles. There are two simple solutions:

1.) Read the numbers in your C application as C char's. Let the database do the conversion to the Number type. Instead of: float x;
Use
char x[20];

and insert the char. The database will convert automatically.

2.) Use SQL*Load to load the data. It will not loose any digits. A sample control file is:

LOAD DATA
INFILE *
INTO TABLE corpapp_comm
FIELDS TERMINATED BY ','
(CommRate, Months)
begindata
51.2312345,22

32.1789,12
123.998,2
11.2223,1

I ran the above ctl file in my database and got:  

SQL> select * from corpapp_comm
  2 /  

  COMMRATE MONTHS
---------- ----------

51.2312345         22
   32.1789         12
   123.998          2
   11.2223          1

Thomas Kyte
tkyte_at_us.oracle.com   Received on Sat Jun 03 1995 - 00:00:00 CEST