Difference Between Number Datatypes

The number data types in oracle database are used to store numeric values/data.

There are BINARY_INTEGER, NUMBER and PLS_INTEGER data types which have small differences w.r.t PL/SQL code performance point of view.

Let's have a look on all three and find which one is better to use and why....

NUMBER Data type: This is the very common data type used to store numeric data (fixed-point and floating-point). Its magnitude range starts from 1E-130 .. 10E125. Oracle throws error if the value exceed or under the specified range.

The syntax is NUMBER(Precision,scale).

Precision: This is the value equal to the total no. of digits.
Scale: This is the value equal to the digits after the decimal point.

e.g. If you want to store a value 1234.56 Then you need to specify NUMBER(6,2).

BINARY_INTEGER Data type: This data type is used to store signed integers. Its magnitude range is -2**31 .. 2**31. BINARY_INTEGER values require less storage space than NUMBER values. This uses library arithmetic hence BINARY_INTEGER operations are slower than PLS_INTEGER operations. If the BINARY_INTEGER calculation overflows then no error/exception is raised.

PLS_INTEGER Data type: This data type is also used to store the signed integers. Its magnitude is similar to BINARY_INTEGER only. If the PLS_INTEGER calculation overflows then an exception is raised. PLS_INTEGER uses machine arithmetic hence operations are faster then BINARY_INTEGER.

NOTE: In new applications, always try to use the PLS_INTEGER as it is faster.