Getting Numeric overflow error for smaller numbers but success for larger numbers

Question

Following is my test function:

create or replace FUNCTION test
RETURN NUMBER
AS
  v_count   number(15);
  v_msisdn  number(15);
BEGIN
  v_msisdn:= 225952 * 10000;
  -- v_msisdn:=50510060853 * 10000;

  return v_msisdn;
END;

When I execute the function as

select test() from dual;

I get an error

1426. 00000 -  "numeric overflow"
*Cause:    Evaluation of an value expression causes an overflow/underflow.

However, if I update

v_msisdn:=50510060853 * 10000;

I do not get error.

Could some one please explain this behaviour as the second query is a bigger number?

Answer 1

You can fix this by casting one of the integer literals to integer or number:

v_msisdn:= cast(225952 as integer) * 10000;

Small integer literals are treated like pls_integer for performance reasons. Unfortunately, this type raises an exception on overflow:

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 For calculations outside the PLS_INTEGER range, use INTEGER, a predefined subtype of the NUMBER data type.

In your second example, one of the values (50510060853) doesn't fit into the range of pls_integer and, thus, is interpreted as an integer.

Here's the similar question on AskTom: Datatype Number results in Numeric overflow although value is small enough