1

Why PLSQL Engine does not throw runtime exception when there is an overflow for binary_integer data type?

Can anyone explain?This was an interview question.

Vaibhav Raghuwanshi
  • 11
  • 1
  • Possible duplicate of [What's the difference between pls\_integer and binary\_integer?](https://stackoverflow.com/questions/7412731/whats-the-difference-between-pls-integer-and-binary-integer) – nabuchodonossor Mar 04 '19 at 10:13
  • could you provide some code? – q4za4 Mar 04 '19 at 13:47

2 Answers2

1

Actually BINARY_INTEGER (also known as PLS_INTEGER) raises an overflow exception.

Refer page 3-8 (bottom part) of below link. https://docs.oracle.com/cd/E11882_01/appdev.112/e25519.pdf

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 there is a Subtype of BINARY_INTEGER/PLS_INTEGER, which is known as SIMPLE_INTEGER.

Now refer page 3-10 of same document

If and only if all operands in an expression have the data type SIMPLE_INTEGER, PL/SQL uses two's complement arithmetic and ignores overflows.

So in this case when the positive max value is reached (+2147483647), the next number will begin with negative max value (-2147483648) and vice versa.

ex:

+2147483647 + 1 returns -2147483648

and

-2147483648 - 1 returns +2147483647

Because of this phenomenon, there won't be an overflow exception in case of SIMPLE_INTEGER type.

Pavan Chandaka
  • 11,671
  • 5
  • 26
  • 34
0

The intereviewer was wrong. it raises -1426 when there is an overflow. I tried a very simple test:

declare xx pls_integer;
begin
    xx := 999999999999999;
end;

And even if it did not raise an error - that would be a bug in the language and if I may say so pretty stupid question for an interview!

Saad Ahmad
  • 393
  • 1
  • 7