dblink手动执行成功，存储过程中报错

环境oracle 9.2.0.8
HP UX 11i

创建dblink
create database link anlink connect to xxxx identified by xxxx using 'gghw';


创建存储过程

SQL> CREATE OR REPLACE PROCEDURE prc_an
  2  IS
  3  BEGIN
  4  EXECUTE IMMEDIATE ('truncate table an');
  5  insert into an select * from user_tables@anlink;
  6  commit;
  7  END prc_an;
  8  /

Warning: Procedure created with compilation errors.


报错信息

SQL> show error
Errors for PROCEDURE PRC_AN:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/1      PL/SQL: ORA-04052: error occurred when looking up remote object
         SYS.USER_TABLES@ANLINK
         ORA-00604: error occurred at recursive SQL level 1
         ORA-04029: error ORA-980 occurred when querying
         ORA_KGLR7_DEPENDENCIES
         ORA-00604: error occurred at recursive SQL level 1
         ORA-00980: synonym translation is no longer valid
         ORA-02063: preceding 3 lines from ANLINK

5/1      PL/SQL: SQL Statement ignored


手动执行成功
SQL> insert into an select * from user_tables@anlink;

127 rows created.

SQL> commit;

Commit complete.

 

解决方法：

SQL> create view v_an as select * from user_tables@anlink;

视图已创建。

SQL> CREATE OR REPLACE PROCEDURE prc_an
  2  IS
  3  BEGIN
  4  EXECUTE IMMEDIATE ('truncate table an');
  5  insert into an select * from v_an;
  6  commit;
  7  END prc_an;
  8  /

过程已创建。

SQL> exec prc_an;

PL/SQL 过程已成功完成。

SQL> select count(*) from an;

  COUNT(*)
----------
       127

SQL> truncate table an;

表被截断。

SQL> exec prc_an;

PL/SQL 过程已成功完成。

SQL> select count(*) from an;

  COUNT(*)
----------
       127

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13165828/viewspace-610009/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13165828/viewspace-610009/