Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle Basics / Oracle NUMBER Data Type

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])]

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:

NUMBER

The following syntax defines a fixed-point number:

NUMBER(p,s)

To define an integer, you use the following form:

NUMBER(p)

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

NUMBER(p,0)

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

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) );

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);

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);

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

INSERT INTO number_demo VALUES(-10000);

Consider the following example:

INSERT INTO number_demo VALUES(9999.999);

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 typeOracle NUMBER data type
INTNUMBER(38)
SMALLINTNUMBER(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.

  • Was this tutorial helpful?
  • YesNo
Previous Oracle Data Types
Next Oracle FLOAT

Getting Started

  • What Is Oracle Database
  • Install Oracle Database Server
  • Download Oracle Sample Database
  • Create Oracle Sample Database
  • Connect To Oracle Database Server

Oracle Data Manipulation

  • SELECT
  • Oracle DUAL Table
  • ORDER BY
  • SELECT DISTINCT
  • WHERE
  • Table & Column Aliases
  • AND
  • OR
  • FETCH
  • BETWEEN
  • IN
  • LIKE
  • IS NULL
  • Joins
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • Self Join
  • GROUP BY
  • HAVING
  • UNION
  • INTERSECT
  • MINUS
  • GROUPING SETS
  • CUBE
  • ROLLUP
  • PIVOT
  • UNPIVOT
  • INSERT
  • INSERT INTO SELECT
  • INSERT ALL
  • UPDATE
  • DELETE
  • MERGE
  • Subquery
  • Correlated Subquery
  • EXISTS
  • NOT EXISTS
  • ANY
  • ALL

Oracle Data Types

  • Oracle Data Types
  • NUMBER
  • FLOAT
  • BINARY_FLOAT
  • CHAR
  • NCHAR
  • VARCHAR2
  • NVARCHAR2
  • DATE
  • INTERVAL
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE

Oracle Data Definition

  • CREATE TABLE
  • Identity Column
  • ALTER TABLE
  • ALTER TABLE ADD Column
  • ALTER TABLE MODIFY Column
  • Drop Columns
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME Table
  • Oracle Virtual Column

Oracle Constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • NOT NULL

Oracle Views

  • CREATE VIEW
  • DROP VIEW
  • Updatable Views
  • Inline Views
  • WITH CHECK OPTION

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.