sql数据少的时候正常，当大了就报错！
10 个解决方案
#1
超过了字符串缓冲区的大小
应该是dbms_output输出吧
应该是dbms_output输出吧
#2
请问楼上的dbms_output怎么改啊，我对数据库不太会用，平时做开发的，现在用到了这个函数，请说仔细点。
我的数据库是oracle 10 谢谢。
我的数据库是oracle 10 谢谢。
#3
樓主出錯的代碼是？
#4
insert into fisheruser.mesage(msgtime,fromnumber,tonumber,fromposition,toposition,msg)
(select tablea ttime, tablea.fromnumber, tablea.numberx,
nvl((select c.position from fisheruser.msgpos c where to_number(substr(tablea.fromnumber(0,7)) =c.telenumber),'未知'),
nvl((select c.position from fisheruser.msgpos c where to_number(substr(tablea.numberx(0,7)) =c.telenumber),'未知'), tablea.msg
from
(
(select to_date(min(msgtime),'yyyy-mm-dd hh24:mi:ss') as ttime, fromnumber,
replace(wm_concat(tonumber),',',',') as numberx, msg from fisheruser.temptable016
group by fromnumber,msg) tablea))
红色的地方报错
(select tablea ttime, tablea.fromnumber, tablea.numberx,
nvl((select c.position from fisheruser.msgpos c where to_number(substr(tablea.fromnumber(0,7)) =c.telenumber),'未知'),
nvl((select c.position from fisheruser.msgpos c where to_number(substr(tablea.numberx(0,7)) =c.telenumber),'未知'), tablea.msg
from
(
(select to_date(min(msgtime),'yyyy-mm-dd hh24:mi:ss') as ttime, fromnumber,
replace(wm_concat(tonumber),',',',') as numberx, msg from fisheruser.temptable016
group by fromnumber,msg) tablea))
红色的地方报错
#5
很正常，wo_concat是oracle提供的系统函数，但是也要遵循oracle的语法规则，oracle里的varchar2长度不允许超过4000的，你超过了肯定会报错。
#6
我觉得楼上的应该是对的，但是怎么改啊？谢谢。
#7
replace(wm_concat(tonumber),',',',')
这个出来的结果很长么？
这个出来的结果很长么？
#8
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.
上面是oracle的文档，说是要改正你的错误的方法是改数据，所以你的问题趋近于无解，除非数据满足条件<=4000
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.
上面是oracle的文档，说是要改正你的错误的方法是改数据，所以你的问题趋近于无解，除非数据满足条件<=4000
#9
我估计明白错哪里了
fisheruser.mesage这个表的tonumber字段长度定义的时候是不是小与replace(wm_concat(tonumber),',',',') 出来的字符串长度..
fisheruser.mesage这个表的tonumber字段长度定义的时候是不是小与replace(wm_concat(tonumber),',',',') 出来的字符串长度..
#10
谢谢楼上的兄弟姐妹，问题已经解决了。
我在前面加了一个判断
select * from fisheruser.temptable016 group by fromnumber,msg having count(tonumber>100)。
的单独处理之后，就没有问题了。再次谢谢各位。
我在前面加了一个判断
select * from fisheruser.temptable016 group by fromnumber,msg having count(tonumber>100)。
的单独处理之后，就没有问题了。再次谢谢各位。
#1
超过了字符串缓冲区的大小
应该是dbms_output输出吧
应该是dbms_output输出吧
#2
请问楼上的dbms_output怎么改啊，我对数据库不太会用，平时做开发的，现在用到了这个函数，请说仔细点。
我的数据库是oracle 10 谢谢。
我的数据库是oracle 10 谢谢。
#3
樓主出錯的代碼是？
#4
insert into fisheruser.mesage(msgtime,fromnumber,tonumber,fromposition,toposition,msg)
(select tablea ttime, tablea.fromnumber, tablea.numberx,
nvl((select c.position from fisheruser.msgpos c where to_number(substr(tablea.fromnumber(0,7)) =c.telenumber),'未知'),
nvl((select c.position from fisheruser.msgpos c where to_number(substr(tablea.numberx(0,7)) =c.telenumber),'未知'), tablea.msg
from
(
(select to_date(min(msgtime),'yyyy-mm-dd hh24:mi:ss') as ttime, fromnumber,
replace(wm_concat(tonumber),',',',') as numberx, msg from fisheruser.temptable016
group by fromnumber,msg) tablea))
红色的地方报错
(select tablea ttime, tablea.fromnumber, tablea.numberx,
nvl((select c.position from fisheruser.msgpos c where to_number(substr(tablea.fromnumber(0,7)) =c.telenumber),'未知'),
nvl((select c.position from fisheruser.msgpos c where to_number(substr(tablea.numberx(0,7)) =c.telenumber),'未知'), tablea.msg
from
(
(select to_date(min(msgtime),'yyyy-mm-dd hh24:mi:ss') as ttime, fromnumber,
replace(wm_concat(tonumber),',',',') as numberx, msg from fisheruser.temptable016
group by fromnumber,msg) tablea))
红色的地方报错
#5
很正常，wo_concat是oracle提供的系统函数，但是也要遵循oracle的语法规则，oracle里的varchar2长度不允许超过4000的，你超过了肯定会报错。
#6
我觉得楼上的应该是对的，但是怎么改啊？谢谢。
#7
replace(wm_concat(tonumber),',',',')
这个出来的结果很长么？
这个出来的结果很长么？
#8
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.
上面是oracle的文档，说是要改正你的错误的方法是改数据，所以你的问题趋近于无解，除非数据满足条件<=4000
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.
上面是oracle的文档，说是要改正你的错误的方法是改数据，所以你的问题趋近于无解，除非数据满足条件<=4000
#9
我估计明白错哪里了
fisheruser.mesage这个表的tonumber字段长度定义的时候是不是小与replace(wm_concat(tonumber),',',',') 出来的字符串长度..
fisheruser.mesage这个表的tonumber字段长度定义的时候是不是小与replace(wm_concat(tonumber),',',',') 出来的字符串长度..
#10
谢谢楼上的兄弟姐妹，问题已经解决了。
我在前面加了一个判断
select * from fisheruser.temptable016 group by fromnumber,msg having count(tonumber>100)。
的单独处理之后，就没有问题了。再次谢谢各位。
我在前面加了一个判断
select * from fisheruser.temptable016 group by fromnumber,msg having count(tonumber>100)。
的单独处理之后，就没有问题了。再次谢谢各位。