NUMBER Data Type

The Oracle NUMBER data type is one of the most frequently used data types in Oracle databases. It is a versatile numeric type that can store both integers and floating-point numbers, and it is highly customizable. Here are some detailed notes about the NUMBER data type:

1. Syntax of NUMBER Data Type

The general syntax for declaring a column with the NUMBER data type is:

NUMBER(p, s)

Where:

• p is the precision (the total number of digits that can be stored).
• s is the scale (the number of digits to the right of the decimal point).

2. Precision and Scale

• Precision (p): The maximum total number of digits that can be stored, both to the left and right of the decimal point. The precision can be between 1 and 38 digits. If precision is not specified, the default value is 38.
• Scale (s): The number of digits that can be stored to the right of the decimal point. The scale can range from 0 to p. If scale is not specified, the default value is 0, meaning the column will only store whole numbers (integers).

For example:

• NUMBER(5, 2) can store numbers up to 999.99, meaning it has a total precision of 5 digits with 2 digits to the right of the decimal point.
• NUMBER(7, 0) can store integers up to 9999999, meaning it has a precision of 7 digits and no decimal places.

3. Default Behavior

If you create a column with just NUMBER, it assumes a precision of 38 and a scale of 0. For example:

CREATE TABLE my_table (

    my_column NUMBER

);

In this case, my_column will store numbers with a maximum precision of 38 digits, with no decimal places.

4. Range of Values

• The maximum precision for a NUMBER column in Oracle is 38 digits.
• The minimum scale can be 0 (for integers).
• The range of values depends on the specified precision and scale. For example:
• NUMBER(5, 2) can store values between -999.99 and 999.99.
• NUMBER(7, 0) can store values between -9999999 and 9999999.
• For floating-point values, you can have both large and small numbers, including positive and negative values.

5. Storage of NUMBER

The storage size of a NUMBER column depends on the precision of the number being stored:

• Oracle stores the NUMBER data type using a variable-length format, where the storage required depends on the precision.
• The storage size typically ranges from 1 to 22 bytes, depending on the number of digits in the column value.

6. Behavior with Large Values

• When a NUMBER column is defined with a large precision but no scale, it behaves like a regular integer column.
• When a scale is provided, the column can store decimal values.
• Oracle uses scientific notation to store very large or very small numbers when the scale or precision exceeds a normal range.

7. Comparison with Other Numeric Data Types

Oracle provides other numeric data types, including:

• INTEGER: It is essentially an alias for NUMBER with a precision of 38 and scale of 0.
• FLOAT: Another alias for NUMBER, but it typically defaults to NUMBER(126) for floating-point precision.
• BINARY_FLOAT and BINARY_DOUBLE: These types store floating-point numbers with binary precision, optimized for performance but with limited precision compared to NUMBER.

8. Examples of Using NUMBER

Here are a few examples to show how you can define and use the NUMBER data type:

• Creating a table with different precisions and scales:

CREATE TABLE financial_data (

    salary NUMBER(8, 2),         -- Can store values like 123456.78

    age NUMBER(3),              -- Can store integer values up to 999

    tax_rate NUMBER(5, 4)       -- Can store values like 0.1234 (up to 4 decimal places)

);

• Inserting values:

INSERT INTO financial_data (salary, age, tax_rate)

VALUES (50000.50, 30, 0.0789);

• Selecting data:

SELECT salary, age, tax_rate FROM financial_data;

9. Handling Overflow and Rounding

• When inserting a value into a NUMBER column, if the value exceeds the specified precision or scale, an error will be raised. For example, if you try to insert 123.456 into a column defined as NUMBER(5, 2), Oracle will raise an error because the precision of 5 can only accommodate two decimal places.
• Oracle uses rounding when necessary, such as when inserting a value that has more digits than the allowed scale. The number will be rounded to the nearest valid value.

10. Performance Considerations

• For high-precision calculations, the NUMBER type can sometimes incur a performance overhead due to the storage format and the additional computation required for large numbers.
• In cases where you need high-performance calculations and don't need excessive precision, consider using BINARY_FLOAT or BINARY_DOUBLE.

11. Advantages of Using NUMBER

• Flexibility: It supports both integers and floating-point numbers.
• Dynamic Storage: It allows for precise control over the storage of numeric values with variable precision and scale.
• Range: It has a large range, capable of storing very small and very large numbers.

Conclusion

The NUMBER data type in Oracle is extremely flexible and can store both integer and floating-point numbers with high precision. Understanding how to use precision and scale effectively helps in creating efficient, accurate data models.