NUMBER

(All limits valid for 11gR2 and 12C)

The NUMBER format is used both in SQL and PL/SQL. It can store numbers from 1.0×10^-130 to (exclusive) 1.0×10¹²⁶  (RDBMS version 11g).

NUMBER is defined as NUMBER[(precision[,scale])]

Precision defines the number of significant digits. Basically the number of digits that you can store (excluding leading zero’s). The scale is 1 to (inclusive) 38

Scale defines the number of digits right of the decimal separator. It can also have a negative value. In that case, numbers are rounded to the scale. The scale can range from -84 to 127 (both inclusive)

A NUMBER without precision or scale can store any number value within the maximum for the data type. It will act as a floating point value.

Consider the precision:

SQL> create table ot_num (nb number);

Table created.

SQL> set numwidth 50
SQL> insert into ot_num values (1234567890);

1 row created.

SQL> insert into ot_num values (1234567890123456789012345678901234567899);

1 row created.

SQL> insert into ot_num values (12345678901234567890123456789012345678901);

1 row created.

SQL> insert into ot_num values (123456789012345678901234567890123456789012345);

1 row created.

SQL> select * from ot_num;

NB
--------------------------------------------------
1234567890
1234567890123456789012345678901234567899
12345678901234567890123456789012345678900
123456789012345678901234567890123456789000000

Oracle can store very large numbers. But the precision is limited to 39 or 40 positions depending on the decimal point.

Consider the scale:

SQL> create table ot_num (nb number(3,2));

Table created.

SQL> insert into ot_num values (12.3);
insert into ot_num values (12.3)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

SQL> insert into ot_num values (1.23);

1 row created.

SQL> insert into ot_num values (0.123);

1 row created.

SQL> insert into ot_num values (0.126);

1 row created.

SQL> select nb from ot_num;

NB
--------------------------------------------------
1,23
,12
,13 

The precision of 3 allows only 3 significant digits. The scale indicates that 2 of the 3 digits are right of the decimal separator. So 12.3 is exceeding the precision.

0.123 and 0.126 are accepted. But since they exceed the scale, they are rounded to a scale of 2.

As mentioned, the scale can also be negative:

SQL> create table ot_num (nb number(5,-2));

Table created.

SQL> insert into ot_num values (123.12);

1 row created.

SQL> insert into ot_num values (12345);

1 row created.

SQL> select * from ot_num;

NB
--------------------------------------------------
100
12300