Oracle NUMBER Data Types

Introduction: This is my study about number data types present
in Oracle database. I have used Oracle 10g (10.2.0.3) for my study:-

Data types INT, INTEGER and SMALLINT all uses NUMBER in Oracle, let us see how:-

Example # 1:

drop table tab1 purge;

create table tab2 (
I INT,
S SMALLINT,
IG INTEGER);

describe tab1

Name Null? Type
------------------ -------- ----------
I NUMBER(38)
S NUMBER(38)
IG NUMBER(38)

Example # 2: Let's see about other number data types:-

drop table tab2 purge;

create table tab2 (
X number,
NUM1 NUMBER,
FLOAT1 FLOAT,
BFLOAT BINARY_FLOAT,
BDOUBLE BINARY_DOUBLE);

describe tab2

Name Null? Type
----------------------- -------- -------------
X NUMBER
NUM1 NUMBER
FLOAT1 FLOAT(126)
BFLOAT BINARY_FLOAT
BDOUBLE BINARY_DOUBLE

declare
y number;
begin for a in 1..10 loop
y:=1/(a*100);
insert into tab2 values (a,y,y,y,y);
end loop;
commit;
end;
/

col NUM1 for .99999999999999999999
col FLOAT1 for .99999999999999999999
col BFLOAT for .99999999999999999999
col BDOUBLE for .99999999999999999999
set lines 120

select * from tab2;

X NUM1 FLOAT1 BFLOAT BDOUBLE
-- ---------------------- ---------------------- ---------------------- ------------
1 .01000000000000000000 .01000000000000000000 .00999999978000000000 .01000000000000000000 2 .00500000000000000000 .00500000000000000000 .00499999989000000000 .00500000000000000010 3 .00333333333333333333 .00333333333333333333 .00333333341000000000 .00333333333333333350 4 .00250000000000000000 .00250000000000000000 .00249999994000000000 .00250000000000000010 5 .00200000000000000000 .00200000000000000000 .00200000009000000000 .00200000000000000000 6 .00166666666666666667 .00166666666666666667 .00166666671000000000 .00166666666666666680 7 .00142857142857142857 .00142857142857142857 .00142857141000000000 .00142857142857142860 8 .00125000000000000000 .00125000000000000000 .00124999997000000000 .00125000000000000000 9 .00111111111111111111 .00111111111111111111 .00111111114000000000 .00111111111111111110 10 .00100000000000000000 .00100000000000000000 .00100000005000000000 .00100000000000000000
10 rows selected.

In the above, the NUM1 (which is using Number data type) and FLOAT1 (which is using FLOAT data type) preserves accuracy/scale than others.

Example # 3: Now, lets us try this

delete tab2;

declare
y number;
begin for a in 1..10 loop
y:=(a+(sqrt(a))/(a*100));
insert into tab2 values (a,y,y,y,y);
end loop;
commit;
end;
/

col NUM1 for 99.99999999999999999999999999999999999999
col FLOAT1 for 99.99999999999999999999999999999999999999

select NUM1,FLOAT1 from tab2;

NUM1 FLOAT1
------------------------------------------ ------------------------------------------ 1.01000000000000000000000000000000000000 1.01000000000000000000000000000000000000
2.00707106781186547524400844362104849039 2.00707106781186547524400844362104849040
3.00577350269189625764509148780501957456 3.00577350269189625764509148780501957460
4.00500000000000000000000000000000000000 4.00500000000000000000000000000000000000
5.00447213595499957939281834733746255247 5.00447213595499957939281834733746255250
6.00408248290463863016366214012450981899 6.00408248290463863016366214012450981900
7.00377964473009227227214516536234180061 7.00377964473009227227214516536234180060
8.00353553390593273762200422181052424520 8.00353553390593273762200422181052424520
9.00333333333333333333333333333333333333 9.00333333333333333333333333333333333330
10.00316227766016837933199889354443271853 10.00316227766016837933199889354443271900

10 rows selected.

select avg(NUM1) NUM1, avg(FLOAT1) FLOAT1 from tab2;

NUM1 FLOAT1
------------------------------------------ ------------------------------------------ 5.50502099789929266650050620329386725341 5.50502099789929266650050620329386725346

select sum(NUM1) NUM1, sum(FLOAT1) FLOAT1 from tab2;

NUM1 FLOAT1
------------------------------------------ ------------------------------------------ 55.05020997899292666500506203293867253408 55.05020997899292666500506203293867253460

select max(NUM1) NUM1, max(FLOAT1) FLOAT1 from tab2;

NUM1 FLOAT1
------------------------------------------ ------------------------------------------ 10.00316227766016837933199889354443271853 10.00316227766016837933199889354443271900

Example # 4: Now, lets us compare NUMBER and FLOAT data type and try this

drop table tab3 purge;

create table tab3 (
NUM1 NUMBER,
FLOAT1 FLOAT);

delete tab3;

insert into tab3 values (
1234567891234567891234567890123456789123.45,
1234567891234567891234567890123456789123.45);

col NUM1 for 9999999999999999999999999999999999999999.99
col FLOAT1 for 9999999999999999999999999999999999999999.99

select * from tab3;

NUM1
--------------------------------------------
FLOAT1
--------------------------------------------
1234567891234567891234567890123456789123.00
1234567891234567891234567890123456789100.00

In the above when comparing the NUM1 (NUMBER data type) and FLOAT1 (FLOAT data type), it appears that NUM1 (Number data type) preserves accuracy/scale than the other.

When default is used, NUMBER datatype can preserve values upto 40 bytes and FLOAT upto 38 bytes