For NUMBER, NUMERIC and DECIMAL columns the scale fixes the position of the least significant digit. If specified, it must be in the range of -84 (84 digits to the left of the decimal point) to 127 (127 digits to the right of the decimal point). Digits beyond the scale are rounded prior to storage. Of course, the scale is implicitly zero for INTEGER and SMALLINT columns. Similarly, it defaults to zero for NUMBER, NUMERIC and DECIMAL columns for which a precision has been specified and for unconstrained NUMERIC and DECIMAL columns, but it remains unset for unconstrained NUMBER columns.
For NUMBER, NUMERIC and DECIMAL columns the precision is the maximum number of significant decimal digits permitted. If it is specified, the precision must be in the range of 1 to 38 decimal digits. An ORA-01438 error is returned if an attempt is made to store a value with more digits left of the (possibly implied) scale than the specified precision. If a precision is not specified, stored values are truncated to the maximum precision supported by the implementation. Oracle's implementation allows for up to 40 decimal digits of precision (more). This is the implicit precision of INTEGER and SMALLINT columns, as well as that of unconstrained NUMBER, NUMERIC and DECIMAL columns.
As is evident from the USER_TAB_COLUMNS query above, Oracle uses its NUMBER datatype internally to represent all of these datatype specifications.
FLOAT, REAL and DOUBLE PRECISION columns differ only in that ANSI requires their precision to be specified in terms of binary bits, not decimal digits. The precision of FLOAT columns defaults to 126 bits, and the precision of REAL and DOUBLE PRECISION columns are fixed at 63 and 126 bits respectively. Oracle uses its NUMBER datatype internally to represent these datatype specifications as well. This can be demonstrated by inserting the same value into sample NUMBER and FLOAT columns and then using the dump function to verify that the datatype number and the bytes stored are identical.
SQL> insert into numbers (number_u, float_u) values (99.99, 99.99);
1 row created.
SQL> select dump(number_u), dump(float_u) from numbers;
Typ=2 Len=3: 193,100,100 Typ=2 Len=3: 193,100,100
Binary precision columns are only reported as FLOAT in the USER_TAB_COLUMNS family of views to preserve the semantics of their data precision.
It is done based on the fact that these are the only columns have a precision, but no scale.
This can be seen in the decode expression used to translate datatype number 2 into a
datatype name in the view text for say USER_TAB_COLUMNS in catalog.sql.
null, decode(c.precision#, null, 'NUMBER', 'FLOAT'),
decimal_precision = ceil(binary_precision * log(10, 2))For example, a FLOAT column with a binary precision of 2 bits, is implemented as a NUMBER column with 1 decimal digit of precision and no fixed scale. Thus the number 7, which requires 3 binary bits, will nevertheless be stored exactly, whereas the number 11, which has two decimal digits, will be rounded to 10 because only one decimal digit of precision is allowed.
SQL> create table float_check (f float(2)); Table created. SQL> insert into float_check values (7); 1 row created. SQL> insert into float_check values (11); 1 row created. SQL> select * from float_check; F ---------- 7 10
© Ixora Pty Ltd. All rights reserved.