Maximum NUMBER Precision

What’s the maximum precision for the NUMBER data type? For many years I believed it to be 38. Afterall, it’s in the documentation under the Datatype Limits of the Reference manual:

38 significant digits

We can easily test this out (the following on a 12.1.0.2 database):

SQL>CREATE TABLE table_a
  2     (val    NUMBER(38,0))
  3  /

Table created.

SQL>CREATE TABLE table_b
  2     (val    NUMBER(39,0))
  3  /
   (val    NUMBER(39,0))
                  *
ERROR at line 2:
ORA-01727: numeric precision specifier is out of range (1 to 38)

and see that we cannot create a NUMBER (39,0) column. However, what happens if we leave the precision off the data type and insert some really big numbers into it (38 to 42 digits):

SQL>CREATE TABLE table_c
  2     (val    NUMBER)
  3  /

Table created.

SQL>INSERT INTO table_c
  2  VALUES (99999999999999999999999999999999999999)
  3  /

1 row created.

SQL>INSERT INTO table_c
  2  VALUES (999999999999999999999999999999999999999)
  3  /

1 row created.

SQL>INSERT INTO table_c
  2  VALUES (9999999999999999999999999999999999999999)
  3  /

1 row created.

SQL>INSERT INTO table_c
  2  VALUES (99999999999999999999999999999999999999999)
  3  /

1 row created.

SQL>INSERT INTO table_c
  2  VALUES (999999999999999999999999999999999999999999)
  3  /

1 row created.

SQL>COMMIT
  2  /

Commit complete.

All values were accepted by the INSERT statements so let’s now see what we’ve ended up with in our table:

SQL>COLUMN VAL FORMAT 9999999999999999999999999999999999999999999

SQL>SELECT val
  2  ,      LENGTH(TO_CHAR(val)) AS length_val
  3  FROM   table_c
  4  /

                                         VAL LENGTH_VAL
-------------------------------------------- ----------
      99999999999999999999999999999999999999         38
     999999999999999999999999999999999999999         39
    9999999999999999999999999999999999999999         40
  100000000000000000000000000000000000000000         40
 1000000000000000000000000000000000000000000         40

It would appear that we can actually get 40 digits of precision into a NUMBER data type even though we cannot define it to be NUMBER(40,0). After 40 digits Oracle approximates the number, using 40 significant digits.

A careful read of the Oracle SQL Reference documentation confirms this behaviour:

NUMBER(p,s)

where:

• p is the precision, or the maximum number of significant decimal digits, where the most significant digit is the left-most nonzero digit, and the least significant digit is the right-most known digit. Oracle guarantees the portability of numbers with precision of up to 20 base-100 digits, which is equivalent to 39 or 40 decimal digits depending on the position of the decimal point.
• s is the scale, or the number of digits from the decimal point to the least significant digit. The scale can range from -84 to 127.
  • Positive scale is the number of significant digits to the right of the decimal point to and including the least significant digit.
  • Negative scale is the number of significant digits to the left of the decimal point, to but not including the least significant digit. For negative scale the least significant digit is on the left side of the decimal point, because the actual data is rounded to the specified number of places to the left of the decimal point. For example, a specification of (10,-2) means to round to hundreds.

Interesting to know. However, NUMBER(38) is going to be satisfactory for virtually all practical purposes… except if you need to store an IPv6 address in decimal form.

Advertisement