开发公司根据业务的需要，在本地创建了大量的使用dblink方式去访问远程数据库的数据的SYNONYM（同义词），其实，本地业务用户通过dblink可以访问远程数据库的同义词，如：select * from a.table@dblink，这样是可以访问的，但是，不幸的是，将同义词放在PROCEDURE里就不行，编译不过去，报错PL/SQL:ORA-04063.
metalink给出了答案:
[@more@]
SELECT From a VIEW in a READ ONLY Database Using a DATABASE LINK Within A CURSOR LOOP Fails With ORA-06550 ORA-04063 PLS-00341 PLS-00364 [ID 358708.1]
Applies to: PL/SQL - Version 8.1.7.0 and later Information in this document applies to any platform. ***Checked for relevance on 23-Feb-2012***
Symptoms Using a DATABASE LINK to connect to a READ ONLY Database to SELECT data from a VIEW fails by Oracle 8iR3(8.1.7.x)
ORA-04052 error occurred when looking up remote object '
Oracle 9iR2(9.2.0.x) and higher
ORA-06550: line , column : ORA-04063: view '' has errors PLS-00341: declaration of cursor '' is incomplete or malformed PLS-00364: loop index variable '' use is invalid
The problem only occurs with dblink when SELECT from the VIEW using a cursor loop. Simple SELECT from the VIEW does not fail. SELECTing from a TABLE in CURSOR LOOP does not fail either.
The following code creates the view on the remote READ ONLY Database
create or replace view x_view as select count(*) y from x;
Executing on local database Oracle9iR2 (9.2.0.X or higher)
declare cursor c_user_objects is select y from x_view@ora92; r_user_objects c_user_objects%rowtype; begin for r_user_objects in c_user_objects loop dbms_output.put_line(r_user_objects.y);
end loop; end; /
returns following expected
Output
ERROR at line 3: ORA-06550: line 3, column 15: PL/SQL: ORA-04063: view "SCOTT.X_VIEW" has errors ORA-06550: line 3, column 1: PL/SQL: SQL Statement ignored ORA-06550: line 2, column 8: PLS-00341: declaration of cursor 'C_USER_OBJECTS' is incomplete or malformed ORA-06550: line 4, column 16: PL/SQL: Item ignored ORA-06550: line 8, column 29: PLS-00364: loop index variable 'R_USER_OBJECTS' use is invalid ORA-06550: line 8, column 8: PL/SQL: Statement ignored Cause This is expected behavior in all versions, since code need to be created on the remote READ ONLY Database, for
each time issued a new version need to be created, which cause the problem.
There is an Enhancement Request raised for this in Bug 2033385 SELECT FROM A VIEW IN A READ-ONLY DB USING A DBLINK WITHIN A CURSOR LOOP FAILS