Use Synonyms for Remote Object Inside PL/SQL Block Raises ORA-00942 Table or View Does not Exist (Doc ID 1381325.1)

Last updated on JULY 17, 2017

Applies to:

PL/SQL - Version 10.2.0.4 to 10.2.0.5 [Release 10.2]
Information in this document applies to any platform.
10.2.0.x local database server involved (10.2.0.4 and 10.2.0.5 tested).
The issue did not reproduce in 11.1.0.7 nor 11.2.0.2.
***Checked for relevance on 30-Apr-2014***


Symptoms


Running a SQL statement where:
-the local user (Refresh)
-connects via the database link as user1 who has select access to the Scott table
-to call a remote object owned by Scott
which uses a local synonym created as Refresh to call the table owned by Scott in a remote server accessed via database link connected as User1 joined [with ANSI Joins] to local table errors if embedded in a PL/SQL block with the following:

ERROR at line 4:
ORA-06550: line 4, column 43:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 3, column 1:
PL/SQL: SQL Statement ignored


Stand-alone SQL outside of the PL/SQL block to run the same query succeeds.

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