Figure 9-3. The effect of negative scale

Again we've used nine significant digits, but look where the decimal point is now! Rather than representing small values down to the hundred-billionth, the smallest value we can now represent precisely is 100 billion. Values less than 100 billion are rounded up or down to the nearest 100 billion, as illustrated in Table 9-3.

Table 9-3. Rounding of NUMBER(9,-11) values
Original value Rounded value that is actually stored
50,000,000,000.123 100,000,000,000.
49,999,999,999.999 0.
150,000,975,230,001 150,000,000,000,000
100,000,000,000,000,000,000 or 1 x 1020. Too large a number for the variable; requires a significant digit in the hundred-quintillion's position; results in an overflow error
-100,000,000,000,000,000,000 or -1 x 1020. Too small a number for the variable; requires a significant digit in the hundred-quintillion's position; results in an underflow error

As Figure 9-3 and Table 9-3 illustrate, negative scales allow us to represent some very large numbers, but only at the sacrifice of precision in the less-significant digits. Any absolute value less than 50 trillion is rounded to zero when stored in a NUMBER(9,-11) variable.

When declaring NUMBER variables using precision and scale, bear in mind that scale is optional and defaults to zero. For example, the following declarations are equivalent:

x NUMBER(9,0);x NUMBER(9);

Both of these declarations result in integer variables (i.e., zero digits past the decimal point) containing nine significant digits. The range of integer values that can be represented using nine significant digits is -999,999,999 through 999,999,999.

Given the wide range and versatility of the NUMBER datatype, it's no wonder that it's so widely used. Using simply NUMBER in your declarations, you can represent floating-point values. By including precision and scale, you can represent fixed-point decimal numbers. By setting scale to zero or omitting it entirely, you can represent integer values. One datatype covers all the bases.