"It stores slightly imprecise representations of real numbers as binary fractions at the hardware level"
http://www.sybase.com/detail?id=20313
Oracle has two data types, FLOAT and BINARY_FLOAT.
http://stackoverflow.com/questions/332492/oracle-floats-vs-number
- FLOAT is really a decimal data type with exact values (basically it is a NUMERIC)
- BINARY_FLOAT is a binary data type which better maps to Sybase FLOAT data type
If you migrate from Sybase FLOAT to Oracle BINARY_FLOAT then any value inserted will be treated as a binary imprecise number, just like Sybase.
SYBASE
drop table testfloat
go
create table testfloat (floatcol float)
go
insert into testfloat values(1.005)
go
insert into testfloat values(1.0049999999999999)
go
select * from testfloat --both values are displayed as 1.005
go
select * from testfloat where floatcol = 1.005 --returns both rows
go
select * from testfloat where floatcol = 1.0049999999999999 --returns both rows
go
select * from testfloat where floatcol = 1.0049999999999998 --returns both rows
go
select * from testfloat where floatcol = 1.0049999999999997 --returns NO rows
go
select * from testfloat where floatcol = 1.004999994--returns NO rows
go
select * from testfloat where floatcol = 1.004999493 --returns NO rows
go
So it looks like Sybase stores 1.005 as 1.0049999999999999, but displays it to the clients (like isql, jdbc) as 1.005
A comparison can be made with the literal 1.005 (though not recommended as float should never be compared in this way)
ORACLE
DROP table testfloat;
create table testfloat(floatcol float, binaryfloatcol binary_float ,varchar2floatcol varchar2(100) );
insert into testfloat values(1.005,1.005,'1.005') ;
insert into testfloat values(1.0049999999999999,1.0049999999999999,'1.0049999999999999');
select * from testfloat; --both values are displayed as 1.005 for BINARY_FLOAT and a mix as FLOAT
select * from testfloat where floatcol = 1.005; --returns one row
select * from testfloat where floatcol = 1.0049999999999999 ; --returns one row
select * from testfloat where floatcol = 1.0049999999999998 ; --returns NO rows
select * from testfloat where floatcol = 1.0049999999999997 ; --returns NO rows
select * from testfloat where binaryfloatcol = 1.005; --returns two rows
select * from testfloat where binaryfloatcol = 1.0049999999999999 ; --returns two rows
select * from testfloat where binaryfloatcol = 1.0049999999999998 ; --returns two rows
select * from testfloat where binaryfloatcol = 1.0049999999999997 ; --returns two rows
select * from testfloat where binaryfloatcol = 1.004999994 ; --returns two rows
select * from testfloat where binaryfloatcol = 1.004999493 ; --returns no row
Oracle stores FLOAT values as explicit numbers whereas BINARY_FLOAT values are not as precise. Oracle also displays 1.005 for both values in BINARY_FLOAT.
Conclusion
From the above test cases it looks like Oracle BINARY_FLOAT is a much better match than Oracle FLOAT for Sybase FLOAT, but it should be noted that since Sybase FLOAT and Oracle BINARY_FLOAT are not precise numbers , comparing equivalence is not identical between databases.
floats , be they Sybase FLOATs or Oracle BINARY_FLOATs should never be directly compared, rather a range of values should be matched.
Notes
Sybase BCP dumps out both floats as 1.0049999999999999
JDBC query against Sybase returns both floats as 1.005.
So during a SQL Developer data move, online will insert 1.005 , but offline will insert 1.0049999999999999. This doesn't really matter if the Oracle data type is BINARY_FLOAT, but it does if you use Oracle FLOAT data type.
