14 个解决方案
#1
查一下sga的大小
select * from v$parameters
where name like 'db%';
更改db_cache_size大小
alter system set db_cache_size=25165824 scope=both;
select * from v$parameters
where name like 'db%';
更改db_cache_size大小
alter system set db_cache_size=25165824 scope=both;
#2
可能原因：字段长度小了
#3
ORA-06502 PL/SQL: numeric or value error string
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.
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.
#4
新老大正解啊！
#5
没效果啊。。
#6
从三楼看，应该是某个字符类型的变量或字段的定义长度太短了。
#7
应该是定义字符串宽度太小了（即赋值大于变量的宽度）
#8
试试这个了没有。
#9
haha VARCHAR2 (4000)
#10
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
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
#11
这个要具体看看问题的上下文了，是什么情况下出的错，还有更详细的信息么
#12
在cmd 下sqlplus
select dbms_lob.substr(sql_fulltext) from v$sql;
ERROR:
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
ORA-06512: 在 line 1
select dbms_lob.substr(sql_fulltext) from v$sql;
ERROR:
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
ORA-06512: 在 line 1
#13
很显然是数据记录太多，用游标取出来后的长度不断增加，超过了returnProName的长度上限。
#14
dbms_output.enable(1000000);
#1
查一下sga的大小
select * from v$parameters
where name like 'db%';
更改db_cache_size大小
alter system set db_cache_size=25165824 scope=both;
select * from v$parameters
where name like 'db%';
更改db_cache_size大小
alter system set db_cache_size=25165824 scope=both;
#2
可能原因：字段长度小了
#3
ORA-06502 PL/SQL: numeric or value error string
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.
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.
#4
新老大正解啊！
#5
没效果啊。。
#6
从三楼看，应该是某个字符类型的变量或字段的定义长度太短了。
#7
应该是定义字符串宽度太小了（即赋值大于变量的宽度）
#8
试试这个了没有。
#9
haha VARCHAR2 (4000)
#10
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
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
#11
这个要具体看看问题的上下文了，是什么情况下出的错，还有更详细的信息么
#12
在cmd 下sqlplus
select dbms_lob.substr(sql_fulltext) from v$sql;
ERROR:
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
ORA-06512: 在 line 1
select dbms_lob.substr(sql_fulltext) from v$sql;
ERROR:
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
ORA-06512: 在 line 1
#13
很显然是数据记录太多，用游标取出来后的长度不断增加，超过了returnProName的长度上限。
#14
dbms_output.enable(1000000);