ORA-00980 synonym translation no longer valid in PLSQL

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.

Thank you to everyone who tried to help. This turned out to be an Oracle limitation:

https://support.oracle.com/rs?type=doc&id=453754.1

APPLIES TO:

PL/SQL - Version 9.2.0.8 and later Information in this document applies to any platform. Checked for relevance on 01-Apr-2015

SYMPTOMS

A PL/SQL block fails with error: ORA-00980: synonym translation is no longer valid, when selecting data from a remote database. The following code demonstrates this issue:

On DB3 (create the table)

CONNECT u3/u3 DROP TABLE tab; CREATE TABLE tab(c1 number); INSERT INTO tab VALUES (1); COMMIT;

On DB2 (create a synonym to the table on DB3)

CONNECT u2/u2 DROP DATABASE LINK dblink2; CREATE DATABASE LINK dblink2 CONNECT TO u3 IDENTIFIED BY u3 USING 'EMT102U6'; SELECT * FROM global_name@dblink2; DROP SYNONYM syn2; CREATE SYNONYM syn2 FOR tab@dblink2; SELECT * FROM syn2;

On DB1 (create a synonym to the synonym on DB2)

CONNECT u1/u1 DROP DATABASE LINK dblink1; CREATE DATABASE LINK dblink1 CONNECT TO u2 IDENTIFIED BY u2 USING 'EMT102W6'; SELECT * FROM global_name@dblink1; DROP SYNONYM syn1; CREATE SYNONYM syn1 FOR syn2@dblink1; SELECT c1 from syn1;

This works in SQL but fails when called from PL/SQL

DECLARE num NUMBER; BEGIN SELECT c1 INTO num FROM syn1; END; /

ERROR at line 4: ORA-06550: line 4, column 3: PL/SQL: ORA-00980: synonym translation is no longer valid ORA-06550: line 4, column 3: PL/SQL: SQL Statement ignored

CAUSE

This issue was reported in Bug 2829591 QUERING FROM A PL/SQL PROCEDURE IN 9I -> 8I-> 7.3.4, GETTING ORA-980. This bug was closed as 'NOT A BUG' for the following reasons

PL/SQL cannot instruct middle database (DB2) to follow the database link during the compilation phase. Therefore in order for this PL/SQL block to compile and run, both database links dblink1 and dblink2 should be defined on the front end database - DB1. During runtime database link dblink2 will be looked up in DB2 as expected.

SOLUTION

To implement the solution, please execute the following steps:

1. Create a database link dblink2 on DB1 pointing to DB3

SQL> create database link dblink2 connect to u3 identified by u3 using 'EMT102U6';

2. Create and compile the PL/SQL block on DB1.

CREATE DATABASE LINK dblink2 CONNECT TO u3 IDENTIFIED BY u3 USING 'EMT102U6';

SELECT * FROM global_name@dblink2; DECLARE num NUMBER; BEGIN
SELECT c1 INTO num FROM syn1; END; / PL/SQL procedure successfully completed.

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.

Workaround solution is to use an Oracle view instead.

CREATE VIEW v_my_synomym as (select * from my_synonym@my_database_link);

Then reference the view in your package or procedure i.e.:

insert into my_table select * from v_my_synonym;