My Oracle Support Banner

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 FEBRUARY 01, 2022

Applies to:

PL/SQL - Version to [Release 10.2]
Information in this document applies to any platform.
10.2.0.x local database server involved ( and tested).
The issue did not reproduce in nor


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.


To view full details, sign in with your My Oracle Support account.

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

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.