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 database):

  2     (val    NUMBER(38,0))
  3  /

Table created.

  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):

  2     (val    NUMBER)
  3  /

Table created.

  2  VALUES (99999999999999999999999999999999999999)
  3  /

1 row created.

  2  VALUES (999999999999999999999999999999999999999)
  3  /

1 row created.

  2  VALUES (9999999999999999999999999999999999999999)
  3  /

1 row created.

  2  VALUES (99999999999999999999999999999999999999999)
  3  /

1 row created.

  2  VALUES (999999999999999999999999999999999999999999)
  3  /

1 row created.

  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

  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:



  • 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.


2 thoughts on “Maximum NUMBER Precision

  1. Hi Mark – I’m wondering, and this may be such a beginner’s question, is NUMBER(38) inclusive of the signage (-/+) and the decimal? For example if I was sending a field in a delimited file would I specify 38 spaces for the location or 40 (1 for sign and 1 for decimal)?
    Which is the most accurate statement Number, max length 38 OR Number max length 40?

  2. Hi Mike, the sign and decimal point aren’t taken into account. So, you will be able to assign a value of -999.99 to a variable defined as NUMBER(5,2). If your file has a fixed width definition then you will need to include space for the extra characters. So, using our NUMBER(5,2) example, assuming positive and negative values are possible and there is a need for a decimal point then the file definition width would be 7 characters. If the database type has is just plain NUMBER with no precision declared then you could get up to 42 characters (40 digits plus one extra for the decimal point and sign) before the value is approximated down to 40 significant digits.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s