ITPUB SQL大赛之BUG（三）

由于SQL大赛题目相对比较困难，不但需要使用大量的特性，且SQL实现十分复杂，一般运行时间也会比较长，这些因素导致碰到bug的几率直线上升。这里介绍SQL大赛期间碰到的几个bug。

这篇描述碰到的ORA-22813错误。

ITPUB SQL大赛之BUG（一）：http://yangtingkun.itpub.net/post/468/515815

ITPUB SQL大赛之BUG（二）：http://yangtingkun.itpub.net/post/468/515815

严格意义上讲，这个问题可能并不是Oracle的bug：

SQL> with i as
2 (select rownum i from dual connect by rownum <= :n),
3 j as
4 (select rownum j from dual connect by rownum <= :n),
5 position as
6 (select i, j
7 from i, j),
8 b as
9 (select rownum - 1 b from dual connect by rownum <= 2),
10 b_line as
11 (select replace(sys_connect_by_path(b, ','), ',', '') line
12 from b
13 where connect_by_isleaf = 1
14 connect by level <= :n),
15 lines as
16 (select rownum, line from b_line
17 where instr(line, 1, 1, :m) > 0
18 and instr(line, 0, 1, :n - :m) > 0),
19 lines_result as
20 (select replace(sys_connect_by_path(line, ','), ',', '') result
21 from lines
22 where connect_by_isleaf = 1
23 connect by level <= :n)
24 select result, j, (j-i) l, (j+i) r, sum(substr(result, (i-1)*:n + j, 1)) c, max(replace(wmsys.wm_concat(substr(result, (i-1)*:n + j, 1)) over(partition by j order by i), ',' ,'')) col
25 from lines_result, position
26 group by grouping sets ((result, j), (result, (j-i)), (result, (j+i)));
(select rownum - 1 b from dual connect by rownum <= 2),
*第 9 行出现错误:
ORA-22813: 操作数值超出系统的限制

导致问题的原因多半是由于wmsys.wm_concat函数造成的。正常情况下，SQL语句造成资源不足的错误多半是临时表空间无法扩展，而这里的ORA-22813错误，显然是PL/SQL程序中资源不足导致的，而wmsys.wm_concat函数显然是Oracle通过TYPE类型实现的PL/SQL函数。

虽然导致问题的真正原因是处理的数据量太大所致，但是Oracle的这个错误信息太不明确了，至少应该提示用户这个限制的具体值是多少。而这种错误信息对于解决问题显然没有任何的帮助。

这个问题最终通过修改SQL的方式来解决，由于当前的查询中wmsys.wm_concat所需要处理的数据量太大，最终将SQL改为先求出所有满足的记录，然后对这些记录进行变形，这样wmsys.wm_concat函数所处理记录数缩小了好几个数量级，因此避免了ORA-22813错误的出现。