ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小解决办法
1.今天写的存储过程在执行过程中，报如下错误。
exec PRO_T_008pro_update_add_delete(17,1,1,1,1,45.0,54.0,45.0,45.0,45.0,54.0,45.0,54.0,'生产厂家','CYB10-2',54.0,45.0,25.0,1.0,45.0,25.0,1.0,45.0,25.0,1.0,45.0,1,4545.0,0,0,0,'no');
begin PRO_T_008pro_update_add_delete(17,1,1,1,1,45.0,54.0,45.0,45.0,45.0,54.0,45.0,54.0,'生产厂家','CYB10-2',54.0,45.0,25.0,1.0,45.0,25.0,1.0,45.0,25.0,1.0,45.0,1,4545.0,0,0,0,'no'); end;
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
ORA-06512: 在 "SMART.PRO_T_008PRO_UPDATE_ADD_DELETE", line 168
ORA-01422: 实际返回的行数超出请求的行数
ORA-06512: 在 line 2
解决办法：
存储过程中定义的变量值太小，加大它的范围即可。
p_dymbh number(38);
p_bbh number(38);
p_upbbh number(38);
原本我设置的是10
过程中定义了一个变量 a varchar2(2); -- a的长度为2 而在赋值时 a := '123123'; -- '123123'的长度为6 这样就会出现你所遇到的问题，解决方法是找到变量，将其长度加大，注意：plsql中varchar2长度上限是4000
create or replace procedure PRO_T_008pro_update_add_delete(v_jbh in number,
v_jslx in number,
v_qtlx in number,
v_sfpfcl in number,
v_ccjzt in number,
v_hsl in number,
v_yy in number,
v_ty in number,
v_hy in number,
v_dym in number,
v_bg in number,
v_jklw in number,
v_scqyb in number,
v_sccj in varchar2,
v_cybxh in varchar2,
v_ygnj in number,
v_yctgnj in number,
v_yjgj in number,
v_yjgjb in number,
v_yjgcd in number,
v_ejgj in number,
v_ejgjb in number,
v_ejgcd in number,
v_sjgj in number,
v_sjgjb in number,
v_sjgcd in number,
v_mdzt in number,
v_clxs in number,
v_bzgtbh in number,
v_bzdntbh in number,
v_id in number,
v_ids in varchar2) is
p_msg varchar2(300) := 'error';
p_dymbh number(38);
p_bbh number(38);
p_upbbh number(38);
begin
If v_ids <> 'no' then
EXECUTE IMMEDIATE ' delete from
T_008_OUTPUTWELLPRODUCTION t where t.jlbh in (' ||
v_ids || ')';
commit;
p_msg := '删除成功';
elsif v_id = 0 then
--insert 动液面数据
insert into T_011_DYNAMICLIQUIDLEVEL (jbh, dym) values (v_jbh, v_dym);
select distinct w.jlbh as jlbh
into p_dymbh
from T_011_DYNAMICLIQUIDLEVEL w
where w.jbh = v_jbh;
--insert 泵信息
select distinct w.jlbh as jlbh
into p_bbh
from t_023_pump w
where w.sccj = v_sccj
and w.cybxh = v_cybxh;
insert into T_008_OUTPUTWELLPRODUCTION
(jbh,
jslx,
qtlx,
sfpfcl,
ccjzt,
hsl,
yy,
ty,
hy,
bg,
jklw,
scqyb,
ygnj,
yctgnj,
yjgj,
yjgjb,
yjgcd,
ejgj,
ejgjb,
ejgcd,
sjgj,
sjgjb,
sjgcd,
mdzt,
clxs,
bzgtbh,
bzdntbh,
dymbh,
bbh)
values
(v_jbh,
v_jslx,
v_qtlx,
v_sfpfcl,
v_ccjzt,
v_hsl,
v_yy,
v_ty,
v_hy,
v_bg,
v_jklw,
v_scqyb,
v_ygnj,
v_yctgnj,
v_yjgj,
v_yjgjb,
v_yjgcd,
v_ejgj,
v_ejgjb,
v_ejgcd,
v_sjgj,
v_sjgjb,
v_sjgcd,
v_mdzt,
v_clxs,
v_bzgtbh,
v_bzdntbh,
p_dymbh,
p_bbh);
commit;
p_msg := '添加成功';
elsif v_id > 0 then
--修改数据
update T_011_DYNAMICLIQUIDLEVEL d
set d.dym = v_dym
where d.jbh = v_jbh;
select distinct w.jlbh as jlbh
into p_upbbh
from t_023_pump w
where w.sccj = v_sccj
and w.cybxh = v_cybxh;
---------------------------------------------------
Update T_008_OUTPUTWELLPRODUCTION t
set jbh = v_jbh,
jslx = v_jslx,
qtlx = v_qtlx,
sfpfcl = v_sfpfcl,
ccjzt = v_ccjzt,
hsl = v_hsl,
yy = v_yy,
ty = v_ty,
hy = v_hy,
bg = v_bg,
jklw = v_jklw,
scqyb = v_scqyb,
ygnj = v_ygnj,
yctgnj = v_yctgnj,
yjgj = v_yjgj,
yjgjb = v_yjgjb,
yjgcd = v_yjgcd,
ejgj = v_ejgj,
ejgjb = v_ejgjb,
ejgcd = v_ejgcd,
sjgj = v_sjgj,
sjgjb = v_sjgjb,
sjgcd = v_sjgcd,
mdzt = v_mdzt,
clxs = v_clxs,
bzgtbh = v_bzgtbh,
bzdntbh = v_bzdntbh,
bbh=p_upbbh
Where t.jlbh = v_id;
commit;
p_msg := '修改成功';
end if;
dbms_output.put_line('p_msg:' || p_msg);
Exception
When Others Then
p_msg := Sqlerrm || ',' || '操作失败';
dbms_output.put_line('p_msg:' || p_msg);
end PRO_T_008pro_update_add_delete;
