[20171211]Bug with integer literals in PLSQL.txt
--//链接 http://orasql.org/2017/12/09/bug-with-integer-literals-in-plsql/
--//重复测试:
--//作者的观点好像是如果在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.