PLS_INTEGER and BINARY_INTEGER

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

PLS_INTEGER stores signed integers that range from -2,147,483,648 to 2,147,483,647, which are represented by using 32 bits. Compared with NUMBER, PLS_INTEGER uses less space and achieves higher computing efficiency.

If the evaluation result of two values of the PLS_INTEGER type exceeds the value range of PLS_INTEGER, the database returns an error. If the data size exceeds the value range of PLS_INTEGER, use the built-in type INTEGER instead.

Even if a value is finally assigned to a variable of the NUMBER type, an error is still returned. Here is an example:

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

To solve the issue, replace at least one variable with INTEGER. Here is an 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