list agg cause ORA-06502 PL/SQL: numeric or value error
来源:互联网 发布:centos ftp 目录 编辑:程序博客网 时间:2024/06/18 08:17
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;
- /
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;
- /
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;
- /
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.
转载请注明:本文来自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 函数也会导致这个问题,目前原因未明。
- list agg cause ORA-06502 PL/SQL: numeric or value error
- ORA-06502: PL/SQL:numeric or value error!
- ORA-06502: PL/SQL: numeric or value error
- ORA-06502: PL/SQL: numeric or value error: character string buffer too small
- ORA-06502: PL/SQL: numeric or value error: character string buffer too small
- ORA-06502: PL/SQL: numeric or value error: character string buffer too small
- ORA-06502:PL/SQL numeric or value error:associate array shape is not consistent with session parame
- PHP调用Oracle存储过程时的错误:ORA-06502: PL/SQL: numeric or value error: character string buffer too small
- ORA-06502:PL/SQL :numberic or value error: character string buffer too small
- Ora06502: PL/SQL numberic or value error.
- ERROR spi.SqlExceptionHelper : 'Infinity' is not a valid numeric or approximate numeric value 问题解决
- ORA-00604: error occurred at recursive SQL level 1 ORA-12705: invalid or unknown NLS parameter value
- error: expected ',' or '...' before numeric
- PL/SQL: ORA-00942: table or view does not exist
- Error getting nested result map values for 'company'. Cause: java.sql.SQLException: Invalid value fo
- ORA-06544: PL/SQL: internal error, arguments: [55916], [], [], [], [], [], [], []
- Error: PL/SQL: ORA-00980: 同义词转换不再有效
- Error updating database. Cause: java.sql.SQLException: ORA-00001: 违反唯一约束条件
- 产品经理职责
- SPOJ_839_OPTM
- java异常处理(初级)
- EXP和IMP的用法
- SetWindowLong函数总结
- list agg cause ORA-06502 PL/SQL: numeric or value error
- 系统字体
- GUID
- Win7下卸载Oracle11g
- ubuntu 安装时出现进入busybox built-in shell
- android.provider 中的各种表列与类
- Git pull、 push 操作无需输密码的方法
- myEclipse/eclipse下控制台"DeviceMonitor]Adb connection Error:远程主机强迫关闭了一个现有的连接。"解决办法
- DOS下打开F盘某个程序