G-2230: Try to use SIMPLE_INTEGER datatype when appropriate.

Minor

Efficiency

Restriction

ORACLE 11g or later

Reason

simple_integer does no checks on numeric overflow, which results in better performance compared to the other numeric datatypes.

With ORACLE 11g, the new data type simple_integer has been introduced. It is a sub-type of pls_integer and covers the same range. The basic difference is that simple_integer is always not null. When the value of the declared variable is never going to be null then you can declare it as simple_integer. Another major difference is that you will never face a numeric overflow using simple_integer as this data type wraps around without giving any error. simple_integer data type gives major performance boost over pls_integer when code is compiled in native mode, because arithmetic operations on simple_integer type are performed directly at the hardware level.

Example (bad)

create or replace package body constants_up is
   co_big_increase constant number(5,0) := 1;

   function big_increase return number is
   begin
      return co_big_increase;
   end big_increase;
end constants_up;
/

Example (good)

create or replace package body constants_up is
   co_big_increase constant simple_integer := 1;

   function big_increase return simple_integer is
   begin
      return co_big_increase;
   end big_increase;
end constants_up;
/