求助：存储过程执行报错：ORA-06502: PL/SQL: 数字或值错误

[RaincyXP]

ORA-02055: 分布式更新操作失效；要求回退
ORA-06502: PL/SQL: 数字或值错误
ORA-06512: 在"CAP.P_TEST_SHE", line 16
跟踪检查发现，执行Insert没有任何问题，但执行Update操作，无论如何都出错。
检查了好久不得要法，请各位大侠指点！
数据库是oracle 9i v9.2.0.6

create or replace procedure P_TEST_SHE
as

v_IsDouble number;
v_TypeName varchar2(50);

begin
v_TypeName := 't_vorg_employ';
Update e3s.t_imp_e3sif2capif@e3sifdb Set Status='1' Where TypeName=v_TypeName;

Select count(1) into v_IsDouble From e3s.t_imp_e3sif2capif@e3sifdb Where Status='1' And TypeName=v_TypeName;

If v_IsDouble >0 then

For j in (Select a.EMPID,a.cname,a.EmpCode,a.id,a.Sex,a.Birthday,
(Select substrb(Name, 1, 50) From e3s.t_base_data @e3sifdb Where Code = a.Degree and Type = 26) Degree,
a.EMPLOYDATE,a.BusinessDate,a.MPhone,a.PHONE_C,
Substrb(a.ENAME,1,20) EName,Substrb(a.ADDR_H,1,50) Address,SubStrb(a.ZIPCODE,1,6) Zip,
a.mdate,
decode(status,'1',1,0) IsEnabled,
decode(status,'0',1,0) HasCanceled,
1 issystem
FROM e3s.t_vorg_employ @e3sifdb a
WHERE to_char(a.EMPID) in (Select keyvalue from e3s.t_imp_e3sif2capif@e3sifdb where status='1' and TypeName=v_TypeName)
and exists (select 'X' from cap.tSTOU_Employee Where EmployeeID=a.EMPID)
) Loop

update cap.tSTOU_Employee set
Name = substrb(j.CName,1,10),
Code = j.EmpCode,
IDCardNo = Substrb(j.ID,1,18),
Sex = decode(j.Sex,'1','1','0'),
Birthday = j.Birthday,
EducDegree = j.DEGREE,
HireDay = j.EMPLOYDATE,
EnterIndustryTime = j.BusinessDate,
MobilePhone = j.MPhone,
OfficePhone=j.PHONE_C,
EName= j.EName,
Address = j.Address,
Zip = j.Zip,
IsEnabled = j.IsEnabled,
HasCanceled = j.HasCanceled,
tFlag = 0,
stFlag=0
Where EmployeeID=j.EMPID;
End Loop;

-- 如果在表中无记录，直接插入数据
INSERT INTO cap.tSTOU_Employee
(EmployeeID,DutyID,IsPrimary,Name,Code,LogonName,LogonFlag,Password,
IsEnabled,HasCanceled,IDCardNo,Sex,Birthday,
EducDegree,HireDay,EnterIndustryTime,MobilePhone,OfficePhone,
EName,Address,Zip,OrgID,NetCode)
Select a.EMPID,100,1,a.cname,a.EmpCode,a.EMPID,decode(a.status,1,1,0),'',
decode(a.status,'1',1,0) IsEnabled,
decode(a.status,'0',1,0) HasCanceled,
Substrb(a.ID,1,18),decode(a.Sex,'1','1','0'),a.Birthday,
(Select substrb(Name, 1, 50) From e3s.t_base_data @e3sifdb Where Code = a.Degree and Type = 26) Degree,
a.EMPLOYDATE,a.BusinessDate,a.MPhone,a.PHONE_C,
Substrb(a.ENAME,1,20) EName,Substrb(a.ADDR_H,1,50) Address,SubStrb(a.ZIPCODE,1,6) Zip,
a.OrgID,
(Select dealerno from e3s.t_vorg_organization @e3sifdb where orgid= a.OrgID) NetCode
FROM e3s.t_vorg_employ @e3sifdb a
WHERE to_char(a.EMPID) in (Select keyvalue from e3s.t_imp_e3sif2capif@e3sifdb where status='1' and TypeName=v_TypeName)
and not exists (select 'X' from cap.tSTOU_Employee Where EmployeeID=a.EMPID);

delete e3s.t_imp_e3sif2capif@e3sifdb where Status='1' And TypeName=v_TypeName;
end if;
------------------------------------------------------------------------------------
Commit ;

end P_test_SHE;

[mantisXF]

你 "For j in "中的select单独执行有数据吗？

ORA-06502: PL/SQL: 数字或值错误
======================================
这个好象是你的字段值不对或者是字段类型不一致 ...

[RaincyXP]

For 单独执行有数据哦
但是FOR 部分的 select和Insert 部分的select 都是一样的，为什么Insert执行没有问题，Update部分执行就有问题呢？这个问题困扰了很久呢。

[mantisXF]

能否desc cap.tSTOU_Employee?

可能是你的数据类型或字符类型长度不一样 ...

[RaincyXP]

是不是想要了解表结构？ 可是我逐个字段的Update运行检查发现，无论如何都出错。
EMPLOYEEID NUMBER not null,
NAME VARCHAR2(30),
ENAME VARCHAR2(40),
ABBREVIATION VARCHAR2(32),
CODE VARCHAR2(100),
ADDRESS VARCHAR2(100),
ZIP CHAR(6),
FAX VARCHAR2(48),
EMAIL VARCHAR2(400),
REMARK VARCHAR2(200),
ORDERNO NUMBER default 65536 not null,
ISENABLED NUMBER default 1 not null,
HASCANCELED NUMBER default 0 not null,
IDCARDNO CHAR(18),
LOGONNAME CHAR(40),
PASSWORD CHAR(50),
BIRTHDAY DATE,
NATION VARCHAR2(20),
HOUSEHOLDPHONE VARCHAR2(48),
MOBILEPHONE VARCHAR2(48),
OFFICEPHONE VARCHAR2(48),
EDUCDEGREE VARCHAR2(20),
HIREDAY DATE,
ENTERINDUSTRYTIME VARCHAR2(40),
SEX NUMBER,
LOGONFLAG NUMBER default 1 not null,
TFLAG NUMBER default 0 not null,
STFLAG NUMBER default 0 not null,
ORGID NUMBER not null,
NETCODE VARCHAR2(20),
SUBNETCODE VARCHAR2(20)

[mantisXF]

EDUCDEGREE VARCHAR2(20),

(Select substrb(Name, 1, 50)
From e3s.t_base_data @e3sifdb
Where Code = a.Degree
and Type = 26) Degree,

一个20，一个50，看是不是这里的问题？

=========================================


还有
"ENTERINDUSTRYTIME VARCHAR2(40),
EnterIndustryTime = j.BusinessDate,"
BusinessDate和ENTERINDUSTRYTIME 数据类型一致吗?

=================================================

还有"Sex = decode(j.Sex,'1','1','0'),"
这样decode出来好像是字符串 ...



应该就是这些了,试试看~~~

[RaincyXP]

可我是逐个字段的去Update测试都出现错哦，而且Insert操作无任何问题。

[bobfang]

e3s.t_vorg_employ @e3sifdb表的结构是怎样的

[RaincyXP]

EMPID NUMBER(10) not null,
ORGID NUMBER(10),
DEPTID NUMBER(20),
EMPCODE VARCHAR2(10),
CLASS VARCHAR2(5),
EMPLOYDATE DATE,
TUTORTYPE VARCHAR2(1),
BUSINESSDATE VARCHAR2(14),
STOCKPERCENTAGE VARCHAR2(3),
MOBILEALLOWNCE VARCHAR2(10),
EMPLOYRANK VARCHAR2(5),
MONTHALLOWNCE VARCHAR2(10),
MONTHBONUS VARCHAR2(10),
ZIPCODE VARCHAR2(20),
ID VARCHAR2(25),
CNAME VARCHAR2(20),
ENAME VARCHAR2(80),
DEGREE VARCHAR2(5),
ADDR_H VARCHAR2(200),
PHONE_C VARCHAR2(20),
MPHONE VARCHAR2(20),
SEX VARCHAR2(1),
BIRTHDAY DATE,
ECODE VARCHAR2(6),
STATUS CHAR(1)