Tag Archives: oracle

This interesting question was posted on our russian forum yesterday:

We have a huge PL/SQL package and this simple function returns wrong result when it’s located at the end of package body:

create or replace package body PKGXXX as
  ...
  function ffff return number is
  nRes number;
  begin        
    nRes :=  268435456;
    return nRes;
  end;
end;
/

But it works fine in any of the following cases:
* replace 268435456 with power(2, 28), or
* replace 268435456 with small literal like 268, or
* move this function to the beginning of package body

The one of the interesting findings was that the returned value is equal to the one of literals in another function.
We can reproduce this bug even with an anonymous pl/sql block. The following test case uses 32768 integer literals from 1000001 to 1032768 and prints 5 other integers:

declare n number;
begin
  n:=1000001; -- this part
  n:=1000002; -- creates
  n:=1000003; -- 32768 
   ...        -- integer
  n:=1032768; -- literals
    dbms_output.put_line('100000='||100000); -- it should print: 100000=100000
    dbms_output.put_line('32766 ='||32766);
    dbms_output.put_line('32767 ='||32767);    
    dbms_output.put_line('32768 ='||32768);
    dbms_output.put_line('32769 ='||32769);
end;

declare
   c clob:='declare n number;begin'||chr(10);
   f varchar2(100):='n:=%s;'||chr(10);
   v varchar2(32767);
   n number:=32768;
begin
   for i in 1..n loop
      v:=v||utl_lms.format_message(f,to_char(1e7+i));
      if length(v)>30000 then
         c:=c||v;
         v:='';
      end if;
   end loop;
   v:=v||q'[
    dbms_output.put_line('100000='||100000);
    dbms_output.put_line('32766 ='||32766);
    dbms_output.put_line('32767 ='||32767);    
    dbms_output.put_line('32768 ='||32768);
    dbms_output.put_line('32769 ='||32769);
   end;
   ]';
   c:=c||v;
   execute immediate c;
end;
/

100000=10000001
32766 =32766
32767 =32767
32768 =10000002
32769 =10000003

This test case well demonstrates wrong results:
* instead of 100000 we get 10000001, which is the value from first line after “begin”, ie 1st integer literal in the code,
* for 32766 and 32767 oracle returns right values
* instead of 32768 (==32767+1) it returns 10000002, which is the integer from 2nd line, ie 2nd integer literal in the code,
* instead of 32769 (==32767+2) it returns 10000003, which is the integer from 3rd line, ie 3rd integer literal in the code
After several tests I can make a conclusion:

• It doesn’t matter what plsql_optimize_level or plsql_code_type you set, was debug enabled or not, the behaviour is the same.
• It seems that this is a kind of PL/SQL optimization: during parsing, oracle leaves integer literal in place if its value is in range -32768..32767 (16bit signed int), but if its value is out of this range, oracle adds this value into array of integers’ constants and replaces the value with the index of this element in this array. But because of index value overflow in cases when a count of such integer literals becomes larger than 32768, instead of Nth element of this array, oracle returns Mth element, where M is mod(N,32767).

So we can describe this behaviour using first test case:

declare n number;
begin
  n:=1000001; -- this part
  n:=1000002; -- creates
  n:=1000003; -- 32768 
   ...        -- integer
  n:=1032768; -- literals
    dbms_output.put_line('100000='||100000); -- it should print 100000, ie 32768th element of array, but prints 10000001
                                             -- where 10000001 is the 1st element of array (1==mod(32768,32767))
    dbms_output.put_line('32766 ='||32766);  -- these 2 lines print right values,
    dbms_output.put_line('32767 ='||32767);  -- because their values are in the range of -32768..32767
    dbms_output.put_line('32768 ='||32768);  -- this line contains 32769th element and prints 2nd element of array (2==mod(32769,32767))
    dbms_output.put_line('32769 ='||32769);  -- this line contains 32770th element and prints 3nd element of array (3==mod(32770,32767))
end;

The following query can help you to find objects which can potentially have this problem:

select
  s.owner,s.name,s.type
 ,sum(regexp_count(text,'(\W|^)3\d{4,}([^.0-9]|$)')) nums_count -- this regexp counts integer literals >= 30000
from dba_source s 
where 
    owner='&owner'
and type in ('FUNCTION','PROCEDURE','PACKAGE','PACKAGE BODY')
group by s.owner,s.name,s.type
having sum(regexp_count(text,'(\W|^)3\d{4,}([^.0-9]|$)'))>32767 -- filter only objects which have >=32767 integer literal

Workaround:
You may noticed that I wrote about INTEGER literals only, so the easiest workaround is to make them FLOAT – just add “.” to the end of each literal:

declare n number;
begin
  n:=1000001.;
  n:=1000002.;
  n:=1000003.;
   ...       
  n:=1032768.;
    dbms_output.put_line('100000='||100000.);
    dbms_output.put_line('32766 ='||32766.);
    dbms_output.put_line('32767 ='||32767.);    
    dbms_output.put_line('32768 ='||32768.);
    dbms_output.put_line('32769 ='||32769.);
end;

declare
   c clob:='declare n number;begin'||chr(10);
   f varchar2(100):='n:=%s.;'||chr(10); -- I've added here "."
   v varchar2(32767);
   n number:=32768;
begin
   for i in 1..n loop
      v:=v||utl_lms.format_message(f,to_char(1e7+i));
      if length(v)>30000 then
         c:=c||v;
         v:='';
      end if;
   end loop;
   v:=v||q'[
    dbms_output.put_line('100000='||100000.); -- .
    dbms_output.put_line('32766 ='||32766.);
    dbms_output.put_line('32767 ='||32767.);    
    dbms_output.put_line('32768 ='||32768.);
    dbms_output.put_line('32769 ='||32769.);
   end;
   ]';
   c:=c||v;
   execute immediate c;
end;
/