我在测试pCmsPersonInforAdd时出现了"ORA-22994: 源偏移量超出源 LOB 的结尾"，我的操作如下：
步骤1：
CREATE SEQUENCE SEQ_PersonInfor
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
nocache;
步骤2：
create table tCmsPersonInfor(
dIntgPersonnelID INTEGER
not null,
dVch2PersonNameVARCHAR2(20),
dVch2Duty
VARCHAR2(20),
dVch2IdentifyVARCHAR2(20),
dVch2ContectPhoneVARCHAR2(20),
dBlobDrivLicence BLOB,
dBlobIdentifyLicence BLOB,
dBlobFactoryLicenceBLOB,
dVch2Remark
VARCHAR2(200),
constraint PK_TCMSPERSONINFOR primary key (dIntgPersonnelID)
);
步骤3：
create or replace procedure pCmsPersonInforAdd
(
viVch2PersonName VARCHAR2,--人员姓名
viVch2Duty VARCHAR2,--职务
viVch2Identify VARCHAR2(20),--身份证号
viVch2ContectPhone VARCHAR2(20),--联系电话
viVch2DrivLicence VARCHAR2(20),--驾照扫描件名称
viVch2IdentifyLicence VARCHAR2(20),--身份证扫描件名称
viVch2FactoryLicence VARCHAR2(20),--出场证扫描件名称
viVch2Remark VARCHAR2(20),--备注
voIntReturns OUT INT
)
as
mNumCount number;
mBflFileloc bfile;
mIntMount int;
mIntDestOffset int := 1;
mIntSrcOffset int := 1;
type emp_varchar2_array is table of varchar2(20) index by binary_integer;
mScanPictureName emp_ssn_array;
type emp_blob_array is table of blob index by binary_integer;
mScanPictureBlob emp_blob_array;
begin
mScanPictureName(1) := viVch2DrivLicence;
mScanPictureName(2) := viVch2IdentifyLicence;
mScanPictureName(3) := viVch2FactoryLicence;
insert into tCmsPersonInfor (dIntgPersonnelID,dVch2PersonName,dVch2Duty,dVch2Identify,dVch2ContectPhone,dBlobDrivLicence,dBlobIdentifyLicence,dBlobFactoryLicence,dVch2Remark)
values (SEQ_PersonInfor.nextval,viVch2PersonName,viVch2Duty,viVch2Identify,viVch2ContectPhone,empty_blob(),empty_blob(),empty_blob(),viVch2Remark);
select max(dIntgPersonnelID) into mNumCount from tCmsPersonInfor;
select dBlobDrivLicence,dBlobIdentifyLicence,dBlobFactoryLicence into mScanPictureBlob(1),mScanPictureBlob(2),mScanPictureBlob(3) from tCmsPersonInfor where dIntgPersonnelID = mNumCount for update;
for i in 1..mScanPictureName.count loop
mBflFileloc := bfilename('UPFILE_DIR',mScanPictureName(i));
dbms_lob.fileopen(mBflFileloc,0);
mIntMount := dbms_lob.getlength(mBflFileloc);
dbms_lob.loadblobfromfile(mScanPictureBlob(i),mBflFileloc,mIntMount,mIntDestOffset,mIntSrcOffset);
dbms_lob.fileclose(mBflFileloc);
end loop;
voIntReturns := 1;
commit;
exception
when others then
voIntReturns := -1;
rollback;
end;
/
进行测试入下图：
1.gif (790.55 KB, 下载次数: 7)
下载附件
2007-12-27 18:42 上传
我该如何处理?谢谢指点
|
