Database Reference
In-Depth Information
Once again, the
NUM_COL
accurately represented the number, but the
FLOAT_COL
and
DBL_COL
did not. This does
not mean that the
NUMBER
type is able to store things with infinite accuracy/precision—just that it has a much larger
precision associated with it. It is easy to observe similar results from the
NUMBER
type:
EODA@ORA12CR1> delete from t;
1 row deleted.
EODA@ORA12CR1> insert into t ( num_col )
2 values ( 123 * 1e20 + 123*1e-20 ) ;
1 row created.
EODA@ORA12CR1> set numformat 999999999999999999999999.999999999999999999999999
EODA@ORA12CR1> select num_col, 123*1e20, 123*1e-20 from t;
NUM_COL
--------------------------------------------------
123*1E20
--------------------------------------------------
123*1E-20
--------------------------------------------------
12300000000000000000000.000000000000000000000000
12300000000000000000000.000000000000000000000000
.000000000000000001230000
As you can see, when we put together a very large number (
123*1e20
) and a very small number (
123*1e-20
),
we lost precision because this arithmetic requires more than 38 digits of precision. The large number by itself can be
faithfully represented, as can the small number, but the result of the larger plus the smaller cannot. We can verify this
is not just a display/formatting issue as follows:
EODA@ORA12CR1> select num_col from t where num_col = 123*1e20;
NUM_COL
--------------------------------------------------
12300000000000000000000.000000000000000000000000
The value in
NUM_COL
is equal to
123*1e20
, and not the value we attempted to insert.
NUMBER Type Syntax and Usage
The syntax for the
NUMBER
type is straightforward:
NUMBER( p,s )
P
and
S
are optional and are used to specify the following:
•
Precision,
or the total number of digits
: By default, the precision is 38 and has valid values in
the range of 1 to 38. The character
*
may be used to represent 38 as well.
•
Scale,
or the number of digits to the right of the decimal point
: Valid values for the scale are
-84 to 127, and its default value depends on whether or not the precision is specified. If no
precision is specified, then scale defaults to the maximum range. If a precision is specified,
then scale defaults to 0 (no digits to the right of the decimal point). So, for example, a column
defined as
NUMBER
stores floating-point numbers (with decimal places), whereas a
NUMBER(38)
stores only integer data (no decimals), since the scale defaults to 0 in the second case.
