Datatypes - Expert Oracle Database Architecture - page 528

You should consider the precision and scale to be edits for your data—data integrity tools in a way. The precision
and scale do not affect at all how the data is stored on disk, only what values are permitted and how numbers are to
be rounded. For example, if a value exceeds the precision permitted, Oracle returns an error:
EODA@ORA12CR1> create table t ( num_col number(5,0) );
Table created.
EODA@ORA12CR1> insert into t (num_col) values ( 12345 );
1 row created.
EODA@ORA12CR1> insert into t (num_col) values ( 123456 );
insert into t (num_col) values ( 123456 )
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column
So, you can use the precision to enforce some data integrity constraints. In this case, NUM_COL is a column that is
not allowed to have more than five digits.
The scale, on the other hand, is used to control rounding of the number. 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 123.45
123.456 123.46
Notice how the number 123.456, with more than five digits, succeeded this time. That is because the scale we
used in this example was used to round 123.456 to two digits, resulting in 123.46, and then 123.46 was validated
against the precision, found to fit, and inserted. However, if we attempt the following insert, it fails because the
number 1234.00 has more than five digits in total:
EODA@ORA12CR1> insert into t (msg,num_col) values ( '1234', 1234 );
insert into t (msg,num_col) values ( '1234', 1234 )
*
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column