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;
/