Difference between PLS_INTEGER and INTEGER for below scenario

Question

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 does case 2 failed, even I was trying to add same data type whereas case 1 executes successfully ?

Wernfried Domscheit · Accepted Answer · 2015-07-13 08:03:34Z

1

Documentation for PLS_INTEGER and BINARY_INTEGER Data Types only says:

A calculation with two PLS_INTEGER values that overflows the PLS_INTEGER range raises an overflow exception, even if you assign the result to a NUMBER data type.

But it does not tell you why.I assume the reason is PLS_INTEGER operations use hardware arithmetic, so Oracle properly does it internally like

n := CAST(p1 + p2 AS NUMBER);

answered Jul 13, 2015 at 8:03

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marco Baldelli · Accepted Answer · 2015-07-13 07:53:43Z

0

PLS_INTEGER is a PL/SQL data type which, according to the PL/SQL Language Reference:

stores signed integers in the range -2,147,483,648 through 2,147,483,647

On the other hand, INTEGER is an ANSI SQL Data Type which Oracle converts into a NUMBER with zero scale.

According to the Database SQL Language Reference, NUMBER is an Oracle Built-in Data Type which

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

answered Jul 13, 2015 at 7:53

Marco Baldelli

3,7481 gold badge24 silver badges29 bronze badges

1 Comment

cartbeforehorse Jan 29 at 9:28

For what it's worth, the documentation also says the following: >> [PLS_INTEGER values require less storage.] >> [PLS_INTEGER operations use hardware arithmetic, so they are faster than NUMBER operations, which use library arithmetic.] So for any passers-by wondering which they should use, use PLS_INTEGER unless your application specifically requires a range greater than 2 billion

Noel · Accepted Answer · 2015-07-13 07:58:32Z

0

PLS_INTEGER can store upto 2,147,483,647. Crossing this, would result in overflow exception.

INTEGER is a predefined subtype of the NUMBER data type, and is usually referred as NUMBER(38). That means, it can go upto (10^39) - 1. So, no exception your first code block.

answered Jul 13, 2015 at 7:58

Noel

10.6k30 gold badges53 silver badges74 bronze badges

Collectives™ on Stack Overflow

Difference between PLS_INTEGER and INTEGER for below scenario

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related