oracle10g存储过程错误 [Oracle 开发]

greenandtree

存储过程如下：
create or replace procedure proc_stat_alldata(protype varchar2 default null) as
  v_pro dbms_sql.varchar2_table;
  v_source dbms_sql.varchar2_table;
  v_btab dbms_sql.varchar2_table;
  v_etab dbms_sql.varchar2_table;
  v_wsql dbms_sql.varchar2_table;
  v_dblink dbms_sql.varchar2_table;
  v_tablist dbms_sql.varchar2_table;
  v_cnt int;
  v_total int;
  sqlstr long;
  tmpsql long;

begin
  v_total:=0;
  v_cnt:=0;
  if protype is null then
  tmpsql := ' where flag =1 ';
  else
  tmpsql := ' where flag =1 and protypedesc =''' || protype || '''';
  end if;
  sqlstr := ' select protypedesc,datasource,btable,etable,wherestr,dblink from base_staticonftable@tasklink ' ||
  tmpsql;
  execute immediate sqlstr BULK COLLECT
  INTO v_pro, v_source, v_btab, v_etab, v_wsql, v_dblink;

  for i in 1 .. v_pro.COUNT loop
  sqlstr := 'select table_name from user_tables@' || v_dblink(i) ||
  ' where table_name >=''' || v_btab(i) ||
  ''' and table_name <=''' || v_etab(i) ||
  ''' and table_name not like ''%_BAK''';
  execute immediate sqlstr BULK COLLECT
  INTO v_tablist;
  delete from ProCountTABLE@tasklink
  where proTypeDesc = v_pro(i)
  and datasource = v_source(i)
  and datadate = to_number(to_char(sysdate - 1, 'yyyymmdd'));
  if v_tablist.COUNT >= 1 then
  for j in 1 .. v_tablist.COUNT loop
  sqlstr := 'select count(*) from ' || v_tablist(j) || '@' ||
  v_dblink(i) || ' ' || v_wsql(i);
  dbms_output.put_line(sqlstr);
  execute immediate sqlstr
  into v_cnt;
  v_total := v_total + v_cnt;
  end loop;
  insert into ProCountTABLE@tasklink
  (proTypeDesc, Datasource, dataDate, totalCnt, insertDate)
  values
  (v_pro(i),
  v_source(i),
  to_number(to_char(sysdate - 1, 'yyyymmdd')),
  v_total,
  sysdate);
  end if;
  commit;
  end loop;
end proc_stat_alldata;


报错内容如下：

PROCEDURE FENGHUO.PROC_STAT_ALLDATA 编译错误

错误：ORA-04052: error occurred when looking up remote object FENGHUO.PROCOUNTTABLE@TASKLINK
  ORA-00604: error occurred at recursive SQL level 1
  ORA-12560: TNS:protocol adapter error
行：1
文本：create or replace procedure proc_stat_alldata(protype varchar2 default null) as

请问这是怎么回事啊？？？？？

tanyangxf

回复 1# greenandtree


select protypedesc,datasource,btable,etable,wherestr,dblink from base_staticonftable@tasklink 看看这句话能不能执行？dblink有问题

duolanshizhe

你的存储过程涉及到一个远程对象

首先要保证这个dblink是可用的，其次保证对象存储及权限正常！

手工执行这个语句，看看是什么结果？

select protypedesc,datasource,btable,etable,wherestr,dblink
from base_staticonftable@tasklink

renxiao2003

dblink所在主机监听，或者服务没有启动。