In article <7ppqhk$rfd$1_at_nnrp1.deja.com>,
pete_at_hanlon.co.uk wrote:
> 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.
>
I can see how the apparent change in handling of the column type can
cause some major grief; have you made a call to support about this? I
do believe that internally Oracle ver 7+ has always stored all number
types as an Oracle number datatype. This does not change the problem
you described so well.
--
Mark D. Powell -- The only advice that counts is the advice that
you follow so follow your own advice --
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
Received on Mon Aug 23 1999 - 08:20:56 CDT
