- /
- /
- /
- /
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×10126 (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