NUMBER Data Type

Definition
The NUMBER data type in Oracle is used to store numeric values such as integers, floating-point, and decimal numbers. It is flexible and allows user-defined precision and scale.

Syntax
NUMBER[(precision [, scale])]

Parameters

• precision – total number of significant digits (1–38)
• scale – number of digits to the right of the decimal point (can be negative)

Explanation of Precision and Scale

• Precision: total number of digits
• Scale: digits after the decimal point
  Example: NUMBER(6,2) → 6 digits total, 2 after the decimal (e.g., 9999.99)

Valid Ranges

• Precision: 1 to 38
• Scale: -84 to 127
  If omitted: NUMBER can store any number up to 38 digits of precision

Storage and Internal Representation
Oracle stores numbers in variable-length format (1 to 22 bytes) depending on the number of digits.
Small integers use about 1–2 bytes, large numbers or decimals up to 22 bytes.

Examples

Example 1: Basic NUMBER Declaration
CREATE TABLE products (
product_id NUMBER,
price NUMBER(8,2),
quantity NUMBER(5)
);

Example 2: Inserting Data
INSERT INTO products VALUES (101, 1250.75, 50);
INSERT INTO products VALUES (102, 10.5, 5);

Example 3: Scale Behavior
CREATE TABLE test_num (amount NUMBER(5,2));
INSERT INTO test_num VALUES (123.456);
Stored as 123.46 because Oracle rounds to 2 decimal places.

Example 4: Negative Scale
CREATE TABLE salary (monthly_salary NUMBER(6,-2));
INSERT INTO salary VALUES (12345);
Stored as 12300 (rounded to hundreds).

Example 5: NUMBER Without Precision and Scale
CREATE TABLE numbers (any_number NUMBER);
Can store any numeric value up to 38 digits.

Default Values and Rounding Rules

• If precision and scale are omitted → unlimited precision (up to 38 digits)
• If only precision is specified → scale defaults to 0
• If value exceeds scale → rounded
• If value exceeds precision → ORA-01438 error

Example: Precision-Only
CREATE TABLE t (num NUMBER(5));
INSERT INTO t VALUES (123456); → error (exceeds 5 digits)

Common Variants and Synonyms

• NUMBER – general numeric type
• INTEGER – NUMBER(38,0)
• INT – NUMBER(38,0)
• SMALLINT – NUMBER(38,0)
• DECIMAL(p,s) – NUMBER(p,s)
• NUMERIC(p,s) – NUMBER(p,s)
• FLOAT(p) – NUMBER(p)

Example
CREATE TABLE test_types (
a INTEGER,
b FLOAT(10),
c DECIMAL(8,2)
);

Arithmetic Operations
You can use NUMBER columns in calculations:
SELECT product_id, price * quantity AS total_value FROM products;

Functions Commonly Used with NUMBER
ROUND(number, scale) – rounds number
TRUNC(number, scale) – truncates number
MOD(x, y) – remainder
ABS(number) – absolute value
POWER(x, y) – exponentiation
CEIL(number) – smallest integer ≥ number
FLOOR(number) – largest integer ≤ number

Performance and Storage Tips

• Use precision and scale to enforce data integrity
• Avoid NUMBER without precision in large tables
• For money, use NUMBER(12,2)
• For integer IDs, use NUMBER(10) or INTEGER
• Negative scales are rarely used

Examples Summary

• NUMBER – any number (e.g., 123456.789)
• NUMBER(5) – up to 5 digits (99999)
• NUMBER(5,2) – 3 before + 2 after decimal (999.99)
• NUMBER(6,-2) – rounded to hundreds (12300)
• INTEGER – whole number (42)

Key Points Summary

• Flexible numeric type supporting integers, floats, and decimals
• Precision = total digits; Scale = digits after decimal
• Default NUMBER allows up to 38 digits
• Oracle rounds if scale is exceeded, errors if precision exceeded
• Storage is variable (1–22 bytes)
• Best for IDs, money, quantities, and calculations