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 (Doc ID 358708.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version 8.1.7.0 and later
Information in this document applies to any platform.

Symptoms

Using a DATABASE LINK to connect to a READ ONLY Database to SELECT data from a VIEW fails with:

Oracle 8iR3(8.1.7.x)

ORA-04052 error occurred when looking up remote object '<object name>'
ORA-00604 error occurred at recursive SQL level <level>
ORA-00372 file <file name> cannot be modified at this time
ORA-01110 data file <file name>: '<storage location>'
ORA-02063 preceding <line> from <dblink>


Oracle 9iR2(9.2.0.x) and higher 

ORA-06550: line <line>, column <column>:
ORA-04063: view '<view name>' has errors
PLS-00341: declaration of cursor '<cursor name>' is incomplete or malformed
PLS-00364: loop index variable '<variable name>' use is invalid

 


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




Sample 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

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms