OraFAQ Forum: SQL & PL/SQL » Error: ORA-00980: synonym translation is no longer valid

Home » SQL & PL/SQL » SQL & PL/SQL » Error: ORA-00980: synonym translation is no longer valid

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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.

Report message to a moderator

Re: Error: ORA-00980: synonym translation is no longer valid [message #676067 is a reply to message #676066]

Thu, 09 May 2019 06:22

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

Report message to a moderator

Re: Error: ORA-00980: synonym translation is no longer valid [message #676069 is a reply to message #676067]

Thu, 09 May 2019 08:17

cookiemonster
Messages: 13972
Registered: September 2008
Location: Rainy Manchester

Senior Member

Why was this post split from the thread it was attached to?

Report message to a moderator

Re: Error: ORA-00980: synonym translation is no longer valid [message #676106 is a reply to message #676069]

Tue, 14 May 2019 15:51

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;
/

Report message to a moderator

Re: Error: ORA-00980: synonym translation is no longer valid [message #676113 is a reply to message #676106]

Wed, 15 May 2019 03:38

cookiemonster
Messages: 13972
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.

Report message to a moderator

Re: Error: ORA-00980: synonym translation is no longer valid [message #676182 is a reply to message #676066]

Mon, 20 May 2019 01:09

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

Report message to a moderator

Previous Topic:	Disable FND_FILE.PUT_LINE(FND_FILE.LOG
Next Topic:	Loop after fetch on generic SYS_REFCURSOR

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Sep 09 01:30:42 CDT 2025