Oracle PL/SQL - PL SQL Data Type NUMBER type

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Data Type
  4. NUMBER type

Introduction

PL/SQL supports a large number of numeric data type variations.

NUMBER, BINARY_INTEGER/PLS_INTEGER and BINARY_FLOAT/BINARY_DOUBLE.

NUMBER is the most generic data type.

It is used to support all but the most intensive scientific calculations.

Numbers can have a maximum of 38 significant digits. The syntax is : 

declare
   variable1_nr NUMBER [(precision [, scale])];
   ...
  • Precision is the total number of digits in the number. The value of precision can be between 1 and 38.
  • Scale is the number of digits to the right of the decimal point. Scale might be negative. The value of scale can be between -84 and 127.

Both precision and scale are optional.

NUMBER data type is overloaded to include three different numeric groups:

  • Integers (you specify just precision) are between -10^38 and 10^38 not including either of the bounds.
  • Fixed-point values (you specify both precision and scale) are between -10^122 and 10^122, not including either of the bounds
  • Floating-point values (you don't specify anything) are between -10^130 and 10^130, not including either of the bounds.

To deal with numbers smaller than 10^-38 or larger than 10^38, you have to use scientific notation.

For example, 128000 should be written as 1.28E5. 

declare
     v1_nr NUMBER(10);   -- integer                                           
     v2_nr NUMBER(10,0); -- also integer                                      
     v3_nr NUMBER(5,5);  -- fixed point
     v4_nr NUMBER(5,-3); -- also fixed point
     v5_nr NUMBER;       -- floating point

Related Topics