java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 1 ;
数字或值错误,字符缓冲区太小。
后台调用存储过程报错,但是并没找到可以越字段的地方。存储过程如下。
create or replace function df_f_cxglyh( in_user_no varchar2,
in_dept_code varchar2,
in_area_code varchar2,
out_user_cursor out highsoft_types.dscursor)
return varchar2
is
v_result number;
v_msg varchar2(500);
v_flag integer;
v_count integer;
v_cbqdbh varchar2(20);
begin
--清空临时表
execute immediate 'truncate table tmp_df_yhda_glyh' ;
v_result := -10;
v_msg := '查询用户出错'; if in_area_code is not null then
select a.cbqdbh into v_cbqdbh from kh_ydkh a where a.dqbm = in_area_code and a.yhbh = in_user_no;
--先增加用户本身
else
select a.cbqdbh into v_cbqdbh from kh_ydkh a where a.yhbh = in_user_no;
end if; --先增加用户本身
v_result := -15;
v_msg := '增加用户本身出错';
insert into tmp_df_yhda_glyh
(yhbh, glbh)
values
(in_user_no,in_user_no);
v_result := -20;
v_msg := '查询用户点关系出错';
v_count := 0;
v_flag := 0;
select count(1) into v_count from
( select distinct a.glyhbh yhbh from kh_jldgx a
where a.yhbh = in_user_no and a.yhbh <> a.glyhbh and a.dqbm = in_area_code
and not exists (select 1 from tmp_df_yhda_glyh b where b.yhbh = in_user_no and b.glbh = a.glyhbh)
and exists (select 1 from kh_ydkh c where c.dqbm = in_area_code and c.yhbh = a.glyhbh and c.cbqdbh = v_cbqdbh)
union
select distinct a.yhbh yhbh from kh_jldgx a
where a.glyhbh = in_user_no and a.yhbh <> a.glyhbh and a.dqbm = in_area_code
and not exists (select 1 from tmp_df_yhda_glyh b where b.yhbh = in_user_no and b.glbh = a.yhbh)
and exists (select 1 from kh_ydkh c where c.dqbm = in_area_code and c.yhbh = a.yhbh and c.cbqdbh = v_cbqdbh) ); while v_count > 0 loop
if v_flag = 0 then
v_result := -30;
v_msg := '插入用户点关系出错';
insert into tmp_df_yhda_glyh
(yhbh, glbh)
select in_user_no, a.glyhbh from kh_jldgx a
where a.yhbh = in_user_no and a.yhbh <> a.glyhbh and a.dqbm = in_area_code
and not exists (select 1 from tmp_df_yhda_glyh b where b.yhbh = in_user_no and b.glbh = a.glyhbh)
and exists (select 1 from kh_ydkh c where c.dqbm = in_area_code and c.yhbh = a.glyhbh and c.cbqdbh = v_cbqdbh)
group by a.glyhbh
union
select in_user_no, a.yhbh from kh_jldgx a
where a.glyhbh = in_user_no and a.yhbh <> a.glyhbh and a.dqbm = in_area_code
and not exists (select 1 from tmp_df_yhda_glyh b where b.yhbh = in_user_no and b.glbh = a.yhbh)
and exists (select 1 from kh_ydkh c where c.dqbm = in_area_code and c.yhbh = a.yhbh and c.cbqdbh = v_cbqdbh)
group by a.yhbh;
else
v_result := -40;
v_msg := '插入用户点关系出错';
insert into tmp_df_yhda_glyh
(yhbh, glbh)
select in_user_no, a.glyhbh from kh_jldgx a
where a.yhbh <> a.glyhbh and a.dqbm = in_area_code
and exists (select 1 from tmp_df_yhda_glyh c where c.yhbh = in_user_no and c.glbh = a.yhbh)
and not exists (select 1 from tmp_df_yhda_glyh b where b.yhbh = in_user_no and b.glbh = a.glyhbh)
and exists (select 1 from kh_ydkh c where c.dqbm = in_area_code and c.yhbh = a.glyhbh and c.cbqdbh = v_cbqdbh)
group by a.glyhbh
union
select in_user_no, a.yhbh from kh_jldgx a
where a.yhbh <> a.glyhbh and a.dqbm = in_area_code
and exists (select 1 from tmp_df_yhda_glyh c where c.yhbh = in_user_no and c.glbh = a.glyhbh)
and not exists (select 1 from tmp_df_yhda_glyh b where b.yhbh = in_user_no and b.glbh = a.yhbh)
and exists (select 1 from kh_ydkh c where c.dqbm = in_area_code and c.yhbh = a.yhbh and c.cbqdbh = v_cbqdbh)
group by a.yhbh;
end if;
v_result := -50;
v_msg := '查询用户点关系出错';
select count(1) into v_count from
( select distinct a.glyhbh yhbh from kh_jldgx a
where a.yhbh <> a.glyhbh and a.dqbm = in_area_code
and exists (select 1 from tmp_df_yhda_glyh c where c.yhbh = in_user_no and c.glbh = a.yhbh)
and not exists (select 1 from tmp_df_yhda_glyh b where b.yhbh = in_user_no and b.glbh = a.glyhbh)
and exists (select 1 from kh_ydkh c where c.dqbm = in_area_code and c.yhbh = a.glyhbh and c.cbqdbh = v_cbqdbh)
union
select distinct a.yhbh yhbh from kh_jldgx a
where a.yhbh <> a.glyhbh and a.dqbm = in_area_code
and exists (select 1 from tmp_df_yhda_glyh c where c.yhbh = in_user_no and c.glbh = a.glyhbh)
and not exists (select 1 from tmp_df_yhda_glyh b where b.yhbh = in_user_no and b.glbh = a.yhbh)
and exists (select 1 from kh_ydkh c where c.dqbm = in_area_code and c.yhbh = a.yhbh and c.cbqdbh = v_cbqdbh) ); v_flag := v_flag + 1;
end loop; v_count := 0;
v_flag := 0;
v_result := -60;
v_msg := '查询用户点变压器关系出错';
select count(1) into v_count from
(select (select e.yhbh from kh_jld e where e.dqbm = a.dqbm and e.jldbh = a.jldbh) yhbh,
(select d.yhbh from kh_jld d where d.dqbm = a.dqbm and d.jldbh = b.jldbh) glyhbh
from kh_jldbyqgx a,kh_jldbyqgx b
where a.dqbm = in_area_code and b.sbbs = a.sbbs and b.dqbm = in_area_code and a.jldbh <> b.jldbh
and exists (select 1 from kh_jld c where c.dqbm = in_area_code and c.jldbh = a.jldbh and c.yhbh = in_user_no) )w
where w.yhbh <> w.glyhbh
and exists (select 1 from kh_ydkh f where f.dqbm = in_area_code and f.yhbh = w.glyhbh and f.cbqdbh = v_cbqdbh)
and not exists (select 1 from tmp_df_yhda_glyh g where g.yhbh = in_user_no and g.glbh = w.glyhbh); while v_count > 0 loop
if v_flag = 0 then
v_result := -70;
v_msg := '插入用户点变压器关系出错';
insert into tmp_df_yhda_glyh
(yhbh, glbh)
select distinct w.yhbh, w.glyhbh from
(select (select e.yhbh from kh_jld e where e.dqbm = a.dqbm and e.jldbh = a.jldbh) yhbh,
(select d.yhbh from kh_jld d where d.dqbm = a.dqbm and d.jldbh = b.jldbh) glyhbh
from kh_jldbyqgx a,kh_jldbyqgx b
where a.dqbm = in_area_code and b.sbbs = a.sbbs and b.dqbm = in_area_code and a.jldbh <> b.jldbh
and exists (select 1 from kh_jld c where c.dqbm = in_area_code and c.jldbh = a.jldbh and c.yhbh = in_user_no) )w
where w.yhbh <> w.glyhbh
and exists (select 1 from kh_ydkh f where f.dqbm = in_area_code and f.yhbh = w.glyhbh and f.cbqdbh = v_cbqdbh)
and not exists (select 1 from tmp_df_yhda_glyh g where g.yhbh = in_user_no and g.glbh = w.glyhbh);
else
v_result := -80;
v_msg := '插入用户点变压器关系出错';
insert into tmp_df_yhda_glyh
(yhbh, glbh)
select in_user_no, w.glyhbh from
(select (select e.yhbh from kh_jld e where e.dqbm = a.dqbm and e.jldbh = a.jldbh) yhbh,
(select d.yhbh from kh_jld d where d.dqbm = a.dqbm and d.jldbh = b.jldbh) glyhbh
from kh_jldbyqgx a,kh_jldbyqgx b
where a.dqbm = in_area_code and b.sbbs = a.sbbs and b.dqbm = in_area_code and a.jldbh <> b.jldbh
and exists (select 1 from kh_jld c, tmp_df_yhda_glyh h
where c.dqbm = in_area_code and c.jldbh = a.jldbh
and h.yhbh = in_user_no and c.yhbh = h.glbh) )w
where w.yhbh <> w.glyhbh
and exists (select 1 from kh_ydkh f where f.dqbm = in_area_code and f.yhbh = w.glyhbh and f.cbqdbh = v_cbqdbh)
and not exists (select 1 from tmp_df_yhda_glyh g where g.yhbh = in_user_no and g.glbh = w.glyhbh)
group by w.glyhbh;
end if;
v_result := -90;
v_msg := '查询用户点变压器关系出错';
select count(1) into v_count from
(select (select e.yhbh from kh_jld e where e.dqbm = a.dqbm and e.jldbh = a.jldbh) yhbh,
(select d.yhbh from kh_jld d where d.dqbm = a.dqbm and d.jldbh = b.jldbh) glyhbh
from kh_jldbyqgx a,kh_jldbyqgx b
where a.dqbm = in_area_code and b.sbbs = a.sbbs and b.dqbm = in_area_code and a.jldbh <> b.jldbh
and exists (select 1 from kh_jld c, tmp_df_yhda_glyh h
where c.dqbm = in_area_code and c.jldbh = a.jldbh
and h.yhbh = in_user_no and c.yhbh = h.glbh) )w
where w.yhbh <> w.glyhbh
and exists (select 1 from kh_ydkh f where f.dqbm = in_area_code and f.yhbh = w.glyhbh and f.cbqdbh = v_cbqdbh)
and not exists (select 1 from tmp_df_yhda_glyh g where g.yhbh = in_user_no and g.glbh = w.glyhbh); v_flag := v_flag + 1; end loop; open out_user_cursor for
select a.glbh from tmp_df_yhda_glyh a order by a.glbh; commit; return null; exception
when others then
rollback; open out_user_cursor for
select a.glbh from tmp_df_yhda_glyh a order by a.glbh; pkg_comm_util.p_pub_error_log(v_result, v_msg, 'df_f_cxglyh', in_user_no); return v_msg;end;
数字或值错误,字符缓冲区太小。
后台调用存储过程报错,但是并没找到可以越字段的地方。存储过程如下。
create or replace function df_f_cxglyh( in_user_no varchar2,
in_dept_code varchar2,
in_area_code varchar2,
out_user_cursor out highsoft_types.dscursor)
return varchar2
is
v_result number;
v_msg varchar2(500);
v_flag integer;
v_count integer;
v_cbqdbh varchar2(20);
begin
--清空临时表
execute immediate 'truncate table tmp_df_yhda_glyh' ;
v_result := -10;
v_msg := '查询用户出错'; if in_area_code is not null then
select a.cbqdbh into v_cbqdbh from kh_ydkh a where a.dqbm = in_area_code and a.yhbh = in_user_no;
--先增加用户本身
else
select a.cbqdbh into v_cbqdbh from kh_ydkh a where a.yhbh = in_user_no;
end if; --先增加用户本身
v_result := -15;
v_msg := '增加用户本身出错';
insert into tmp_df_yhda_glyh
(yhbh, glbh)
values
(in_user_no,in_user_no);
v_result := -20;
v_msg := '查询用户点关系出错';
v_count := 0;
v_flag := 0;
select count(1) into v_count from
( select distinct a.glyhbh yhbh from kh_jldgx a
where a.yhbh = in_user_no and a.yhbh <> a.glyhbh and a.dqbm = in_area_code
and not exists (select 1 from tmp_df_yhda_glyh b where b.yhbh = in_user_no and b.glbh = a.glyhbh)
and exists (select 1 from kh_ydkh c where c.dqbm = in_area_code and c.yhbh = a.glyhbh and c.cbqdbh = v_cbqdbh)
union
select distinct a.yhbh yhbh from kh_jldgx a
where a.glyhbh = in_user_no and a.yhbh <> a.glyhbh and a.dqbm = in_area_code
and not exists (select 1 from tmp_df_yhda_glyh b where b.yhbh = in_user_no and b.glbh = a.yhbh)
and exists (select 1 from kh_ydkh c where c.dqbm = in_area_code and c.yhbh = a.yhbh and c.cbqdbh = v_cbqdbh) ); while v_count > 0 loop
if v_flag = 0 then
v_result := -30;
v_msg := '插入用户点关系出错';
insert into tmp_df_yhda_glyh
(yhbh, glbh)
select in_user_no, a.glyhbh from kh_jldgx a
where a.yhbh = in_user_no and a.yhbh <> a.glyhbh and a.dqbm = in_area_code
and not exists (select 1 from tmp_df_yhda_glyh b where b.yhbh = in_user_no and b.glbh = a.glyhbh)
and exists (select 1 from kh_ydkh c where c.dqbm = in_area_code and c.yhbh = a.glyhbh and c.cbqdbh = v_cbqdbh)
group by a.glyhbh
union
select in_user_no, a.yhbh from kh_jldgx a
where a.glyhbh = in_user_no and a.yhbh <> a.glyhbh and a.dqbm = in_area_code
and not exists (select 1 from tmp_df_yhda_glyh b where b.yhbh = in_user_no and b.glbh = a.yhbh)
and exists (select 1 from kh_ydkh c where c.dqbm = in_area_code and c.yhbh = a.yhbh and c.cbqdbh = v_cbqdbh)
group by a.yhbh;
else
v_result := -40;
v_msg := '插入用户点关系出错';
insert into tmp_df_yhda_glyh
(yhbh, glbh)
select in_user_no, a.glyhbh from kh_jldgx a
where a.yhbh <> a.glyhbh and a.dqbm = in_area_code
and exists (select 1 from tmp_df_yhda_glyh c where c.yhbh = in_user_no and c.glbh = a.yhbh)
and not exists (select 1 from tmp_df_yhda_glyh b where b.yhbh = in_user_no and b.glbh = a.glyhbh)
and exists (select 1 from kh_ydkh c where c.dqbm = in_area_code and c.yhbh = a.glyhbh and c.cbqdbh = v_cbqdbh)
group by a.glyhbh
union
select in_user_no, a.yhbh from kh_jldgx a
where a.yhbh <> a.glyhbh and a.dqbm = in_area_code
and exists (select 1 from tmp_df_yhda_glyh c where c.yhbh = in_user_no and c.glbh = a.glyhbh)
and not exists (select 1 from tmp_df_yhda_glyh b where b.yhbh = in_user_no and b.glbh = a.yhbh)
and exists (select 1 from kh_ydkh c where c.dqbm = in_area_code and c.yhbh = a.yhbh and c.cbqdbh = v_cbqdbh)
group by a.yhbh;
end if;
v_result := -50;
v_msg := '查询用户点关系出错';
select count(1) into v_count from
( select distinct a.glyhbh yhbh from kh_jldgx a
where a.yhbh <> a.glyhbh and a.dqbm = in_area_code
and exists (select 1 from tmp_df_yhda_glyh c where c.yhbh = in_user_no and c.glbh = a.yhbh)
and not exists (select 1 from tmp_df_yhda_glyh b where b.yhbh = in_user_no and b.glbh = a.glyhbh)
and exists (select 1 from kh_ydkh c where c.dqbm = in_area_code and c.yhbh = a.glyhbh and c.cbqdbh = v_cbqdbh)
union
select distinct a.yhbh yhbh from kh_jldgx a
where a.yhbh <> a.glyhbh and a.dqbm = in_area_code
and exists (select 1 from tmp_df_yhda_glyh c where c.yhbh = in_user_no and c.glbh = a.glyhbh)
and not exists (select 1 from tmp_df_yhda_glyh b where b.yhbh = in_user_no and b.glbh = a.yhbh)
and exists (select 1 from kh_ydkh c where c.dqbm = in_area_code and c.yhbh = a.yhbh and c.cbqdbh = v_cbqdbh) ); v_flag := v_flag + 1;
end loop; v_count := 0;
v_flag := 0;
v_result := -60;
v_msg := '查询用户点变压器关系出错';
select count(1) into v_count from
(select (select e.yhbh from kh_jld e where e.dqbm = a.dqbm and e.jldbh = a.jldbh) yhbh,
(select d.yhbh from kh_jld d where d.dqbm = a.dqbm and d.jldbh = b.jldbh) glyhbh
from kh_jldbyqgx a,kh_jldbyqgx b
where a.dqbm = in_area_code and b.sbbs = a.sbbs and b.dqbm = in_area_code and a.jldbh <> b.jldbh
and exists (select 1 from kh_jld c where c.dqbm = in_area_code and c.jldbh = a.jldbh and c.yhbh = in_user_no) )w
where w.yhbh <> w.glyhbh
and exists (select 1 from kh_ydkh f where f.dqbm = in_area_code and f.yhbh = w.glyhbh and f.cbqdbh = v_cbqdbh)
and not exists (select 1 from tmp_df_yhda_glyh g where g.yhbh = in_user_no and g.glbh = w.glyhbh); while v_count > 0 loop
if v_flag = 0 then
v_result := -70;
v_msg := '插入用户点变压器关系出错';
insert into tmp_df_yhda_glyh
(yhbh, glbh)
select distinct w.yhbh, w.glyhbh from
(select (select e.yhbh from kh_jld e where e.dqbm = a.dqbm and e.jldbh = a.jldbh) yhbh,
(select d.yhbh from kh_jld d where d.dqbm = a.dqbm and d.jldbh = b.jldbh) glyhbh
from kh_jldbyqgx a,kh_jldbyqgx b
where a.dqbm = in_area_code and b.sbbs = a.sbbs and b.dqbm = in_area_code and a.jldbh <> b.jldbh
and exists (select 1 from kh_jld c where c.dqbm = in_area_code and c.jldbh = a.jldbh and c.yhbh = in_user_no) )w
where w.yhbh <> w.glyhbh
and exists (select 1 from kh_ydkh f where f.dqbm = in_area_code and f.yhbh = w.glyhbh and f.cbqdbh = v_cbqdbh)
and not exists (select 1 from tmp_df_yhda_glyh g where g.yhbh = in_user_no and g.glbh = w.glyhbh);
else
v_result := -80;
v_msg := '插入用户点变压器关系出错';
insert into tmp_df_yhda_glyh
(yhbh, glbh)
select in_user_no, w.glyhbh from
(select (select e.yhbh from kh_jld e where e.dqbm = a.dqbm and e.jldbh = a.jldbh) yhbh,
(select d.yhbh from kh_jld d where d.dqbm = a.dqbm and d.jldbh = b.jldbh) glyhbh
from kh_jldbyqgx a,kh_jldbyqgx b
where a.dqbm = in_area_code and b.sbbs = a.sbbs and b.dqbm = in_area_code and a.jldbh <> b.jldbh
and exists (select 1 from kh_jld c, tmp_df_yhda_glyh h
where c.dqbm = in_area_code and c.jldbh = a.jldbh
and h.yhbh = in_user_no and c.yhbh = h.glbh) )w
where w.yhbh <> w.glyhbh
and exists (select 1 from kh_ydkh f where f.dqbm = in_area_code and f.yhbh = w.glyhbh and f.cbqdbh = v_cbqdbh)
and not exists (select 1 from tmp_df_yhda_glyh g where g.yhbh = in_user_no and g.glbh = w.glyhbh)
group by w.glyhbh;
end if;
v_result := -90;
v_msg := '查询用户点变压器关系出错';
select count(1) into v_count from
(select (select e.yhbh from kh_jld e where e.dqbm = a.dqbm and e.jldbh = a.jldbh) yhbh,
(select d.yhbh from kh_jld d where d.dqbm = a.dqbm and d.jldbh = b.jldbh) glyhbh
from kh_jldbyqgx a,kh_jldbyqgx b
where a.dqbm = in_area_code and b.sbbs = a.sbbs and b.dqbm = in_area_code and a.jldbh <> b.jldbh
and exists (select 1 from kh_jld c, tmp_df_yhda_glyh h
where c.dqbm = in_area_code and c.jldbh = a.jldbh
and h.yhbh = in_user_no and c.yhbh = h.glbh) )w
where w.yhbh <> w.glyhbh
and exists (select 1 from kh_ydkh f where f.dqbm = in_area_code and f.yhbh = w.glyhbh and f.cbqdbh = v_cbqdbh)
and not exists (select 1 from tmp_df_yhda_glyh g where g.yhbh = in_user_no and g.glbh = w.glyhbh); v_flag := v_flag + 1; end loop; open out_user_cursor for
select a.glbh from tmp_df_yhda_glyh a order by a.glbh; commit; return null; exception
when others then
rollback; open out_user_cursor for
select a.glbh from tmp_df_yhda_glyh a order by a.glbh; pkg_comm_util.p_pub_error_log(v_result, v_msg, 'df_f_cxglyh', in_user_no); return v_msg;end;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货