Number Data Type in Oracle – Understanding Precision and scale

When you create a table in Oracle database and assign the NUMBER data type to one or more columns, how does Oracle store the data for that column and how the minimum & maximum limit of the values that can be stored on that column is decided.

Let’s understand the NUMBER data type with precision and scale rules.

The NUMBER data type stores zero as well as positive and negative numbers. The range of NUMBER data type that can be stored in absolute value from 1.0 x 10130 to (but not including) 1.0 x 10126. Each NUMBER value requires from 1 to 22 bytes.

Before getting into the syntax for specifying NUMBER data type, let’s understand few basic concepts:

• Fixed-point number: A number representation where the decimal point is at a fixed position. The number of digits for the integer and fractional part is predetermined. Fixed point numbers have a set number of digits before and after the decimal point.

Example: If you store values with two decimal places, you can precisely represent 12.34, but not 12.345.

• Floating point number: A number representation where the decimal point can move, or “float” to be directly before or after the most significant digits. This is also known as scientific notation. A floating-point number can represent a much wider range of values by having a variable decimal point position.

Example: 2.34e-9 meters (2.34 nanometer) and 2.34e6 meters (2340 kilometers) can be represented in the same floating-point format. This would require a huge fixed-point number or separate format.

• Most significant digit: The Left-most Non-zero digit 
• Least Significant digit: The right-most known digit. 

The most significant digit and the least significant digits are shown in the diagram below where 1 is the most significant digit and 9 is the least significant digit.

In Oracle database, you can specify a fixed-point number using the form: NUMBER(p.s)

• ‘p’ is the precision which indicates the maximum number of total significant decimal digits. (From the most to the least significant digit)
• ‘s’ is the scale which indicates the number of digits from the decimal point to the least significant digit, The scale can range from -84 to 127.

Please see the image below for a better understanding.

• A positive scale indicates the maximum number of significant digits from the right of the decimal point to and including the least significant digit. (As shown in the image above)
• A negative scale indicates the number of significant digits from 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.

Example: if you store a number 123.89 to a column with data type NUMBER(3,-2), then the number will be stored as rounded up to the nearest 100, which is 100 in this case.

• If you specify a column as ‘NUMBER (p)’ (without any scale), then it is same as NUMBER(p,0). This means “NUMBER(p)” can be used to store only Integers.
• If you specify just NUMBER (without the precision or scale), then the maximum allowed precision and scale is assumed.

Let us see some of the examples with various precision and scale.

Actual Data	Column Data type	How the number is stored.
123.89	NUMBER	123.89
123.89	NUMBER(3)	124 (scale is zero)
123.89	NUMBER(3,2)	Value exceeds precision
123.89	NUMBER(4,2)	Value exceeds precision
123.89	NUMBER(5,2)	123.89
123.89	NUMBER(6,1)	123.9   (scale is 1, so, the number is rounded up to 1 decimal place)
123.89	NUMBER(6, -2)	100 (scale is negative (-2), so the number is rounded up to the nearest 100)

Sometime scale can be greater than precision. This is most used for ‘e’ notation (scientific notation) numbers or decimal numbers with no digit before the decimal point. When scale is greater than precision, then the precision specifies the maximum number of significant digits to the right of the decimal places and the scale specifies the maximum number of significant digits after the decimal point and after all the leading zeros. 

There must be “(scale – precision)” number of zeros after the decimal point, else you will see the ORA-01438 error related to the precision. If there are more non-zero digits than the scale, then the number is rounded up. Let’s see few examples.

Actual Data	Column Data type	How the number is stored.
0.01234	NUMBER(4, 5)	0.01234
0.00012	NUMBER(4, 5)	0.00012
0.000127	NUMBER(4, 5)	0.00013 (precision is 5,so the number is rounded up)
0.0000012	NUMBER(2,7)	0.0000012
0.00000123	NUMBER(2,7)	0.0000012
1.2e-4 (same as 0.00012)	NUMBER(2,5)	0.00012
1.2e-5 (same as 0.000012	NUMBER(2,5)	0.00001 (Rounded up as scale is 2 but precision is 5)

Let’s see some practical examples:

• Create a table ‘NUM1’ with few number columns.

SQL> CREATE TABLE num1 (n1 NUMBER(2), n2 NUMBER(5,2), n3 NUMBER(2,3));

Table created.

SQL> desc num1

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 N1                                                 NUMBER(2)

 N2                                                 NUMBER(5,2)

 N3                                                 NUMBER(2,3)

• Let’s insert record to this table with different possible values and test the output. (The insert is rolled back after every insert to limit the SELECT output to only 1 row)

Insert with normal values. The insert is successful.

SQL> INSERT INTO num1 VALUES(12, 123.45, 0.012);

1 row created.

SQL> select * from num1;

        N1         N2         N3

---------- ---------- ----------

12    123.45       .012

Insert values with higher number scale, Please notice that the data is rounded up for the decimal point.

SQL> INSERT INTO num1 VALUES(12.3, 123.456, 0.00123);

1 row created.

SQL> select * from num1;

        N1         N2         N3

---------- ---------- ----------

12    123.46       .001

Number with values with higher that expected precision. You will see ORA-01438 error.

SQL> INSERT INTO num1 VALUES(1, 12345.6789, 0.0023);

INSERT INTO num1 VALUES(1, 12345.6789, 0.0023)

                           *

ERROR at line 1:

ORA-01438: value 12345.6789 greater than specified precision (5, 2) for column

Help: https://docs.oracle.com/error-help/db/ora-01438/

SQL> INSERT INTO num1 VALUES(1, 12345, 0.0023);

INSERT INTO num1 VALUES(1, 12345, 0.0023)

                           *

ERROR at line 1:

ORA-01438: value 12345 greater than specified precision (5, 2) for column

Help: https://docs.oracle.com/error-help/db/ora-01438/

SQL> INSERT INTO num1 VALUES(1, 1234, 0.0023);

INSERT INTO num1 VALUES(1, 1234, 0.0023)

                           *

ERROR at line 1:

ORA-01438: value 1234 greater than specified precision (5, 2) for column

Help: https://docs.oracle.com/error-help/db/ora-01438/

123 is stored successfully as it can be written as 123.00 which satisfy the precision and scale.

SQL> INSERT INTO num1 VALUES(1, 123, 0.0023);

1 row created.

SQL> select * from num1;

        N1         N2         N3

---------- ---------- ----------

1        123       .002

Few examples where scale is longer than precision. There cannot be any digit to the left of the decimal point.

SQL> INSERT INTO num1 VALUES(1, 123, 1.234);

INSERT INTO num1 VALUES(1, 123, 1.234)

                                *

ERROR at line 1:

ORA-01438: value 1.234 greater than specified precision (2, 3) for column

Help: https://docs.oracle.com/error-help/db/ora-01438/

SQL> INSERT INTO num1 VALUES(1, 123, 1.23);

INSERT INTO num1 VALUES(1, 123, 1.23)

                                *

ERROR at line 1:

ORA-01438: value 1.23 greater than specified precision (2, 3) for column

Help: https://docs.oracle.com/error-help/db/ora-01438/

Scale is longer than the precision. It required at least 1 leading zero after the  decimal point.

SQL> INSERT INTO num1 VALUES(1, 123, 0.23);

INSERT INTO num1 VALUES(1, 123, 0.23)

                                *

ERROR at line 1:

ORA-01438: value .23 greater than specified precision (2, 3) for column

Help: https://docs.oracle.com/error-help/db/ora-01438/

SQL> INSERT INTO num1 VALUES(1, 123, 0.023);

1 row created.

SQL> select * from num1;

        N1         N2         N3

---------- ---------- ----------

         1        123       .023

SQL> INSERT INTO num1 VALUES(1, 123, 0.0023);

1 row created.

SQL> select * from num1;

        N1         N2         N3

---------- ---------- ----------

         1        123       .002

SQL> INSERT INTO num1 VALUES(1, 123, 0.01);

1 row created.

SQL> select * from num1;

        N1         N2         N3

---------- ---------- ----------

         1        123        .01

SQL> INSERT INTO num1 VALUES(1, 123, 0.000123);

1 row created.

SQL> select * from num1;

        N1         N2         N3

---------- ---------- ----------

         1        123          0

SQL> INSERT INTO num1 VALUES(1, 123, 0.00099);

1 row created.

SQL> select * from num1;

        N1         N2         N3

---------- ---------- ----------

         1        123       .001

SQL> INSERT INTO num1 VALUES(1, 123, 0.009);

1 row created.

SQL> select * from num1;

        N1         N2         N3

---------- ---------- ----------

         1        123       .009

SQL> INSERT INTO num1 VALUES(1, 123, 0.09);

1 row created.

SQL> select * from num1;

        N1         N2         N3

---------- ---------- ----------

         1        123        .09