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

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

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:

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

The following syntax defines a fixed-point number:

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

To define an integer, you use the following form:

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

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

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

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

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

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 s, 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 for define numeric columns as shown in the following table:

ANSI data type | Oracle NUMBER data type |
---|---|

INT | NUMBER(38) |

SMALLINT | NUMBER(38) |

NUMBER(p,s) | NUMBER(p,s) |

DECIMAL(p,s) | NUMBER(p,s) |

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.