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 with a range from -2,147,483,648 to 2,147,483,647, occupying 32 bits. Compared to the NUMBER type, PLS_INTEGER uses less storage space and is more efficient in computation.

If the result of two calculations of type PLS_INTEGER exceeds the range of PLS_INTEGER, an error is returned by the database. If a data size exceeds the range of PLS_INTEGER, it must be replaced with the built-in type INTEGER.

As shown in the following example, an error is still reported even though the value is ultimately 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;
/
ORA-01426: numeric overflow

The solution is to replace at least one of the variables with INTEGER, 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