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 MARCH 25, 2025
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.
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
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
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
| Symptoms |
| Cause |
| Solution |