在行 1 上开始执行命令时出错:
declare
aa number;
bb varchar2(1024);
begin
select count(*),Min(a.s_noticeid)
into aa,bb
from BD_NOTICEPUBLISH a join bd_notice b on a.s_noticeid=b.s_noticeid
where a.S_ISDOWNLOAD='0' and a.s_posid='100000000012' order by a.S_NOTICEID;
--dbms_output(aa||bb);
end;
错误报告:
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
ORA-06512: 在 line 5
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause:
*Action:s_noticeid是个char(10)型的,怎么会缓冲区不够?我单独执行:
select count(*),Min(a.s_noticeid)
from BD_NOTICEPUBLISH a join bd_notice b on a.s_noticeid=b.s_noticeid
where a.S_ISDOWNLOAD='0' and a.s_posid='100000000012' order by a.S_NOTICEID;
输出结果是:
-----------------
2,2010042701就一点问题都没有,我定义的aa和bb已经足够大了,求高人指点
declare
aa number;
bb varchar2(1024);
begin
select count(*),Min(a.s_noticeid)
into aa,bb
from BD_NOTICEPUBLISH a join bd_notice b on a.s_noticeid=b.s_noticeid
where a.S_ISDOWNLOAD='0' and a.s_posid='100000000012' order by a.S_NOTICEID;
--dbms_output(aa||bb);
end;
错误报告:
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
ORA-06512: 在 line 5
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause:
*Action:s_noticeid是个char(10)型的,怎么会缓冲区不够?我单独执行:
select count(*),Min(a.s_noticeid)
from BD_NOTICEPUBLISH a join bd_notice b on a.s_noticeid=b.s_noticeid
where a.S_ISDOWNLOAD='0' and a.s_posid='100000000012' order by a.S_NOTICEID;
输出结果是:
-----------------
2,2010042701就一点问题都没有,我定义的aa和bb已经足够大了,求高人指点
aa number;
bb varchar2(1024); --1)将bb声明为number: bb number;
begin
--2)min(a.s_noticeid)改成min(to_number(a.s_noticeid))
select count(*),Min(a.s_noticeid)
into aa,bb
from BD_NOTICEPUBLISH a join bd_notice b on a.s_noticeid=b.s_noticeid
where a.S_ISDOWNLOAD='0' and a.s_posid='100000000012' order by a.S_NOTICEID;
--dbms_output(aa||bb);
end;
你可以这样定义
bb varchar2(32767);
然后打印
dbms_output(length(bb));
dbms_output(bb);
这样的话min('abcd')岂不会在隐式转换中出错了
我觉得可以测试下length(Min(a.s_noticeid)),看看这个结果的长度