各位大侠,以下是我写的PL\SQL,运行时报如下错误:
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
ORA-06512: 在 line 3636行是代码中红字加粗的行。我在网上查了一下,这个错误是由于没有给变量设置长度造成的,但是我也设了,所以想请教一下有经验的朋友,谢谢!declarev_ancestor_string varchar(255);
v_ancestor varchar(35);
v_descendent varchar(35);
v_vehicle "gww_weld".vehicle%type;
cursor c_gww_weld is
select trim(item) item, vehicle from "gww_weld"
for update;cursor c_ancestor (cv_descendent in mbm.bprod%type) is
select bprod from mesv12.mbm x
where bmwhs='CS' and bprod not like 'CS%'
and not exists (select * from mesv12.mbm y where x.bprod=y.bchld and bmwhs='CS' and bprod not like 'CS%')--and level=1
start with bchld=cv_descendent
connect by prior bprod=bchld;begin
open c_gww_weld; loop
v_ancestor_string:='';
v_ancestor:='';
v_descendent:='';
fetch c_gww_weld into v_descendent,v_vehicle;
EXIT WHEN c_gww_weld%NOTFOUND OR c_gww_weld%NOTFOUND IS NULL;
open c_ancestor (v_descendent);
loop
v_ancestor:='';
fetch c_ancestor into v_ancestor;
EXIT WHEN c_ancestor%NOTFOUND OR c_ancestor%NOTFOUND IS NULL;
v_ancestor_string:=trim(v_ancestor_string)||';'||trim(v_ancestor);
end loop;
update "gww_weld" set vehicle=v_ancestor_string where current of c_gww_weld; end loop;
end;
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
ORA-06512: 在 line 3636行是代码中红字加粗的行。我在网上查了一下,这个错误是由于没有给变量设置长度造成的,但是我也设了,所以想请教一下有经验的朋友,谢谢!declarev_ancestor_string varchar(255);
v_ancestor varchar(35);
v_descendent varchar(35);
v_vehicle "gww_weld".vehicle%type;
cursor c_gww_weld is
select trim(item) item, vehicle from "gww_weld"
for update;cursor c_ancestor (cv_descendent in mbm.bprod%type) is
select bprod from mesv12.mbm x
where bmwhs='CS' and bprod not like 'CS%'
and not exists (select * from mesv12.mbm y where x.bprod=y.bchld and bmwhs='CS' and bprod not like 'CS%')--and level=1
start with bchld=cv_descendent
connect by prior bprod=bchld;begin
open c_gww_weld; loop
v_ancestor_string:='';
v_ancestor:='';
v_descendent:='';
fetch c_gww_weld into v_descendent,v_vehicle;
EXIT WHEN c_gww_weld%NOTFOUND OR c_gww_weld%NOTFOUND IS NULL;
open c_ancestor (v_descendent);
loop
v_ancestor:='';
fetch c_ancestor into v_ancestor;
EXIT WHEN c_ancestor%NOTFOUND OR c_ancestor%NOTFOUND IS NULL;
v_ancestor_string:=trim(v_ancestor_string)||';'||trim(v_ancestor);
end loop;
update "gww_weld" set vehicle=v_ancestor_string where current of c_gww_weld; end loop;
end;
因为oracle不保证varchar在不同版本是一样的。pl/sql中varchar2最大32767字节
用在表列和sql语句最大4000
尽量声明v_ancestor_string大一些
不行就debug 一下啊