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

 

严格意义上讲，这个问题可能并不是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错误的出现。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-691600/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-691600/