Database Reference
In-Depth Information
When you specify the scale of 2, at most three digits may be to the left of the decimal place and two to the right.
Hence that number does not fit. The
NUMBER(5,2)
column can hold all values between 999.99 and -999.99.
It may seem strange to allow the scale to vary from -84 to 127. What purpose could a negative scale fulfill? It
allows you to round values to the left of the decimal place. Just as the
NUMBER(5,2)
rounded values to the nearest .01,
so a
NUMBER(5,-2)
would round to the nearest 100, for example:
EODA@ORA12CR1> create table t ( msg varchar2(10), num_col number(5,-2) );
Table created.
EODA@ORA12CR1> insert into t (msg,num_col) values ( '123.45', 123.45 );
1 row created.
EODA@ORA12CR1> insert into t (msg,num_col) values ( '123.456', 123.456 );
1 row created.
EODA@ORA12CR1> select * from t;
MSG NUM_COL
---------- ----------
123.45 100
123.456 100
The numbers were rounded to the nearest 100. We still have five digits of precision, but there are now seven digits
(including the trailing two 0s) permitted to the left of the decimal point:
EODA@ORA12CR1> insert into t (msg,num_col) values ( '1234567', 1234567 );
1 row created.
EODA@ORA12CR1> select * from t;
MSG NUM_COL
---------- ----------
123.45 100
123.456 100
1234567 1234600
EODA@ORA12CR1> insert into t (msg,num_col) values ( '12345678', 12345678 );
insert into t (msg,num_col) values ( '12345678', 12345678 )
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
So, the precision dictates how many digits are permitted in the number after rounding, using the scale to
determine how to round. The precision is an integrity constraint, whereas the scale is an edit.
It is interesting and useful to note that the
NUMBER
type is, in fact, a variable length datatype on disk and will
consume between 0 and 22 bytes of storage. Many times, programmers consider a numeric datatype to be a
fixed-length type—that is what they typically see when programming with 2- or 4-byte integers and 4- or 8-byte floats.
