Use Synonyms for Remote Object Inside PL/SQL Block Raises ORA-00942 Table or View Does not Exist
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 18.104.22.168 nor 22.214.171.124.
***Checked for relevance on 30-Apr-2014***
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:
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.
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