ORA-00980 synonym translation no longer valid in PLSQL

oracle plsql synonym
D Veloper picture D Veloper · Aug 3, 2015 · Viewed 37.9k times · Source

I've got a synonym on a remote Oracle database that I can access in SQL over a database link, eg,

insert into my_table select * from my_synonym@my_database_link;

If I put the above statement into a PLSQL block, it won't compile, giving the error message "ORA-00980: synonym translation is no longer valid". The standard explanation is the table that the synonym points to has been dropped, etc, but this is not the case because the statement works in SQL.

Answer

a_horse_with_no_name picture a_horse_with_no_name · Aug 3, 2015

If something works in SQL but not in PL/SQL then in most cases this is a problem with privileges.

Any privilege that a user received through a role is not active when you enter a PL/SQL block. So most probably the SELECT privilege on the underlying table was granted through a role and thus is not "active" in the PL/SQL block.

The usual cure for this is to grant the privileges directly to the user, not through a role.

Related questions

When should database synonyms be used?

I've got the syntax down but I'm wondering if somebody can provide an illustrative use case where database synonyms are very useful.

Read More
SQL error "ORA-01722: invalid number"

A very easy one for someone, The following insert is giving me the ORA-01722: invalid number why? INSERT INTO CUSTOMER VALUES (1,'MALADY','Claire','27 Smith St Caulfield','0419 853 694'); INSERT INTO CUSTOMER VALUES (2,'GIBSON','Jake','27 Smith St Caulfield','0415 713 598…

Read More
Is there a combination of "LIKE" and "IN" in SQL?

In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question. Further, I often use conditions like WHERE something …

Read More