PLS_INTEGER and BINARY_INTEGER Data Types

The PL/SQL data types PLS_INTEGER and BINARY_INTEGER are identical. For simplicity, this document uses PLS_INTEGER to mean both PLS_INTEGER and BINARY_INTEGER.

The PLS_INTEGER data type stores signed integers in the range -2,147,483,648 through 2,147,483,647, represented in 32 bits.

The PLS_INTEGER data type has these advantages over the NUMBER data type and NUMBER subtypes:

·         PLS_INTEGER values require less storage.

·         PLS_INTEGER operations use hardware arithmetic, so they are faster than NUMBER operations, which use library arithmetic.

For efficiency, use PLS_INTEGER values for all calculations in its range.

Preventing PLS_INTEGER Overflow

A calculation with two PLS_INTEGER values that overflows the PLS_INTEGER range raises an overflow exception, even if you assign the result to a NUMBER data type (as in Example 3-3). For calculations outside the PLS_INTEGER range, use INTEGER, a predefined subtype of the NUMBER data type (as in Example 3-4).

Example 3-3 PLS_INTEGER Calculation Raises Overflow Exception

DECLARE

  p1 PLS_INTEGER := 2147483647;

  p2 PLS_INTEGER := 1;

  n NUMBER;

BEGIN

  n := p1 + p2;

END;

/

Result:

DECLARE

*

ERROR at line 1:

ORA-01426: numeric overflow

ORA-06512: at line 6

Example 3-4 Preventing Example 3-3 Overflow

DECLARE

  p1 PLS_INTEGER := 2147483647;

  p2 INTEGER := 1;

  n NUMBER;

BEGIN

  n := p1 + p2;

END;

/

Result:

PL/SQL procedure successfully completed.

Predefined PLS_INTEGER Subtypes

Table 3-3 lists the predefined subtypes of the PLS_INTEGER data type and describes the data they store.

Table 3-3 Predefined Subtypes of PLS_INTEGER Data Type

Data Type	Data Description
NATURAL	Nonnegative PLS_INTEGER value
NATURALN	Nonnegative PLS_INTEGER value with NOT NULL constraintFoot 1
POSITIVE	Positive PLS_INTEGER value
POSITIVEN	Positive PLS_INTEGER value with NOT NULL constraintFootref 1
SIGNTYPE	PLS_INTEGER value -1, 0, or 1 (useful for programming tri-state logic)
SIMPLE_INTEGER	PLS_INTEGER value with NOT NULL constraint. For more information, see "SIMPLE_INTEGER Subtype of PLS_INTEGER".

PLS_INTEGER and its subtypes can be implicitly converted to these data types:

·         CHAR

·         VARCHAR2

·         NUMBER

·         LONG

All of the preceding data types except LONG, and all PLS_INTEGER subtypes, can be implicitly converted to PLS_INTEGER.

A PLS_INTEGER value can be implicitly converted to a PLS_INTEGER subtype only if the value does not violate a constraint of the subtype. For example, casting the PLS_INTEGER value NULL to the SIMPLE_INTEGER subtype raises an exception, as Example 3-5shows.

Example 3-5 Violating Constraint of SIMPLE_INTEGER Subtype

DECLARE

  a SIMPLE_INTEGER := 1;

  b PLS_INTEGER := NULL;

BEGIN

  a := b;

END;

/

Result:

DECLARE

*

ERROR at line 1:

ORA-06502: PL/SQL: numeric or value error

ORA-06512: at line 5

SIMPLE_INTEGER Subtype of PLS_INTEGER

If and only if all operands in an expression have the data type SIMPLE_INTEGER, PL/SQL uses two's complement arithmetic and ignores overflows. Because overflows are ignored, values can wrap from positive to negative or from negative to positive; for example:

2³⁰ + 2³⁰ = 0x40000000 + 0x40000000 = 0x80000000 = -2³¹

-2³¹ + -2³¹ = 0x80000000 + 0x80000000 = 0x00000000 = 0

For example, this block runs without errors:

DECLARE

  n SIMPLE_INTEGER := 2147483645;

BEGIN

  FOR j IN 1..4 LOOP

    n := n + 1;

    DBMS_OUTPUT.PUT_LINE(TO_CHAR(n, 'S9999999999'));

  END LOOP;

  FOR j IN 1..4 LOOP

   n := n - 1;

   DBMS_OUTPUT.PUT_LINE(TO_CHAR(n, 'S9999999999'));

  END LOOP;

END;

/

Result:

+2147483646

+2147483647

-2147483648

-2147483647

-2147483648

+2147483647

+2147483646

+2147483645

PL/SQL procedure successfully completed.

Expressions with Both SIMPLE_INTEGER and Other Operands

If an expression has both SIMPLE_INTEGER and other operands, PL/SQL implicitly converts the SIMPLE_INTEGER values toPLS_INTEGER NOT NULL.

The PL/SQL compiler issues a warning when SIMPLE_INTEGER and other values are mixed in a way that might negatively impact performance by inhibiting some optimizations.

Integer Literals in SIMPLE_INTEGER Range

Integer literals in the SIMPLE_INTEGER range have the data type SIMPLE_INTEGER. However, to ensure backward compatibility, when all operands in an arithmetic expression are integer literals, PL/SQL treats the integer literals as if they were cast to PLS_INTEGER.

I hope you all have enjoyed reading this article. Comments are welcome....