Oracle PL/SQL - Preventing PLS_INTEGER Overflow

Introduction

A calculation with two PLS_INTEGER values that overflows the PLS_INTEGER range raises an overflow exception.

For calculations outside the PLS_INTEGER range, use INTEGER, a predefined subtype of the NUMBER data type.

PLS_INTEGER Calculation Raises Overflow Exception

Demo

SQL>
SQL> DECLARE--   w  w  w  .j  a v  a 2 s  . c o  m
  2    p1 PLS_INTEGER := 2147483647;
  3    p2 PLS_INTEGER := 1;
  4    n NUMBER;
  5
  6  BEGIN
  7     n := p1 + p2;
  8  END;
  9  /
DECLARE
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 7


SQL>

To fix it and prevent Overflow

Demo

SQL>
SQL> DECLARE--  w  w  w.j  av  a 2 s.  com
  2     p1 PLS_INTEGER := 2147483647;
  3     p2 INTEGER := 1;
  4     n NUMBER;
  5  BEGIN
  6     n := p1 + p2;
  7  END;
  8  /

PL/SQL procedure successfully completed.

SQL>

Related Topic