ORACLE 8 NUMERIC PROCESSING BUG?

From: Peter Hanlon <peter_at_hanlon.co.uk>
Date: Sun, 22 Aug 1999 21:35:40 GMT
Message-ID: <7ppqfc$rev$1_at_nnrp1.deja.com>



A change has been made to the way that Oracle 8 handles FLOAT columns. If you create a table with a FLOAT column and you describe the table, the column will be reported back as a NUMBER.

On the face of it this doesn't seem so terrible, except that the values that can be stored in a FLOAT are not the same as the values that can be stored in a NUMBER. For example a FLOAT(10) can only store 3/4 digits where as a NUMBER(10) can store 10 digits, so the two data types are not equal.

This may also not seem so terrible, except that inserting a value with more than 3/4 digits into a FLOAT(10) column is not an error, the value is simply truncated. This means that you think your data has been safely stored in the database because your insert statement has worked and your table said it contains a NUMBER(10) column, when in fact it has been corrupted.

The OCI describe function “odesc” also reports back that FLOAT columns are actually NUMBER’s and returns the wrong precision (the binary size of the float). This means that any programs using dynamic SQL functionality will also make the mistake of thinking they can insert up to 10 digits.

Another interesting feature is that if you alter a NUMBER(10) column to be a FLOAT(10) column it works but doesn’t truncate the values already stored in the table or raise an error reporting that the two data types are not compatible. If you later attempt to insert a ten digit number into the table, it gets truncated. I guess this is sort of correct but it makes debugging code a nightmare when your program used to work but doesn't anymore.

Example



SQL> create table test (col0 number(10));

Table created.

SQL> insert into test (col0) values (1111111111);

1 row created.

SQL> select col0 from test;

      COL0



1111111111

SQL> desc test

 Name                                      Null?    Type
 ----------------------------------------- --------

----------------------------
COL0 NUMBER(10)

SQL> select * from test;

      COL0



1111111111

SQL> alter table test modify col0 float(10);

Table altered.

SQL> insert into test (col0) values (1111111111);

1 row created.

SQL> select col0 from test;

      COL0



1111111111
1111000000

SQL> I haven’t found an answer to the problem other than don’t trust desc, always check the data type of a column in all_tab_columns and to be sure never use FLOAT.

Peter Hanlon.

Hanlon Consulting is a software company based in London developing Oracle software. Their main product, DataMill, is an Oracle population tool that exploits the unique flexibility of SQL, J/SQL and PL/SQL.

--

Peter Hanlon.....................Email :  pete_at_hanlon.co.uk
Hanlon Consulting Ltd............Phone : +44 (0)20 8391 4021
http://www.hanlon.co.uk/ ........Fax   : +44 (0)870 056 7283


Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
Received on Sun Aug 22 1999 - 23:35:40 CEST

Original text of this message