Difference between PLS_INTEGER and INTEGER for below script
Please see these two scenarios:
Case 1:
DECLARE
p1 PLS_INTEGER := 2147483647;
p2 INTEGER := 1;
n NUMBER;
BEGIN
n := p1 + p2;
END;
PL/SQL procedure successfully completed.
Case 2
DECLARE
p1 PLS_INTEGER := 2147483647;
p2 PLS_INTEGER := 1;
n NUMBER;
BEGIN
n := p1 + p2;
END;
Error at line 1
ORA-01426: numeric overflow
ORA-06512: at line 6
Why case 2
failed, even I tried to add the same datatype while case 1
succeeded?
source to share
The documentation for PLS_INTEGER and BINARY_INTEGER Data Types only says:
A calculation with two PLS_INTEGER values that overflows the PLS_INTEGER range throws an overflow exception even if you assign the result to the NUMBER data type.
But that doesn't tell you why. I guess the reason is that the PLS_INTEGER operations use hardware arithmetic, so Oracle does it correctly internally, like
n := CAST(p1 + p2 AS NUMBER);
source to share
PLS_INTEGER is a PL / SQL data type, which, according to the PL / SQL Language Reference :
stores signed integers in the range -2147,483,648 to 2,147,483,647
INTEGER, on the other hand, is an ANSI SQL datatype that Oracle converts to a zero-scale NUMBER.
As per the reference to SQL Server SQL Database, NUMBER is a built-in Oracle datatype that
stores zero as well as positive and negative fixed numbers with absolute values from 1.0 x 10 ^ -130 to, but not including 1.0 x 10 ^ 126
source to share