查一下sga的大小 select * from v$parameters where name like 'db%';更改db_cache_size大小 alter system set db_cache_size=25165824 scope=both;
ORA-06502 PL/SQL: numeric or value error stringCause: 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 NewFormatName(sorderid in orderhist.orderid%type) return varchar2 is returnProName varchar2(3000); v_proName varchar2(500); v_num number(10); v_result varchar2(1000); cursor c_proName is select decode(od.prodname,'','',od.prodname) || '*' ||decode(od.upnum,0,'',od.upnum) || decode(od.spnum,0,'',od.spnum) from orderdet od,orderhist oh where od.orderid=oh.orderid; begin open c_proName; loop fetch c_proName into v_result; exit when c_proName%notfound; returnProName:=returnProName ||' ' || v_result; end loop; close c_proName; return (returnProName); end NewFormatName;写了个函数,运行函数没问题,但是在SQL中调用的时候报错:ORA-06502 PL/SQL 数字或值错误,指向returnProName:=returnProName ||' ' || v_result;这一行调用的SQL: select NewFormatName(orderhist.orderid) as 品名,orderhist.parcdt as 投递日期 From orderhist, CONTACT, ems, PHONE, Address, Topic, usr, grp,orderdet where orderhist.contactid = contact.contactid and orderhist.spellid = ems.spellid(+) and phone.phoneid = (select min(phoneid) from phone where phone.contactid = orderhist.getcontactid) and orderhist.contactid = address.contactid and topic.contactid(+) = contact.contactid
在cmd 下sqlplus select dbms_lob.substr(sql_fulltext) from v$sql;ERROR: ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小 ORA-06512: 在 line 1
select * from v$parameters
where name like 'db%';更改db_cache_size大小
alter system set db_cache_size=25165824 scope=both;
return varchar2 is returnProName varchar2(3000);
v_proName varchar2(500);
v_num number(10);
v_result varchar2(1000);
cursor c_proName is
select decode(od.prodname,'','',od.prodname) || '*' ||decode(od.upnum,0,'',od.upnum) || decode(od.spnum,0,'',od.spnum) from orderdet od,orderhist oh where od.orderid=oh.orderid;
begin
open c_proName;
loop
fetch c_proName into v_result;
exit when c_proName%notfound;
returnProName:=returnProName ||' ' || v_result;
end loop;
close c_proName;
return (returnProName);
end NewFormatName;写了个函数,运行函数没问题,但是在SQL中调用的时候报错:ORA-06502 PL/SQL 数字或值错误,指向returnProName:=returnProName ||' ' || v_result;这一行调用的SQL:
select NewFormatName(orderhist.orderid) as 品名,orderhist.parcdt as 投递日期
From orderhist, CONTACT, ems, PHONE, Address, Topic, usr, grp,orderdet
where orderhist.contactid = contact.contactid
and orderhist.spellid = ems.spellid(+)
and phone.phoneid =
(select min(phoneid) from phone where phone.contactid = orderhist.getcontactid)
and orderhist.contactid = address.contactid
and topic.contactid(+) = contact.contactid
select dbms_lob.substr(sql_fulltext) from v$sql;ERROR:
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
ORA-06512: 在 line 1