错误如下:ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
ORA-06512: 在 "CWZBTG.PROC_UPDATE_USER", line 18
光标落在*那行
代码:
create or replace procedure PROC_update_user is
CURSOR cur_update_user IS
select a.own||a.id_card as userid, nvl(a.id,'-99') as cwid, nvl(a.name,'') as username,
nvl(b.wzdeptid,'00') as wzdeptid,a.own as baseapt,nvl(a.deptname,'') as deptname
from dd_da_archive a,AC_DEPTCONTRAST b
where a.own=b.own and a.deptname=b.deptname and a.id_card is not null
and a.own is not null and a.own||a.id_card in (select userid from ac_user);
/**????*/
zb_userid varchar(30);
zb_cwid varchar(10);
zb_username varchar(18);
zb_wzdeptid varchar(6);
zb_baseapt varchar(20);
zb_deptname varchar(20);
begin
open cur_update_user;
LOOP
* FETCH cur_update_user INTO zb_userid ,zb_cwid,zb_username ,zb_wzdeptid,zb_baseapt,zb_deptname;
IF(cur_update_user%FOUND) THEN
/**????????????????????*/
update ac_user set cwid=zb_cwid,username=zb_username,wzdeptid=zb_wzdeptid,deptname=zb_deptname
where userid=zb_userid and baseapt=zb_baseapt;
commit;
ELSE
EXIT;
END IF;
end loop;
CLOSE cur_update_user;
end PROC_update_user;
ORA-06512: 在 "CWZBTG.PROC_UPDATE_USER", line 18
光标落在*那行
代码:
create or replace procedure PROC_update_user is
CURSOR cur_update_user IS
select a.own||a.id_card as userid, nvl(a.id,'-99') as cwid, nvl(a.name,'') as username,
nvl(b.wzdeptid,'00') as wzdeptid,a.own as baseapt,nvl(a.deptname,'') as deptname
from dd_da_archive a,AC_DEPTCONTRAST b
where a.own=b.own and a.deptname=b.deptname and a.id_card is not null
and a.own is not null and a.own||a.id_card in (select userid from ac_user);
/**????*/
zb_userid varchar(30);
zb_cwid varchar(10);
zb_username varchar(18);
zb_wzdeptid varchar(6);
zb_baseapt varchar(20);
zb_deptname varchar(20);
begin
open cur_update_user;
LOOP
* FETCH cur_update_user INTO zb_userid ,zb_cwid,zb_username ,zb_wzdeptid,zb_baseapt,zb_deptname;
IF(cur_update_user%FOUND) THEN
/**????????????????????*/
update ac_user set cwid=zb_cwid,username=zb_username,wzdeptid=zb_wzdeptid,deptname=zb_deptname
where userid=zb_userid and baseapt=zb_baseapt;
commit;
ELSE
EXIT;
END IF;
end loop;
CLOSE cur_update_user;
end PROC_update_user;
declare
n varchar(10);
begin
n := '中華人民共和國';
dbms_output.put_line(n);
end;Error at line 1
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4declare
n nvarchar2(10);
begin
n := '中華人民共和國';
dbms_output.put_line(n);
end;中華人民共和國
你是调用过程的时候出错 还是过程有问题
--看看这样看
create or replace procedure PROC_update_user is
CURSOR cur_update_user IS
select a.own||a.id_card as userid, nvl(a.id,'-99') as cwid, nvl(a.name,'') as username,
nvl(b.wzdeptid,'00') as wzdeptid,a.own as baseapt,nvl(a.deptname,'') as deptname
from dd_da_archive a,AC_DEPTCONTRAST b
where a.own=b.own and a.deptname=b.deptname and a.id_card is not null
and a.own is not null and (a.own||a.id_card) in (select userid from ac_user);
zb_userid varchar(50);
zb_cwid varchar(50);
zb_username varchar(100);
zb_wzdeptid varchar(50);
zb_baseapt varchar(50);
zb_deptname varchar(50);
begin
open cur_update_user;
FETCH cur_update_user INTO zb_userid,zb_cwid,zb_username,zb_wzdeptid,zb_baseapt,zb_deptname;
while cur_update_user%found LOOP
update ac_user set cwid=zb_cwid,username=zb_username,wzdeptid=zb_wzdeptid,deptname=zb_deptname
where userid=zb_userid and baseapt=zb_baseapt;
FETCH cur_update_user INTO zb_userid ,zb_cwid,zb_username ,zb_wzdeptid,zb_baseapt,zb_deptname;
end loop;
CLOSE cur_update_user;
commit;
end PROC_update_user;
zb_cwid varchar(10);
zb_username varchar(18);
zb_wzdeptid varchar(6);
zb_baseapt varchar(20);
zb_deptname varchar(20);应该是你这几个变量定义有问题。域宽度小了