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

Critical

Efficiency

Reason

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

With Oracle Database 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)

declare
   l_result       number(9,0)          := 0;   -- violates also G-2130, G-2220
   co_upper_bound constant pls_integer := 1e8;
begin
   <<burning_cpu>>
   for i in 1..co_upper_bound
   loop
      if i > 0 then
         l_result := l_result + 1;
      end if;
   end loop burning_cpu;
   sys.dbms_output.put_line(l_result);
end;
/

Example (good)

declare
   l_result       simple_integer       := 0;
   co_upper_bound constant pls_integer := 1e8;
begin
   <<burning_cpu>>
   for i in 1..co_upper_bound
   loop
      if i > 0 then
         l_result := l_result + 1;
      end if;
   end loop burning_cpu;
   sys.dbms_output.put_line(l_result);
end;
/