ORA-06502: PL/SQL: numeric or value error: character string buffer too small

今天 一网友碰到了这个ora 6502错误,下面还原一下

anbob@ORCL> select lengthb(wm_concat(employee_id)) from employee where rownum<=995;

LENGTHB(WM_CONCAT(EMPLOYEE_ID))
-------------------------------
3998

anbob@ORCL> select lengthb(wm_concat(employee_id)) from employee where rownum<=996;
select lengthb(wm_concat(employee_id)) from employee where rownum<=996
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "WMSYS.WM_CONCAT_IMPL", line 30

我的解决方法

anbob@ORCL> l
  1  create or replace function my_concat
  2  return clob
  3  is
  4  str_res clob;
  5  str varchar2(20) default ',';
  6  begin
  7  for x in (select employee_id from employee) loop
  8     str_res:=str_res||str||to_char(x.employee_id);
  9  end loop;
 10  return str_res;
 11* end;
anbob@ORCL> select my_concat from dual;

MY_CONCAT
--------------------------------------------------------------------------------
,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,8
8,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,1
11,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,1
31,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,1
51,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,1
.....

anbob@ORCL> select length(my_concat) from dual;

LENGTH(MY_CONCAT)
-----------------
            18669

note:
wm_concat PL/SQL function that returns VARCHAR2,varchar2 max chars 4000,so When the size of the return string is greater than about 2000 characters will get an ORA-06502