PL/SQL:ORA-04063錯誤解決方法

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