Storing Numeric Data

What Are the Numeric Datatypes?

The following SQL datatypes store numeric data:

• NUMBER

• BINARY_FLOAT

• BINARY_DOUBLE

Use the NUMBER datatype to store integers and real numbers in a fixed-point or floating-point format. Numbers using this datatype are guaranteed to be portable among different Oracle database platforms. For nearly all cases where you need to store numeric data, you would use the NUMBER datatype. When defining numeric data, you can use the precision option to set the maximum number of digits in the number, and the scale option to define how many of the digits are to the right of the decimal point. For example, a field to hold the salary of an employee can be defined as NUMBER(8,2), providing 6 digits for the primary unit of currency (dollars, pounds, marks, and so on) and two digits for the secondary unit (cents, pennies, pfennigs, and so on).

Oracle Database XE provides the numeric BINARY_FLOAT and BINARY_DOUBLE datatypes exclusively for floating-point numbers. They support all of the basic functionality provided by the NUMBER datatype. However, while the NUMBER datatype uses decimal precision, BINARY_FLOAT and BINARY_DOUBLE datatypes use binary precision. This enables faster arithmetic calculations and usually reduces storage requirements.

Using the NUMBER Datatype

The NUMBER datatype 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 10¹²⁶. If you specify an arithmetic expression whose value has an absolute value greater than or equal to 1.0 x 10¹²⁶, then Oracle Database XE returns an error.

The NUMBER datatype can be specified with a precision (p) and a scale (s) designator. Precision is the total number of significant decimal digits, where the most significant digit is the left-most, nonzero digit, and the least significant digit is the right-most, known digit. Scale is the number of digits from the decimal point to the least significant digit. The scale can range from -84 to 127. For examples, see Table: Storage of Scale and Precision.

You can specify a NUMBER datatype as follows:

• NUMBER(p) for an integer

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

• NUMBER(p, s) for a fixed-point number

  This explicitly specifies the precision (p) and scale (s). It is good practice to specify the scale and precision of a fixed-point number column for extra integrity checking on input. Specifying scale and precision does not force all values to a fixed length. If a value exceeds the precision, then Oracle Database XE returns an error. If a value exceeds the scale, then Oracle Database XE rounds it.

• NUMBER for a floating-point number

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

Table: Storage of Scale and Precision show how Oracle Database XE stores data using different values for precision and scale. Note that the values are rounded to the specified scale.

Using Floating-Point Number Formats

The BINARY_FLOAT and BINARY_DOUBLE datatypes store floating-point data in the 32-bit IEEE 754 format and the double precision 64-bit IEEE 754 format respectively. Compared to the Oracle NUMBER datatype, arithmetic operations on floating-point data are usually faster for BINARY_FLOAT and BINARY_DOUBLE. High-precision values require less space when stored as BINARY_FLOAT and BINARY_DOUBLE datatypes.

The BINARY_FLOAT datatype has a maximum positive value equal to 3.40282E+38F and the minimum positive value equal to 1.17549E-38F.

The BINARY_DOUBLE datatype has a maximum positive value equal to 1.79769313486231E+308 and the minimum positive value equal to 2.22507485850720E-308.