A new data type in Oracle 11g: SIMPLE_INTEGER

Prior to Oracle 11g, we have used PLS_INTEGER data type in PL/SQL programs. In 11g, a new data type SIMPLE_INTEGER has been introduced. It is a sub-type of PLS_INTEGER data type and has the same range as PLS_INTEGER. The basic difference between the two is that SIMPLE_INTEGER is always NOT NULL.  When the value of the declared variable is never going to be null then we can declare it with SIMPLE_INTEGER data type. Another major difference is that it never gives numeric overflow error like its parent data type instead it wraps around without giving any error. When we don’t have to worry about null checking and overflow errors, SIMPLE_INTEGER data type is the best to use.

Another difference is that the 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. When code is compiled in ‘INTERPRETED’ mode which is default, SIMPLE_INTEGER is still better than the PLS_INTEGER but is not that significant.

One can alter the session to compile code in ‘NATIVE’  mode by executing following statement and then compile the stored procedure.

SQL> ALTER SESSION SET PLSQL_CODE_TYPE=’NATIVE’;

Session altered.

To switch back to default mode,

SQL> ALTER SESSION SET PLSQL_CODE_TYPE=’INTERPRETED’;

Session altered.

Following code snippet shows the error when SIMPLE_INTEGER variable is assigned null value.

DECLARE
v_col1 SIMPLE_INTEGER := 1;
BEGIN
v_col1 := NULL;
END;
/

Since v_col1 should always be not null and we are trying to assign null value, it gives following error.

PLS-00382: expression is of wrong type

Even if we haven’t assigned the value in the declaration section for SIMPLE_INTEGER datatype, it results into following error:

PLS-00218: a variable declared NOT NULL must have an initialization assignment.