Oracle NUMBER Data Type

Summary: in this tutorial, you will learn about the Oracle NUMBER data type and how to use it to define numeric columns for a table.

Introduction to Oracle NUMBER data type

The Oracle NUMBER data type is used to store numeric values that can be negative or positive. The following illustrates the syntax of the NUMBER data type:

NUMBER[(precision [, scale])]
Code language: SQL (Structured Query Language) (sql)

The Oracle NUMBER data type has precision and scale.

• The precision is the number of digits in a number. It ranges from 1 to 38.
• The scale is the number of digits to the right of the decimal point in a number. It ranges from -84 to 127.

For example, the number 1234.56 has a precision of 6 and a scale of 2. So to store this number, you need NUMBER(6,2).

Both precision and scale are in decimal digits and optional. If you skip the precision and scale, Oracle uses the maximum range and precision for the number.

For example, the following form defines a number that can store numeric values with the maximum range and precision:

NUMBERCode language: SQL (Structured Query Language) (sql)

The following syntax defines a fixed-point number:

NUMBER(p,s)Code language: SQL (Structured Query Language) (sql)

To define an integer, you use the following form:

NUMBER(p)Code language: SQL (Structured Query Language) (sql)

The above form represents a fixed-point number with precision p and a scale of zero, which is equivalent to the following:

NUMBER(p,0)
Code language: SQL (Structured Query Language) (sql)

Oracle allows the scale to be negative, for example, the following number will round the numeric value to hundreds.

NUMBER(5,-2)Code language: SQL (Structured Query Language) (sql)

Note that if you insert a number into a NUMBER(p,s) column and the number exceeds precision p, Oracle will issue an error. However, if the number exceeds the scale, Oracle will round the value.

Oracle NUMBER data type examples

The following statement creates a table named number_demo that consists of a numeric column:

CREATE TABLE number_demo ( 
    number_value NUMERIC(6, 2) 
);Code language: SQL (Structured Query Language) (sql)

The following INSERT statements insert three numbers into the number_demo table:

INSERT INTO number_demo
VALUES(100.99);

INSERT INTO number_demo
VALUES(90.551);

INSERT INTO number_demo
VALUES(87.556);Code language: SQL (Structured Query Language) (sql)

In this example:

• The first value was inserted successfully because the number was in the range defined for the column.
• The second value was rounded down and the third value was rounded up because the column accepted only numbers with two decimal point places.

The following example inserts the maximum and minimum values that the number_value can accept:

INSERT INTO number_demo
VALUES(9999.99);

INSERT INTO number_demo
VALUES(-9999.99);Code language: SQL (Structured Query Language) (sql)

The following example causes an error because the inserted value exceeds the precision defined for the column.

INSERT INTO number_demo
VALUES(-10000);Code language: SQL (Structured Query Language) (sql)

Consider the following example:

INSERT INTO number_demo
VALUES(9999.999);Code language: SQL (Structured Query Language) (sql)

In this example, the value was rounded up which resulted in a number that exceeded the precision defined for the column.

Oracle NUMBER data type aliases

Oracle contains a number of aliases that you can use to define numeric columns as shown in the following table:

Note that INT, SMALLINT, NUMERIC, and DECIMAL are just aliases. They are not the real data types. Internally, Oracle maps these aliases to the corresponding NUMBER data type.

In this tutorial, you have learned the Oracle NUMBER data type and how to use it to define numeric columns for a table.