高分求救！急。。。。。。！在线等待

[txchen]

本人在调试ORACLE8.16存储过程时 ， 发生如下error , 不知哪位曾遇到过此类error 提示，或是有何见解，有答必有分，不够可再加!
在线等待

ORA-01008: 并非所有变量都已关联
ORA-02063: 紧接着line（源于CO_SERVER）
ORA-06512: 在"TXCHEN.PRO_WZ_SJCS_JCSJXZ", line 236

[LGQDUCKY]

代码，大家分析一下。。。。

[boy002]

用了没有定义的变量

[boy002]

一般这个问题出在PL/SQL中Procedure中很少见

[hdkkk]

形参、实参个数不一致吧？

[supkim]

sql内的返回结果集比存储变量多吧

还是它贴出来吧

[甜而不腻]

存储过程错误，你把存储过程贴出来。

[l2g32003]

是不是给存储过程传递的参数个数不够

看样子你的存储过程代码不短吧

[gateschina]

包头呢？？？？？？？？？？？？？？？
怎么不贴出来？

[txchen]

这是个存储过程PRO_WZ_SJCS_JCSJXZ

[vrv0129]

up

[txchen]

问题主要出在
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的字段.

[beckhambobo]

select sysdate() into dqrq from dual@co_server;
这一句上,@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;这句也是多余的

[txchen]

-- 基础数据下载
(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;

[txchen]

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_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;

[txchen]

代码分成了两段.
以下是错误提示
ORA-01008: 并非所有变量都已关联
ORA-02063: 紧接着line（源于CO_SERVER）
ORA-06512: 在"TXCHEN.PRO_WZ_SJCS_JCSJXZ", line 307
ORA-06512: 在line 1

[txchen]

@co_server是一个数据库链路,用于连接远程服务器上的数据库.
而select sysdate() into dqrq from dual@co_server;是用于取远程服务器的的系统时间.
这名话没有什么问题.

如果我不写raise;在前台用PB调用存储过程时不会弹出错误信息.

[txchen]

请高手指点,是我的数据库设置有问题还是代码有问题？