Oracle Database - NUMBER Data Type

Card Puncher Data Processing

ABout

The NUMBER data type stores zero as well as positive and negative fixed numbers with absolute values from 1.0 x 10-130 to (but not including) 1.0 x 10126. If you specify an arithmetic expression whose value has an absolute value greater than or equal to 1.0 x 10126, then Oracle returns an error. Each NUMBER value requires from 1 to 22 bytes.

Syntax

When you specify a fixed-point number use the following form to specify the precision and scale of the number:

NUMBER(precision, scale) 

See What are Fixed-point numbers (exact numbers)? for the definition of precision and scale.

Scale greater than precision

Scale can be greater than precision, most commonly when e notation is used. When scale is greater than precision, the precision specifies the maximum number of significant digits to the right of the decimal point. For example, a column defined as NUMBER(4,5) requires a zero for the first digit after the decimal point and rounds all values past the fifth digit after the decimal point.

How to specify:…

An integer

Specify an integer using the following form:

NUMBER(p) 

This represents a fixed-point number with precision p and scale 0 and is equivalent to NUMBER(p,0).

A floating-point number

Specify a floating-point number using the following form:

NUMBER 

The absence of precision and scale designators specifies the maximum range and precision for an Oracle number.

Storage of Scale and Precision

The table below show how Oracle stores data using different values for precision and scale.

Storage of Scale and Precision

Actual Data Specified As Stored As
123.89 NUMBER 123.89
123.89 NUMBER(3) 124
123.89 NUMBER(6,2) 123.89
123.89 NUMBER(6,1) 123.9
123.89 NUMBER(3) exceeds precision
123.89 NUMBER(4,2) exceeds precision
123.89 NUMBER(6,-2) 100
.01234 NUMBER(4,5) .01234
.00012 NUMBER(4,5) .00012
.000127 NUMBER(4,5) .00013
.0000012 NUMBER(2,7) .0000012
.00000123 NUMBER(2,7) .0000012
1.2e-4 NUMBER(2,5) 0.00012
1.2e-5 NUMBER(2,5) 0.00001

Documentation / Reference

  • Sql Developer Help





Discover More
Card Puncher Data Processing
Oracle - DataType

Each value manipulated by Oracle Database has a datatype. Large Object: Data Type Description VARCHAR2(size [BYTE | CHAR]) Variable-length character string having maximum length size...
Card Puncher Data Processing
Oracle Database - Floating-Point Number Datatype (BINARY_FLOAT, BINARY_DOUBLE)

Oracle provides two special datatype to store floating-point data: Datatype Storage Format BINARY_FLOAT single precision (32 bit) IEEE 754...
Card Puncher Data Processing
Oracle Database - Numeric Datatype

The following SQL data types store numeric data: NUMBER for integers and real numbers in a fixed-point or floating-point format BINARY_FLOAT and BINARY_DOUBLE for floating point Use the NUMBER...



Share this page:
Follow us:
Task Runner