FLOAT data type migration

Sybase FLOAT is generally used to save non integer numbers like fractions where no number of precision can hold the exact value. Sybase FLOATs do not store an exact value
"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 FLOAT any value inserted will be treated as a specific explicit numeric, which will not behave like Sybase FLOAT.
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.


Update For DB2
DB2 behaves the exact same way as Sybase. So DB2 FLOAT should also be converted to Oracle BINARY_FLOAT.