The NUMBER Type

The NUMBER datatype is by far the most common numeric datatype you'll encounter in the world of Oracle and PL/SQL programming. NUMBER is the only numeric datatype supported directly by the Oracle database engine. In fact, the importance of NUMBER relative to the other numeric types is so great that you could probably go through your entire career using only NUMBER in your PL/SQL programs.

Use the NUMBER datatype to store integer, fixed, or floating-point numbers of just about any size, up to a maximum of 38 significant digits. However, you can set an assumed decimal point anywhere from 127 positions to the left (scale of 127) through 84 positions to the right (scale of -84) of those 38 significant digits. Effectively, then, the NUMBER type supports the following range of absolute values:

1x10^-127 through 9.9999999999999999999999999999999999999x10⁺¹²¹ (38 nines)

This range is what we came up with in our testing using Oracle9i Release 1. However, in the Oracle9i SQL Reference, Oracle specifies the range as 1.0 x 10^-130 through 9.9...9 x 10¹²⁵ (38 nines).

The simplest way to declare a NUMBER variable is simply to specify the keyword NUMBER:

DECLARE x NUMBER;

Such a declaration results in a floating-point NUMBER. Oracle will allocate space for up to the maximum of 38 digits, and the decimal point will float to best accommodate whatever values you assign to the variable.

Generally, when you declare a variable of type NUMBER, you also specify the variable's precision and scale, as follows:

NUMBER ( precision , scale )

Such a declaration results in a fixed-point number. The precision is the total number of significant digits that the number contains. The amount of memory that Oracle sets aside for a NUMBER directly relates to the precision you specify. The scale dictates the number of digits to the right or left of the decimal point, and also affects the point at which rounding occurs. Both the precision and the scale values must be literal, integer values; you cannot use variables or constants in the declaration. Legal values for precision range from 1 to 38, and legal values for scale range from -84 to 127.

When declaring fixed-point numbers, the value for scale is usually less than the value for precision. For example, you might declare a variable holding a monetary amount as NUMBER(9,2). Figure 9-1 shows how to interpret such a declaration.