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

原创 2012年03月30日 13:05:10

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

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

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

  1. 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. /  

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 error
ORA-06512: at line 9

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

  1. declare  
  2.   v_name varchar2(3);   
  3. begin  
  4.   v_name := 'Frank';   
  5. end;   
  6. /   
  7.   
  8. declare  
  9.   v_n number(2);   
  10. begin  
  11.   v_n := 100;   
  12. end;   
  13. /  

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 small
ORA-06512: at line 4

dw@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 large
ORA-06512: at line 4

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

  1. declare  
  2.   v_n number(2) ;   
  3. begin  
  4.   v_n := 'a';   
  5. end;   
  6. /  

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 error
ORA-06512: at line 4

dw@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.

转载请注明:本文来自iDB Stock:http://www.idb-stock.net/idb/2011/07/05/204.html

===========

 

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 函数也会导致这个问题,目前原因未明。

ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小 错误分析

1. 问题起因 最近在进行oracle的一些操作时,总会遇到这个错误:  ORA-06502: PL/SQL: 数字或值错误 :  字符串缓冲区太小,错误如下: ORA-00604: 递归...
  • yfleng2002
  • yfleng2002
  • 2012年07月27日 17:51
  • 75709

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

Oracle Report调试效率不是一般的低,对以前做惯.net的我来说对这一点更加是深有体会,如果遇到一个比较隐蔽的错误的时候将会令你蛋痛不已。今天我就有幸遇到一个隐式类型转换的问题,找了一个上午...
  • xhlinlinxh
  • xhlinlinxh
  • 2011年05月26日 15:21
  • 3636

ORA-06502

ORA-06502: PL/SQL: numeric or value error: number precision too large 今天调试一个oracle函数,报该错误,原来以为是插入表的数...
  • lxzo123
  • lxzo123
  • 2011年04月15日 10:28
  • 11453

Number(p,s)的认识....

1、number(p,s)整数位精确到小数点后s位,并四舍五入。若s为负数,则精确到小数点前s位,并四舍五入。 2、如果p=0,比如 number(10) 此时插入数值0.5,小数点后一位四舍五入,实...
  • zwxrain
  • zwxrain
  • 2006年04月12日 14:04
  • 7604

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

http://www.idb-stock.net/idb/2011/07/05/204.html ora-06502错误主要是指数据字或值错误,包括以下子类型:字符到数据值的转换错误、字符串缓冲区太...
  • caolaosanahnu
  • caolaosanahnu
  • 2012年03月30日 13:05
  • 1473

ORA-06502: PL/SQL: 数字或值错误

今天花了大半天找一个错误,错误提示如下ORA-06502: PL/SQL: 数字或值错误 代码如下: type cur_type is ref CURSOR; --定义一...
  • u010634288
  • u010634288
  • 2016年12月09日 10:29
  • 164

impdp导入报错:ora-39125,ora-06502,LPX-00225,ora-06512

一客户工作人员做导入操作时候,报错,信息如下: ora-39125:worker unexpected fatal error in KUPW$WORKER.PUT_DDLS while calli...
  • killvoon
  • killvoon
  • 2015年04月07日 09:09
  • 1784

PHP调用Oracle存储过程时的错误:ORA-06502: PL/SQL: numeric or value error: character string buffer too small

刚刚试着用PHP 的oci8 函数集去调用一个存储过程时总是查询失败且返回以下错误,很是郁闷。。 ORA-06502: PL/SQL: numeric or value error: charact...
  • Eric6
  • Eric6
  • 2010年08月10日 15:44
  • 3342

Bulk Bind: Truncated Bind问题的解决

执行一段bulk collect的select语句时,报下面的错误,这个错误是因为字段长度与检索出来的长度不一致,一般是字段长度小于检索出来的长度,仔细检查后发现是因为yearmonth的长度声明为v...
  • wonder4
  • wonder4
  • 2007年10月09日 09:36
  • 5724

ORA-06502: PL/SQL:numeric or value error!

调用存储过程,得到错误信息是ORA-06502: numeric or value error. 最后发现是往一个varchar2的变量里面放数据的时候,超过了长度限制!!!   长度加长就ok了...
  • heicm
  • heicm
  • 2011年03月30日 15:40
  • 782
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:list agg cause ORA-06502 PL/SQL: numeric or value error
举报原因:
原因补充:

(最多只允许输入30个字)