未合理使用类型 导致 ora-6502

业务反馈 之前返回结果集量小，sql执行正常，返回结果集增多后出现ora-6502， 需协助分析原因语句为，

select eis_get单位体检条件(6) from dual;

06502 官方解释如下

ORA-06502: PL/SQL: numeric or value errorstring

Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).

Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.

在字段类型 使用中存在错误导致

函数如下

create or replace function eis_get单位体检条件(
                                        vi机构序号 number
                                       ) return nvarchar2 as

  Mi数量 number(4);
  Ms结果 nvarchar2(4000);
  Ms返回结果 nvarchar2(4000);
  
begin
  for r in(select 系统序号 from eis_t体检单位 where 隶属机构i=vi机构序号 and 有效状态b=1 ) loop
    Ms结果:=r.系统序号;
    select count(*) into Mi数量 from eis_t体检套餐 where 体检单位i=r.系统序号 and 有效状态B=1;
    Ms结果:=Ms结果||'|'||Mi数量；
   select count(*) into Mi数量 from eis_t体检套餐 where 体检单位i=r.系统序号 and 有效状态B=1 and 适用婚况i is null;
    Ms结果:=Ms结果||'|'||Mi数量;
    select count(*) into Mi数量 from eis_t体检套餐 where 体检单位i=r.系统序号 and 有效状态B=1 and 职业情况i is null;
    Ms结果:=Ms结果||'|'||Mi数量;
    select count(*) into Mi数量 from eis_t体检套餐 where 体检单位i=r.系统序号 and 有效状态B=1 and 职位级别i is null;
    Ms结果:=Ms结果||'|'||Mi数量;
    select count(*) into Mi数量 from eis_t体检套餐 where 体检单位i=r.系统序号 and 有效状态B=1 and 所在部门i is null;
    Ms结果:=Ms结果||'|'||Mi数量;
    Ms结果:=Ms结果||';';

    Ms返回结果:=Ms返回结果||Ms结果;
  end loop;

return Ms返回结果;

end eis_get单位体检条件;

 

随着基础业务数据是不断增加 ，Ms返回结果 拼接后会不停的增长，这里使用nvarchar会存在字节上限，会导致字节长度不足

通过排查 发现 执行到 return Ms返回结果已经开始超过nvarchar2字节上限

调整字符类型用法后问题解决

create or replace function eis_get单位体检条件(
                                        vi机构序号 number
                                       ) return 
                                        nclob 
                                        as

  Mi数量 number(18);
  Ms结果 Nclob;
  Ms返回结果 nclob;
  v_counter number:=0;

begin
  for r in(select 系统序号 from eis_t体检单位 where 隶属机构i=vi机构序号 and 有效状态b=1 ) loop
    Ms结果:=to_char(r.系统序号);
   select count(*) into Mi数量 from eis_t体检套餐 where 体检单位i=r.系统序号 and 有效状态B=1;
    Ms结果:=Ms结果||'|'||to_char(Mi数量);
   select count(*) into Mi数量 from eis_t体检套餐 where 体检单位i=r.系统序号 and 有效状态B=1 and 适用婚况i is null;
    Ms结果:=Ms结果||'|'||to_char(Mi数量);
    select count(*) into Mi数量 from eis_t体检套餐 where 体检单位i=r.系统序号 and 有效状态B=1 and 职业情况i is null;
    Ms结果:=Ms结果||'|'||to_char(Mi数量);
    select count(*) into Mi数量 from eis_t体检套餐 where 体检单位i=r.系统序号 and 有效状态B=1 and 职位级别i is null;
    Ms结果:=Ms结果||'|'||to_char(Mi数量);
   select count(*) into Mi数量 from eis_t体检套餐 where 体检单位i=r.系统序号 and 有效状态B=1 and 所在部门i is null;
    Ms结果:=Ms结果||'|'||to_char(Mi数量);
    Ms结果:=Ms结果||';';

    Ms返回结果:=Ms返回结果||Ms结果;
     v_counter:=v_counter+1;
  end loop;
  
return Ms返回结果;
end eis_get单位体检条件;