ORA-00980 synonym translation no longer valid in PLSQL

Question

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 1

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.