A practical approach to understand Oracle number datatype

Oracle Database Version: 9iR2

Oracle number datatype houses fixed and floating point numbers. A number datatype can be decked by specifying p=precision (total number of digits) and s=scale(number of digits to the right of the decimal point).

If specifying, precision can go up to 38 digits.


SQL> create table table1(col1 number(39,2));
create table table1(col1 number(39,2))
*
ERROR at line 1:
ORA-01727: numeric precision specifier is out of range (1 to 38)



If precision is not specified, Number data type can house a value up to 9.99E125.

Max Value

SQL> create table table1(col1 number);

Table created.

SQL> insert into table1 values(9999999999999999999999999999
9999999999999999999999999999999999999999999999999999
999999999999999999999999999999999999999999999);

1 row created.

SQL> select length('999999999999999999999999999999999999
9999999999999999999999999999999999999999999999999999
9999999999999999999999999999999999999') Length from dual;

LENGTH
----------
125


Beyond Max Value

Wrote file afiedt.buf

1* insert into table1 values(999999999999999999999999999999
9999999999999999999999999999999999999999999999999999
99999999999999999999999999999999999999999991)
SQL> /
insert into table1 values(99999999999999999999999999999999
999999999999999999999999999999999999999999999999999
9999999999999999999999999999999999999999991)
*
ERROR at line 1:
ORA-01426: numeric overflow



1* select length('99999999999999999999999999999999999999
999999999999999999999999999999999999999999999999999
9999999999999999999999999999999999991') Beyond Length from
dual
SQL> /

BEYOND LENGTH
----------
126

SQL>


If specifying, scale can be in the range (-84 to 127).


SQL> create table table1(col1 number(7,-88));
create table table1(col1 number(7,-88))
*
ERROR at line 1:
ORA-01728: numeric scale specifier is out of range (-84 to 127)


SQL> create table table1(col1 number(7,127));

Table created.

SQL> create table table1(col1 number(7,128));
create table table1(col1 number(7,128))
*
ERROR at line 1:
ORA-01728: numeric scale specifier is out of range (-84 to 127)



Let us take p=3 and s=1. Out of 3 digits 1 digit can come to the left of decimal point. Thus only 2 digits can come to the right of decimal point.

SQL> create table table1(col1 number(3,1));

Table created.

If you specify a value .26 on right side, then Oracle will round it to .3 . You can insert a two digit positive value. Oracle takes (p-s)=(3-1) = 2 digits on right side of decimal point. You cannot store 126.55555 value in this case.

SQL> insert into table1 values(12);

1 row created.

SQL> insert into table1 values(12.1);

1 row created.

SQL> insert into table1 values(12.1555); // .1555 will be rounded to .2

1 row created.

SQL> select * from table1;

COL1
----------
12
12.1
12.2

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

SQL> insert into table1 values(100); // Only two digits allowed before decimal
insert into table1 values(100)
*
ERROR at line 1:
ORA-01438: value larger than specified precision allows for this column

Let us take now p=7 and s=-9. If negative scale value is specified then digits on right side of the decimal point will be rounded. Anything after the decimal will be ignored.

SQL> create table table1(col1 number(7,-9));

Table created.

SQL> insert into table1 values(1234567.12); // 7 digits on left

1 row created.

SQL> select * from table1;

COL1
----------
0

SQL> insert into table1 values(12345678.12); // 8 digits on left

1 row created.

SQL> select * from table1;

COL1
----------
0
0

SQL> insert into table1 values(123456781.12); // 9 digits

1 row created.

SQL> select * from table1;

COL1
----------
0
0
0

SQL> insert into table1 values(1234567812.12); // 10 digits on left

1 row created.

SQL> select * from table1;

COL1
----------
0
0
0
0
1000000000

SQL> insert into table1 values(1734567812.12); // 10 digits on left, should round to 2000000000

1 row created.

SQL> select * from table1;

COL1
----------
0
0
0
0
1000000000
2000000000

6 rows selected.

SQL>


How many digits can be there in this case ? (p-s) = (7-(-9))=16 digits can be stored on right side.


SQL> insert into table1 values(17345678125.12);

1 row created.

SQL> insert into table1 values(173456781255555.12);

1 row created.

SQL> insert into table1 values(1734567812555555.12);

1 row created.

SQL> select length('1734567812555555') from dual;

LENGTH('1734567812555555')
--------------------------
16

SQL>

Increasing one more digit on left side will lead to a complain as follows.

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


As an another example, consider p=3 and s=-1.

SQL> create table table1(col1 number(3,-1));

Table created.

SQL> insert into table1 values(1.2);

1 row created.

SQL> insert into table1 values(99.2);

1 row created.

SQL> select * from table1;

COL1
----------
0
100

SQL> insert into table1 values(999.2);

1 row created.

SQL> select * from table1;

COL1
----------
0
100
1000

Can it house a value of 9999.2 ? No, because as scale is negative, it will try to round 9999 value. After rounding result will be 10000, which is a five digit number. It cannot be housed, because total digits can be p-s=3-(-1)=4.

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


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


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


Can it house a value of 9498 ? Yes as follows.


SQL> insert into table1 values(9498); //As value is below 9500 no need to round

1 row created.

Conclusion: Various examples have been taken into consideration for understanding number data type. This practice will give more insight for beginners as they always confuse with precision and scale attributes of number data types. I welcome any comment on this.