list agg cause ORA-06502 PL/SQL: numeric or value error

http://www.idb-stock.net/idb/2011/07/05/204.html

ora-06502错误主要是指数据字或值错误，包括以下子类型：字符到数据值的转换错误、字符串缓冲区太小、数值精度太高等。

对空集合的调用，会报ora-06502错误

declare
  type cnt_typ is table of number index by binary_integer;
  v_cnt1 cnt_typ;
begin
  select 1 bulk collect
    into v_cnt1
    from dual
   where 1 > 20;
  for i in v_cnt1.first .. v_cnt1.last
  loop
    dbms_output.put_line(v_cnt1(i));
  end loop;
end;
/

dw@dw>declare  2    type cnt_typ is table of number index by binary_integer;  3    v_cnt1 cnt_typ;  4  begin  5    select 1 bulk collect  6      into v_cnt1  7      from dual  8     where 1 > 20;  9    for i in v_cnt1.first .. v_cnt1.last 10    loop 11      dbms_output.put_line(v_cnt1(i)); 12    end loop; 13  end; 14  /declare*ERROR at line 1:ORA-06502: PL/SQL: numeric or value errorORA-06512: at line 9

对变量赋值时，值的长度超过了变量定义的长度,会报ora-06502错误

declare
  v_name varchar2(3);
begin
  v_name := 'Frank';
end;
/

declare
  v_n number(2);
begin
  v_n := 100;
end;
/

dw@dw>declare  2    v_name varchar2(3);  3  begin  4    v_name := 'Frank';  5  end;  6  /declare*ERROR at line 1:ORA-06502: PL/SQL: numeric or value error: character string buffer too smallORA-06512: at line 4dw@dw>declare  2    v_n number(2);  3  begin  4    v_n := 100;  5  end;  6  /declare*ERROR at line 1:ORA-06502: PL/SQL: numeric or value error: number precision too largeORA-06512: at line 4

字符转换为数字时，也会报ORA-06502字符到数据值的转换错误

declare
  v_n number(2) ;
begin
  v_n := 'a';
end;
/

dw@dw>declare  2    v_n number(2) ;  3  begin  4    v_n := 'a';  5  end;  6  /declare*ERROR at line 1:ORA-06502: PL/SQL: numeric or value error: character to number conversion errorORA-06512: at line 4dw@dw>

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.

===========

for r_rec in (
     --loanxid is null
     select portfolioCusip,portfolioname,facilityCusip,FACILITYNAME, TRADECCY,loanxid, sum(nvl(CommitmentAmount,0)) position,0.0 apolloWAVGPrice, --sum(commitmentamount*WTAVGPURCHASEPRICE)/sum(commitmentamount) apolloWAVGPrice,
     lv,lvname,
     sum(nvl(notionalAmount,0)) notional
     /*
     from GCD.PtrsPositionExtTMP tmp 
     group by portfolioCusip,facilityCusip,FIRMACCOUNTCD,TRADECCY,loanxid 
     */
     from GCD.PtrsPositionExt  tmp 
     where tmp.portfolioCusip in (select portfolioCusip from trustee_daily_position_recon where reporttype=p_reporttype and asofdate=p_date)
     and (loanxid is null or loanxid not like 'LX%') -- we have other values :NOUKMARK,NOLXBOND,NOTLOANX,NOLXMARK
     --group by portfolioCusip,facilityCusip, TRADECCY,loanxid 
     group by portfolioCusip,portfolioname,facilityCusip,  FACILITYNAME ,TRADECCY,loanxid, lv,lvname
     
     union
     
     --loanxid is not null
     select portfolioCusip,portfolioname,
     '' facilityCusip,
     '' FACILITYNAME,
     '' TRADECCY,
       (select listagg(facilitycusip,'|') within group (order by loanxid) from loanfacility where loanxid=tmp.loanxid group by loanxid) facilityCusip,
       (select listagg(facilityname,'|') within group (order by loanxid) from loanfacility where loanxid=tmp.loanxid group by loanxid) FACILITYNAME, 
       (select  listagg(globalccy, '|') within group(order by tt.loanxid)from (select distinct globalccy, loanxid from loanfacility  ) tt  where tt.loanxid=tmp.loanxid group by tt.loanxid) TRADECCY,
      loanxid, sum(nvl(CommitmentAmount,0)) position,0.0 apolloWAVGPrice, --sum(commitmentamount*WTAVGPURCHASEPRICE)/sum(commitmentamount) apolloWAVGPrice,
     lv,lvname,
     sum(nvl(notionalAmount,0)) notional
     /*
     from GCD.PtrsPositionExtTMP tmp 
     group by portfolioCusip,facilityCusip,FIRMACCOUNTCD,TRADECCY,loanxid 
     */
     from GCD.PtrsPositionExt  tmp 
     where tmp.portfolioCusip in (select portfolioCusip from trustee_daily_position_recon where reporttype=p_reporttype and asofdate=p_date)
       and loanxid is not null and loanxid like 'LX%'
     --group by portfolioCusip,facilityCusip, TRADECCY,loanxid 
     group by portfolioCusip,portfolioname,  loanxid, lv,lvname
    ) loop
     --dbms_output.put_line('for:'||r_rec.portfolioCusip||r_rec.facilityCusip);
     update trustee_daily_position_recon recon set apolloPosition=r_rec.position,  --facilityCusip=r_rec.facilityCusip,FACILITYNAME=r_rec.FACILITYNAME,
     apolloWAVGPrice=r_rec.apolloWAVGPrice, apolloNotional=r_rec.notional,
     APOLLOLOANXID=r_rec.loanxid
     where recon.portfolioCusip=r_rec.portfolioCusip 
     and ( (recon.facilityCusip=r_rec.facilityCusip and recon.trusteeIdentifierType not in ('LOANX_ID','LOANX_IDC') )
       or (recon.trusteeIdentifier=r_rec.loanxid and recon.trusteeIdentifierType in ('LOANX_ID','LOANX_IDC'))
      )
     and recon.lv=r_rec.lvname
   and reporttype=p_reporttype and asofdate=p_date;
     --and recon.Currency=r_rec.Currency;
     
      
     --dbms_output.put_line('after update:'||r_rec.portfolioCusip||r_rec.facilityCusip);
     select count(*) cnt into cntTmp from trustee_daily_position_recon recon
     where recon.portfolioCusip=r_rec.portfolioCusip 
     --and ( recon.facilityCusip=r_rec.facilityCusip or ((recon.trusteeIdentifier=r_rec.loanxid or recon.trusteeIdentifier=r_rec.facilityCusip) and recon.facilityCusip is null))
     and ( (recon.facilityCusip=r_rec.facilityCusip and recon.trusteeIdentifierType not in ('LOANX_ID','LOANX_IDC') )
       or (recon.trusteeIdentifier=r_rec.loanxid and recon.trusteeIdentifierType in ('LOANX_ID','LOANX_IDC'))
      )
     and recon.lv=r_rec.lvname;
     --dbms_output.put_line('before insert:'||r_rec.portfolioCusip||r_rec.facilityCusip||'+'||cntTmp);
     if (cntTmp = 0) then
      --dbms_output.put_line('insert:'||r_rec.portfolioCusip||r_rec.facilityCusip);
      insert into trustee_daily_position_recon (recordId,portfolioCusip,portfolioName,facilitycusip,FACILITYNAME,apolloLoanXID,trusteePosition,apolloPosition,Currency,apolloWAVGPrice,lv,asofdate,reporttype,trusteeNotional,apolloNotional,trusteeWAVGPrice) 
      values(GCD.trusteeRecon_Seq.nextval, r_rec.portfolioCusip,r_rec.portfolioName,r_rec.facilityCusip,r_rec.FACILITYNAME,r_rec.loanxid,0,r_rec.position, r_rec.Tradeccy,r_rec.apolloWAVGPrice,r_rec.lvname,p_date,p_reporttype,0,r_rec.notional,0);
     end if;
     --dbms_output.put_line('after insert:'||r_rec.portfolioCusip||r_rec.facilityCusip);
    end loop;

list agg 函数也会导致这个问题，目前原因未明。