Accessing TABLE From READ ONLY DATABASE Using DATABASE LINK Within PL/SQL Fails With ORA-06550 ORA-04063 or PLS-00905 (Doc ID 358697.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.
***Checked for relevance on 27th Jun 2016***

Symptoms

Accessing TABLE From READ ONLY (STANDBY) DATABASE Using DATABASE LINK Within PL/SQL Fails With ORA-06550 ORA-04063 or PLS-00905

To reproduce in remote read only Database:

(startup)
drop table x;
create table x ( c1 number);
(shutdown)
(startup open read only)


In local database:

drop database link ora102;
create database link ora102 using 'ora102';

declare
    i number;
    begin
    select count(*) into i from x@ora102;
end;
/

If local and remote database have version between Oracle9i 9.2.0 to Oracle 11.2 or later it fails with:

SQL> declare
  2      i number;
  3      begin
  4      select count(*) into i from x@ora102;
  5  end;
  6  /
    select count(*) into i from x@ora102;
                                *
ERROR at line 4:
ORA-06550: line 4, column 33:
PL/SQL: ORA-04063: table "SCOTT.X" has errors
ORA-06550: line 4, column 5:
PL/SQL: SQL Statement ignored

If local database have versions between Oracle8 8.0.6 to Oracle8i 8.1.7 it fails with :

SVRMGR> declare
     2>     i number;
     3>     begin
     4>     select count(*) into i from x@ora817;
     5> end;
     6> /
    select count(*) into i from x@ora817;
                               
                                *
ORA-06550: line 4, column 33:
PLS-00905: object SCOTT.X@ORA817.WORLD is invalid
ORA-06550: line 4, column 5:
PL/SQL: SQL Statement ignored

If Local Database has a version higher than Oracle9i 9.2.0 and remote Database a version lower than Oracle9i 9.0.1 then it could fails with ORA-00600 [17069]

Work OK When Accessing TABLE Using SQL From READ ONLY (STANDBY) DATABASE Using DATABASE LINK

SVRMGR> select count(*) from x@ora817;
COUNT(*)
----------
         0
1 row selected.

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