In OceanBase Database, the PLS_INTEGER and BINARY_INTEGER data types in PL are the same.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
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;
/
OBE-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