Home » SQL & PL/SQL » SQL & PL/SQL » Error: ORA-00980: synonym translation is no longer valid
Error: ORA-00980: synonym translation is no longer valid [message #676066] Thu, 09 May 2019 04:37 Go to next message
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.
Re: Error: ORA-00980: synonym translation is no longer valid [message #676067 is a reply to message #676066] Thu, 09 May 2019 06:22 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

ALWAYS post Oracle version to 4 decimal places.

Please do NOT tell us about what you think you do or see; SHOW us using COPY & PASTE
Re: Error: ORA-00980: synonym translation is no longer valid [message #676069 is a reply to message #676067] Thu, 09 May 2019 08:17 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why was this post split from the thread it was attached to?
Re: Error: ORA-00980: synonym translation is no longer valid [message #676106 is a reply to message #676069] Tue, 14 May 2019 15:51 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
This is a know problem with all versions of the oracle database. Oracles own suggestion is the following

TIP: Another option is to use dyanmic SQL in the PL/SQL block as a work around. When using dynamic SQL the database link is not resolved at compile time but at runtime.

As I test I used synonyms starting in an oracle 18 database, jumping to a synonym in an oracle 10g database pointing to a table in an oracle 8i database. If I use the following code I get the error

declare
cnt number;
begin
select count(*)  into cnt from itm where ve_cd = 'SELY';
dbms_output.put_line(cnt);
end;
/

If I do the following it works everytime

declare
cnt number;
begin
execute immediate 'select count(*) from itm where ve_cd = ''SELY''' into cnt;
dbms_output.put_line(cnt);
end;
/
Re: Error: ORA-00980: synonym translation is no longer valid [message #676113 is a reply to message #676106] Wed, 15 May 2019 03:38 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
To follow up on my above post - this was originally a reply to a really old post. It's not a question.
Since it's been split off from the original I can't find the original and it no longer really makes sense in (lack of) context.
icon1.gif  Re: Error: ORA-00980: synonym translation is no longer valid [message #676182 is a reply to message #676066] Mon, 20 May 2019 01:09 Go to previous message
sandycullen
Messages: 2
Registered: May 2019
Junior Member
You can fix this error by checking the following details
When you have drop the user, ensure that the synonyms referencing to the objects of that user are also dropped
Ensure the synonym created has the object, else it will show the 'ora-00980 synonym translation is no longer valid'
Or Re-create the synonym to refer it to a valid table, view or synonym
Ensure that the object has a reference to synonym created.
These few tips might help to fix 'ora-00980 synonym translation is no longer valid' error.
You can also try these techniques toresolve Oracle error
Previous Topic: Disable FND_FILE.PUT_LINE(FND_FILE.LOG
Next Topic: Loop after fetch on generic SYS_REFCURSOR
Goto Forum:
  


Current Time: Fri Mar 29 10:13:05 CDT 2024