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 188.8.131.52 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:
- 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.
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?
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.