Integer Arithmetic

I was looking at the problem of the 3rd party application which is generating ORA-01426 errors when I found the following on My Oracle Support. According to Oracle, this issue affects version 11.2.0.4 and above but I tested it on Oracle 11.2.0.1. Since version 10g, Oracle has used integer arithmetic when it can. The largest integer result it can hold is not very long so you can easily get an ORA-01426:

 

SQL> declare

  2   z number;

  3  begin

  4   z := 654321 * -123456;

  5   dbms_output.put_line('z = '||z);

  6  end;

  7  /

declare

*

ERROR at line 1:

ORA-01426: numeric overflow

ORA-06512: at line 4

 

SQL>

 

If you add a digit after the decimal point in one of the operands, Oracle thinks that the number is not an integer and the calculation works correctly:

 

SQL> declare

  2   z number;

  3  begin

  4   z := 654321.0 * -123456;

  5   dbms_output.put_line('z = '||z);

  6  end;

  7  /

z = -80779853376

 

PL/SQL procedure successfully completed.

 

SQL>

 

It also works if the integer values are stored in variables:

 

SQL> declare

  2   x number;

  3   y number;

  4   z number;

  5  begin

  6   x := 654321;

  7   y := -123456;

  8   z := x * y;

  9   dbms_output.put_line('z = '||z);

10  end;

11  /

z = -80779853376

 

PL/SQL procedure successfully completed.

 

SQL>

 

Oracle do not regard this as a bug.

Somehow I doubt if this is the cause of my problem as it only seems to manifest itself with hard coded values but I will be bearing it in mind.

Several days later:

Kevan Gelling commented that  the calculation will also generate an ORA-01426 if the values are stored in variables defined as PLS_INTEGER. You can see what he means in the example below: 

SQL> declare

  2   x pls_integer;

  3   y pls_integer;

  4   z pls_integer;

  5  begin

  6   x := 654321;

  7   y := -123456;

  8   z := x * y;

  9   dbms_output.put_line('z = '||z);

10  end;

11  /

declare

*

ERROR at line 1:

ORA-01426: numeric overflow

ORA-06512: at line 8

 

SQL> 

This could be a useful line of investigation as several default schemas use PLS_INTEGER variables in the database concerned: 

SQL> l

  1  select owner, count(*)

  2  from dba_source

  3  where upper(text) like '%PLS_INTEGER%'

  4  group by owner

  5* order by 1

SQL> /

 

OWNER                            COUNT(*)

------------------------------ ----------

APEX_030200                           147

CTXSYS                                 17

EXFSYS                                  4

MDSYS                                   8

ORDSYS                                  2

SYS                                  1898

XDB                                   180

 

7 rows selected.

 

SQL>