Sunday, June 15, 2014

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> 

More to follow...

2 comments:

Kevan Gelling said...

The last example will also fail if x and y are defined with PLS_INTEGER datatypes.

Andrew Reid said...

Dear Kevan,

Thank you. I did not know that. I will look for them in the application which is giving the problems.

Kind Regards,

Andrew