Number Types-Datatypes
Oracle supports three native datatypes suitable for storing numbers:
•\ NUMBER: The Oracle NUMBER type is capable of storing numbers with an extremely large degree of precision—38 digits of precision, in fact. The underlying data format is similar to a packed decimal representation. The Oracle NUMBER type is a variable-length format from 0 to 22 bytes in length. It is appropriate for storing any number as small as 10e-130 and numbers up to but not including 10e126. This is by far the most common NUMBER type in use today.
•\ BINARY_FLOAT: This is an IEEE native single-precision floating-point number. On disk, it will consume 5 bytes of storage: 4 fixed bytes for the floating-point number and 1 length byte. It is capable of storing numbers in the range of ~ ± 1038.53 with six digits of precision.
•\ BINARY_DOUBLE: This is an IEEE native double-precision floating- point number. On disk, it will consume 9 bytes of storage: 8 fixed bytes for the floating-point number and 1 length byte. It is capable of storing numbers in the range of ~ ± 10308.25 with 13 digits of precision.
As you can see from this quick overview, the Oracle NUMBER type has significantly larger precision than the BINARY_FLOAT and the BINARY_DOUBLE types, but a much smaller range than the BINARY_DOUBLE. That is, you can store numbers very precisely with many significant digits in a NUMBER type, but you can store much smaller and larger numbers in the BINARY_FLOAT and BINARY_DOUBLE types.
As a quick example, we can create a table with the various datatypes in them and see what is stored given the same inputs:
$ sqlplus eoda/foo@PDB1
SQL> create table t( num_col number,float_col binary_float,dbl_col binary_double);Table created.
SQL> insert into t ( num_col, float_col, dbl_col )
values ( 1234567890.0987654321, 1234567890.0987654321,1234567890.0987654321 );1 row created.
SQL> set numformat 99999999999.99999999999 SQL> select * from t;NUM_COLFLOAT_COLDBL_COL
1234567890.09876543210 1234567940.00000000000 1234567890.09876540000
Note that the NUM_COL returns the exact number we provided as input. There are fewer than 38 significant digits in the input number (I supplied a number with 20 significant digits), so the exact number is preserved. The FLOAT_COL, however, using the BINARY_FLOAT type, was not able to accurately represent this number. In fact, it preserved only seven digits accurately.
The DBL_COL fared much better, accurately representing the number in this case out to 17 digits. Overall, though, this should be a good indication that the BINARY_FLOAT and BINARY_DOUBLE types will not be appropriate for financial applications! If you play around with different values, you’ll see different results:
SQL> delete from t;1 row deleted.
SQL> insert into t ( num_col, float_col, dbl_col )values ( 9999999999.9999999999, 9999999999.9999999999,9999999999.9999999999);1 row created.
SQL> select * from t;NUM_COL FLOAT_COL DBL_COL
9999999999.99999999990 10000000000.00000000000 10000000000.00000000000
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:
SQL> delete from t;1 row deleted.
SQL> insert into t ( num_col ) values ( 123 * 1e20 + 1231e-20 ) ; 1 row created. SQL> set numformat 999999999999999999999999.999999999999999999999999 SQL> select num_col, 1231e20, 1231e-20 from t;NUM_COL 1231E20
123*1E-20
12300000000000000000000.000000000000000000000000
12300000000000000000000.000000000000000000000000
.000000000000000001230000
As you can see, when we put together a very large number (1231e20) and a very small number (1231e-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:
SQL> 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.