问题描述
今天 一网友碰到了这个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
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。