"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.

**Update For DB2**

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