1211Bug with integer literals in PLSQL

[20171211]Bug with integer literals in PLSQL.txt

--//連結
--//重複測試:
--//作者的觀點好像是如果在PL/SQL中定義的整形變數並引用太多,最後出現異常.

1.環境:
SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

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:

set head off
spool /tmp/n.txt
select  'n:='||to_char(rownum+1000000)||';' from dual connect by level <=32768;
spool off

--//將/tmp/n.txt 內容修改如下:

$ cat /tmp/n.txt
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;
/

SCOTT@book> set serverout on
SCOTT@book> @ /tmp/n.txt
100000=1000001
32766 =32766
32767 =32767
32768 =1000002
32769 =1000003

PL/SQL procedure successfully completed.

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;
/

SCOTT@book> @ /tmp/n.txt
100000=100000
32766 =32766
32767 =32767
32768 =32768
32769 =32769
PL/SQL procedure successfully completed.

--//我的測試寫成這樣也ok.作者的輸出dbms_output.put_line('32769 ='||32769.);後面帶點.;
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;
/
SCOTT@book> @ /tmp/n.txt
100000=100000
32766 =32766
32767 =32767
32768 =32768
32769 =32769
PL/SQL procedure successfully completed.

--//如果我修改如下,註解2行:
declare n number;
begin
n:=1000001;
n:=1000002;
n:=1000003;
n:=1000004;
--n:=1000005;
--n:=1000006;
n:=1000007;
....
n:=1032767;
n:=1032768;
    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;
/

SCOTT@book> @ /tmp/n.txt
100000=100000
32766 =32766
32767 =32767
32768 =0
32769 =1000001
PL/SQL procedure successfully completed.

--//如果我修改如下,註解3行:
SCOTT@book> @ /tmp/n.txt
100000=100000
32766 =32766
32767 =32767
32768 =32768
32769 =0
PL/SQL procedure successfully completed.