本人在调试ORACLE8.16存储过程时 , 发生如下error , 不知哪位曾遇到过此类error 提示,或是有何见解,有答必有分,不够可再加!
在线等待ORA-01008: 并非所有变量都已关联
ORA-02063: 紧接着line(源于CO_SERVER)
ORA-06512: 在"TXCHEN.PRO_WZ_SJCS_JCSJXZ", line 236
在线等待ORA-01008: 并非所有变量都已关联
ORA-02063: 紧接着line(源于CO_SERVER)
ORA-06512: 在"TXCHEN.PRO_WZ_SJCS_JCSJXZ", line 236
(sjzl varchar2,flag out number)
--sjzl数据种类,也就是传输记录表中的传输内容;flag 传输成功标志 ,1成功
IS
rowcount number;
lrd number;
sccsrq date;
dqrq date;
str varchar2(1000);
begin
lrd:=1;
select sysdate() into dqrq from dual@co_server;
if sjzl = '车辆基础信息' then
select count(*) into rowcount from pub_cs_csjl@co_server where id_lrd=lrd and CSNR=sjzl;
if rowcount=0 then --如果没有发现历史传输记录
str:='
truncate table cl_jbxx';
execute immediate str;
insert into cl_jbxx
select * from cl_jbxx@co_server
where lrrq<=dqrq;
str:='
truncate table cl_fjxx';
execute immediate str; insert into cl_fjxx
select * from cl_fjxx@co_server
where lrrq<=dqrq;
insert into pub_cs_csjl@co_server
values(lrd,sjzl,dqrq);
else
select zjcsrq into sccsrq from pub_cs_csjl@co_server where id_lrd=lrd and CSNR=sjzl;
if sccsrq is null or sccsrq=to_date('1901-01-01') or sccsrq>dqrq then--有记录但最近传输日期丢失
flag:= -1;
return;
else
delete from cl_jbxx
where id in ( select id
from cl_jbxx@co_server
where lrrq>sccsrq and lrrq<=dqrq);
insert into cl_jbxx
select * from cl_jbxx@co_server
where lrrq>sccsrq and lrrq<=dqrq;
delete from cl_fjxx
where id_jbxx in ( select id_jbxx
from cl_fjxx@co_server
where lrrq>sccsrq and lrrq<=dqrq);
insert into cl_fjxx
select * from cl_fjxx@co_server
where lrrq>sccsrq and lrrq<=dqrq; update pub_cs_csjl@co_server
set zjcsrq=dqrq
where id_lrd=lrd and csnr=sjzl;
end if;
end if;
elsif sjzl = '车辆异动信息' then
select count(*) into rowcount from pub_cs_csjl@co_server where id_lrd=lrd and CSNR=sjzl;
if rowcount=0 then --如果没有发现历史传输记录
str:='
truncate table cl_bmyd';
execute immediate str;
insert into cl_bmyd
select * from cl_bmyd@co_server
where lrrq<=dqrq;
str:='
truncate table cl_lxyd';
execute immediate str;
insert into cl_lxyd
select * from cl_lxyd@co_server
where lrrq<=dqrq;
str:='
truncate table cl_qtyd';
execute immediate str;
insert into cl_qtyd
select * from cl_qtyd@co_server
where lrrq<=dqrq;
insert into pub_cs_csjl@co_server
values(lrd,sjzl,dqrq);
else
select zjcsrq into sccsrq from pub_cs_csjl@co_server where id_lrd=lrd and CSNR=sjzl;
if sccsrq is null or sccsrq=to_date('1901-01-01') or sccsrq>dqrq then--有记录但最近传输日期丢失
flag:= -1;
return;
else
delete from cl_bmyd
where id in ( select id
from cl_bmyd@co_server
where lrrq>sccsrq and lrrq<=dqrq);
insert into cl_bmyd
select * from cl_bmyd@co_server
where lrrq>sccsrq and lrrq<=dqrq;
delete from cl_lxyd
where id in ( select id
from cl_lxyd@co_server
where lrrq>sccsrq and lrrq<=dqrq);
insert into cl_lxyd
select * from cl_lxyd@co_server
where lrrq>sccsrq and lrrq<=dqrq;
delete from cl_qtyd
where id in ( select id
from cl_qtyd@co_server
where lrrq>sccsrq and lrrq<=dqrq);
insert into cl_qtyd
select * from cl_qtyd@co_server
where lrrq>sccsrq and lrrq<=dqrq;
update pub_cs_csjl@co_server
set zjcsrq=dqrq
where id_lrd=lrd and csnr=sjzl;
end if;
end if;
elsif sjzl = '车辆类型信息' then
str:='
truncate table cl_lx';
execute immediate str;
insert into cl_lx
select * from cl_lx@co_server;
elsif sjzl = '人事部门人员信息' then
select count(*) into rowcount from pub_cs_csjl@co_server where id_lrd=lrd and CSNR=sjzl;
if rowcount=0 then --如果没有发现历史传输记录
str:='
truncate table rs_bmb';
execute immediate str; insert into rs_bmb
select * from rs_bmb@co_server
where lrrq<=dqrq;
str:='
truncate table rs_ryxx';
execute immediate str; insert into rs_ryxx
select * from rs_ryxx@co_server
where lrrq<=dqrq;
insert into pub_cs_csjl@co_server
values(lrd,sjzl,dqrq);
else
select zjcsrq into sccsrq from pub_cs_csjl@co_server where id_lrd=lrd and CSNR=sjzl;
if sccsrq is null or sccsrq=to_date('1901-01-01') or sccsrq>dqrq then--有记录但最近传输日期丢失
flag:= -1;
return;
else
delete from rs_bmb
where id in ( select id
from rs_bmb@co_server
where lrrq>sccsrq and lrrq<=dqrq);
insert into rs_bmb
select * from rs_bmb@co_server
where lrrq>sccsrq and lrrq<=dqrq;
delete from rs_ryxx
where id in ( select id
from rs_ryxx@co_server
where lrrq>sccsrq and lrrq<=dqrq);
insert into rs_ryxx
select * from rs_ryxx@co_server
where lrrq>sccsrq and lrrq<=dqrq;
update pub_cs_csjl@co_server
set zjcsrq=dqrq
where id_lrd=lrd and csnr=sjzl;
end if;
end if;
select count(*) into rowcount from pub_cs_csjl@co_server where id_lrd=lrd and CSNR=sjzl;
if rowcount=0 then --如果没有发现历史传输记录
str:='
truncate table rs_ygydqk';
execute immediate str;
insert into rs_ygydqk
select * from rs_ygydqk@co_server
where lrrq<=dqrq;
insert into pub_cs_csjl@co_server
values(lrd,sjzl,dqrq);
else
select zjcsrq into sccsrq from pub_cs_csjl@co_server where id_lrd=lrd and CSNR=sjzl;
if sccsrq is null or sccsrq=to_date('1901-01-01') or sccsrq>dqrq then--有记录但最近传输日期丢失
flag:= -1;
return;
else
delete from rs_ygydqk
where id in ( select id
from rs_ygydqk@co_server
where lrrq>sccsrq and lrrq<=dqrq);
insert into rs_ygydqk
select * from rs_ygydqk@co_server
where lrrq>sccsrq and lrrq<=dqrq;
update pub_cs_csjl@co_server
set zjcsrq=dqrq
where id_lrd=lrd and csnr=sjzl;
end if;
end if;
elsif sjzl = '人事基础项目' then
--全删除后重写
str:='
truncate table rs_xmsz';
execute immediate str;
str:='
truncate table rs_xmlx';
execute immediate str;
insert into rs_xmlx
select * from rs_xmlx@co_server;
insert into rs_xmsz
select * from rs_xmsz@co_server;
elsif sjzl = '人事工资信息' then
select count(*) into rowcount from pub_cs_csjl@co_server where id_lrd=lrd and CSNR=sjzl;
if rowcount=0 then --如果没有发现历史传输记录
str:='
truncate table rs_gzff';
execute immediate str;
insert into rs_gzff
select * from rs_gzff@co_server
where lrrq<=dqrq;
str:='
truncate table rs_gzmx';
execute immediate str; insert into rs_gzmx(id,id_gzff,id_xmsz_gzxm,gzz,gzny)
select rs_gzmx.id,rs_gzmx.id_gzff,rs_gzmx.id_xmsz_gzxm,
rs_gzmx.gzz,rs_gzmx.gzny
from rs_gzmx@co_server,rs_gzff@co_server
where rs_gzmx.id_gzff=rs_gzff.id and
rs_gzff.lrrq<=dqrq;
str:='
truncate table rs_bxj';
execute immediate str;
insert into rs_bxj(id,id_ryxx,id_xmsz_bxjlx,bxjz,ny)
select rs_bxj.id,rs_bxj.id_ryxx,rs_bxj.id_xmsz_bxjlx,rs_bxj.bxjz,rs_bxj.ny
from rs_bxj@co_server,rs_gzff@co_server
where rs_bxj.id_ryxx=rs_gzff.id_ryxx and
rs_bxj.ny=rs_gzff.gzny and
rs_gzff.lrrq<=dqrq;
insert into pub_cs_csjl@co_server
values(lrd,sjzl,dqrq);
else
select zjcsrq into sccsrq from pub_cs_csjl@co_server where id_lrd=lrd and CSNR=sjzl;
if sccsrq is null or sccsrq=to_date('1901-01-01') or sccsrq>dqrq then--有记录但最近传输日期丢失
flag:= -1;
return;
else
delete from rs_gzff
where id in ( select id
from rs_gzff@co_server
where lrrq>sccsrq and lrrq<=dqrq);
insert into rs_gzff
select * from rs_gzff@co_server
where lrrq>sccsrq and lrrq<=dqrq;
delete from rs_gzmx
where id_gzff in ( select id
from rs_gzff@co_server
where lrrq>sccsrq and lrrq<=dqrq);
insert into rs_gzmx(id,id_gzff,id_xmsz_gzxm,gzz,gzny)
select rs_gzmx.id,rs_gzmx.id_gzff,rs_gzmx.id_xmsz_gzxm,
rs_gzmx.gzz,rs_gzmx.gzny
from rs_gzmx@co_server,rs_gzff@co_server
where rs_gzmx.id_gzff=rs_gzff.id and
lrrq>sccsrq and lrrq<=dqrq;
delete from rs_bxj
where to_char(id_ryxx)||to_char(ny,'yyyy-mm-dd') in (
select to_char(id_ryxx)||to_char(gzny,'yyyy-mm-dd')
from rs_gzff@co_server
where lrrq>sccsrq and lrrq<=dqrq);
insert into rs_bxj(id,id_ryxx,id_xmsz_bxjlx,bxjz,ny)
select rs_bxj.id,rs_bxj.id_ryxx,rs_bxj.id_xmsz_bxjlx,rs_bxj.bxjz,rs_bxj.ny
from rs_bxj@co_server,rs_gzff@co_server
where rs_bxj.id_ryxx=rs_gzff.id_ryxx and
rs_bxj.ny=rs_gzff.gzny and
lrrq>sccsrq and lrrq<=dqrq;
update pub_cs_csjl@co_server
set zjcsrq=dqrq
where id_lrd=lrd and csnr=sjzl;
end if;
end if;
end if ;
flag:=1;
commit;
return;
EXCEPTION
when others then
rollback;
raise;
flag:= 0;
return;
end;
以下是错误提示
ORA-01008: 并非所有变量都已关联
ORA-02063: 紧接着line(源于CO_SERVER)
ORA-06512: 在"TXCHEN.PRO_WZ_SJCS_JCSJXZ", line 307
ORA-06512: 在line 1
delete from rs_gzmx
where id_gzff in ( select id
from rs_gzff@co_server
where lrrq>sccsrq and lrrq<=dqrq);
insert into rs_gzmx(id,id_gzff,id_xmsz_gzxm,gzz,gzny)
select rs_gzmx.id,rs_gzmx.id_gzff,rs_gzmx.id_xmsz_gzxm,
rs_gzmx.gzz,rs_gzmx.gzny
from rs_gzmx@co_server,rs_gzff@co_server
where rs_gzmx.id_gzff=rs_gzff.id and
lrrq>sccsrq and lrrq<=dqrq;
当把变量dqrq换成sysdate后就不会出错.
表里没有叫dqrq的字段.
这一句上,@co_server这句是什么意思?
修改为:
dqrq:=sysdate;
--------------------------------
str:='
truncate table cl_jbxx';
execute immediate str;
这句要用grnat drop any table to 当前用户; 的权限------------------------------
return;
EXCEPTION
when others then
rollback;
raise;
flag:= 0;
return; 这里出现了两个return语句,其实不用的,oracle自动会退出过程,不用你人工干预,raise;这句也是多余的
而select sysdate() into dqrq from dual@co_server;是用于取远程服务器的的系统时间.
这名话没有什么问题.如果我不写raise;在前台用PB调用存储过程时不会弹出错误信息.