Error: ORA-00980: synonym translation is no longer valid [message #676066] |
Thu, 09 May 2019 04:37 |
|
abs
Messages: 1 Registered: May 2019
|
Junior Member |
|
|
I had the same problem - the problem is Oracle does not recognize synonyms in an external database which in turn reference a table in another database. This is only an issue from within an Oracle Package or Procedure. But is not an issue when issuing the SQL command from SQL PLUS.
For example: DB1.Synomym points to DB2.synonym which in turn points to DB3.table ([select * from DB1.synonym] works as an SQL command but not in package/procedure).
Solution 1: Do what you said above - reference the DB2 synonym or the DB3.table directly i.e. [select * from DB2.synonym] or [select * from DB3.table]
Solution 2: create a view to select from DB2.synonym. Then use the view instead of the table in your package or procedure i.e. [SELECT * from DB1.view]
The only issue with Solution 2 is that it is read only.
|
|
|