Skip to Main Content
  • Questions
  • Datatype Number results in Numeric overflow although value is small enough

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Markus.

Asked: August 30, 2016 - 2:00 pm UTC

Last updated: August 31, 2016 - 8:25 am UTC

Version: Enterprise Edition 11.2.0.4.0

Viewed 10K+ times! This question is

You Asked

Try following Test Cases:
DECLARE
x NUMBER := 1;
BEGIN
x := 1024 * 1024 * 1024 * 5;
END;
/
-> ORA-01426: numeric overflow

DECLARE
x NUMBER := 1;
BEGIN
x := x * 1024;
x := x * 1024;
x := x * 1024;
x := x * 5;
END;
/
-> this works

example can be done in various Variations like

DECLARE
x NUMBER := 1024 * 1024 * 1024 * 5;
BEGIN
Null;
END;
/

Seems like if constructed with MORE than 3 Operators and value exceeds small-int, Overflow occurs. Later on , you can do everything.

AND: (i find it interesting..) :
DECLARE
x NUMBER;
BEGIN
select 1024 * 1024 * 1024 * 5 INTO x FROM dual;
END;
/
this works too.

ANY IDEA to that ?

regards
max



and Chris said...

When you multiply integers in PL/SQL it's actually using pls_integers. These use hardware arithmetic. This is faster than number operations. They use library arithmetic.

But there's a catch. The upper limit of these are 2,147,483,647. Your calculation of 1024 * 1024 * 1024 * 5 goes waaaay over this! You'll hit the limit at 1024 * 1024 * 1024 * 2:

SQL> declare
  2    l number := 1024 * 1024 * 1024  * 2;
  3  begin
  4    null;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 2


But why does it fail?

From the docs:

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

https://docs.oracle.com/cloud/latest/db112/LNPLS/datatypes.htm#LNPLS99938

So how to avoid this?

Make one of the values in the expression a number! Then Oracle will implicitly convert the whole calculation to number arithmetic.

Here are a few ways you could do this:

Cast one of the literals

SQL> declare
  2    l number := cast(1024 as number) * 1024 * 1024  * 2;
  3  begin
  4    null;
  5  end;
  6  /

PL/SQL procedure successfully completed.


Add a decimal to one of the literals, making it a number

SQL> declare
  2    l number := 1024 * 1024 * 1024  * 2.0;
  3  begin
  4    null;
  5  end;
  6  /


Include a variable that's a number in the calculation

SQL> declare
  2    n number := 1;
  3    l number := n * 1024 * 1024 * 1024  * 2;
  4  begin
  5    null;
  6  end;
  7  /


This is why your second example works. All the multiplications use number arithmetic. So don't have the 2,147,483,647 limit.

The SQL statement works because it's processed in the SQL engine. PLS_integer doesn't exist there. So it uses number arithmetic.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Pitfalls

Markus Flatscher, August 31, 2016 - 8:43 am UTC

Thank you for explaination. I was not aware that Oracle does in this case an implicit conversion to the "most likely" type, i implied it would take it all as "number" type. The Integer Border i figured out too by Trial and error, but could not get clear why. So this explains.
I was also not aware about the fact, that the intermediate result has type of Operators, not of the final result.

THX A LOT for fast and detailed Response; But reading all doc's in this detailled manner is hard to do.

Regards
Max


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library