PLS_INTEGER and BINARY_INTEGER

In the PL data types of OceanBase Database, PLS_INTEGER and BINARY_INTEGER are the same.

PLS_INTEGER stores signed integers ranging from -2,147,483,648 to 2,147,483,647, occupying 32 bits. Compared with the NUMBER type, PLS_INTEGER occupies less storage space and is more efficient in calculations.

If the result of an operation between two PLS_INTEGER values exceeds the range of PLS_INTEGER, an error is returned. If the data size exceeds the range of PLS_INTEGER, you must replace it with the built-in INTEGER type.

The following example shows that an error is still returned even if the result is assigned to a NUMBER variable.

obclient> DECLARE
     num1 PLS_INTEGER := 2147483647;
     num2 PLS_INTEGER := 1;
     total NUMBER;
BEGIN
     total := num1 + num2;
     DBMS_OUTPUT.PUT_LINE(total);
END;
/
OBE-01426: numeric overflow

To resolve this issue, you must replace at least one of the variables with the INTEGER type, as shown in the following example:

obclient> DECLARE
     num1 PLS_INTEGER := 2147483647;
     num2 INTEGER := 1;
     total NUMBER;
BEGIN
     total := num1 + num2;
     DBMS_OUTPUT.PUT_LINE(total);
END;
/
Query OK, 0 rows affected

2147483648