Oracle in a Nutshell

Numeric Datatypes

The Oracle database uses a standard, variable-length internal format for storing numbers. This internal format can maintain a precision of up to 38 digits.

The only numeric datatype within the database for Oracle8 and later versions is NUMBER. (However, see the later discussion of PL/SQL numeric types.) Declaring a column or variable as NUMBER will automatically provide a precision of 38 digits. The NUMBER datatype can also accept two qualifiers, as in:

column NUMBER(precision, scale)

The precision of the datatype is the total number of significant digits in the number and can be any integer up to 38; the default precision is 38. scale represents the number of digits to the right of the decimal point; the default scale is 0. If you assign a negative number to scale, Oracle will round the number up to the designated place to the left of the decimal point.

The NUMBER datatype is the only datatype that stores numeric values in Oracle8 and later versions. The ANSI datatypes of DECIMAL/DEC, NUMBER, INTEGER/INT, SMALLINT, FLOAT, DOUBLE PRECISION, and REAL are all stored in the NUMBER datatype. These are all defined datatypes in PL/SQL and other languages.

PL/SQL supports the BINARY_INTEGER, which stores signed integers between -2³¹ and 2³¹. This datatype has the following subtypes:

NATURAL

Non-negative integers

NATURALN

Non-negative integers and no NULLs

POSITIVE

Positive integers

POSITIVEN

Positive integers and no NULLs

SIGNTYPE

Accepts only -1, 0, or 1, which is useful in three-state ...