How does FLOAT map/relate to NUMBER in Oracle 10g?

Question

What is the FLOAT data type in Oracle 10g and how does it relate to NUMBER?

The only reference I can find to FLOAT in the Oracle documentation is in the BINARY_DOUBLE section of this page: http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements001.htm#i140621 It seems to indicate that it stores a floating point number and allows you to specify bits of precision, but it doesn't reference the NUMBER type. The 11g docs don't mention FLOAT at all.

The book "Expert Oracle Database Architecture: Oracle Database 9i, 10g, and 11g Programming Techniques and Solutions, Second Edition" says:

In addition to the NUMBER, BINARY_FLOAT, and BINARY_DOUBLE types, Oracle syntactically supports the following numeric datatypes:

When I say "syntactically supports," I mean that a CREATE statement may use these datatypes, but under the covers they are all really the NUMBER type. ...

• FLOAT(p): Maps to the NUMBER type.

What I don't understand is how it maps to NUMBER.

NUMBER(p) allows me to specify precision but the scale defaults to 0. It seems like FLOAT(p) is mapping to NUMBER(decimal p, *), that is, fixed precision but variable scale, which is not something the NUMBER type allows as far as I can tell.

So, FLOAT is not just an alias but also provides behavior that NUMBER by itself does not offer?

Answer 1

The documentation in 10g is a bit unclear. It has improved quite a bit from 11.1 onwards:

http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements001.htm#sthref94

In summary, FLOAT is the same as NUMBER, with two differences

1. FLOAT cannot specify the scale
2. In FLOAT, the precision is given in binary bits, in NUMBER in decimal digits, so FLOAT(126) means 126 bits of precision, NUMBER(38) means 38 decimal digits of precision

EDIT Some examples show that a FLOAT is just a NUMBER in disguise.

CREATE TABLE t (
  n1 NUMBER(*,1),  f1 FLOAT(1), f2 FLOAT(2), f3 FLOAT(3),
  n2 NUMBER(*,2),  f4 FLOAT(4), f5 FLOAT(5), f6 FLOAT(6)
);
INSERT INTO t VALUES (1/3, 1/3, 1/3, 1/3, 1/3, 1/3, 1/3, 1/3);

SELECT n1, f1, f2, f3 FROM t;
  0.3 0.3 0.3 0.3

SELECT DUMP(n1), DUMP(f1), DUMP(f2), DUMP(f3) FROM t;
  Typ=2 Len=2: 192,31
  Typ=2 Len=2: 192,31
  Typ=2 Len=2: 192,31
  Typ=2 Len=2: 192,31

SELECT n2, f4, f5, f6 FROM t;
  0.33 0.33 0.33 0.33

SELECT DUMP(n2), DUMP(f4), DUMP(f5), DUMP(f6) FROM t;
  Typ=2 Len=2: 192,34
  Typ=2 Len=2: 192,34
  Typ=2 Len=2: 192,34
  Typ=2 Len=2: 192,34

Be careful, though, the conversion factor from Float's precision bits to Number's decimal digits is not 3, but around 3.32. To be exact digits = ceil(bits / log(2,10).