ORA-06502：PLSQL：数字或值错误：字符串缓冲区太⼩解决办
法
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_mdzt in number,
v_clxs in number,
v_bzgtbh in number,
delete inv_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
bxh = 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_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
bxh = 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;
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;
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论