ORA-00980 synonym translation no longer valid in PLSQL

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.

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.

Oracle / PLSQL: ORA-00980 Error Message, 4/3 PL/SQL: SQL Statement ignored 4/15 PL/SQL: ORA-00980: synonym translation is no longer valid cbcm_customer@ROAM> Am I doing� The ORA-00980 synonym translation is no longer valid for a materialized view can happen when a user ID is the the same on the created the public synonym for a table. See Oracle Note 38109.1 on MOSC. You need to test the MV signed on as the same user who created the materialized view. You can also try creating a private synonym.

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

  1. 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.

Ask TOM "ORA-00980: synonym translation is no longer valid", 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� In PLSQL : declare Cursor c1 is select * from banks@DB_MIBRIDGE.TEST.BACS.CO.UK where rownum < 3; begin For i in c1 loop Null; end loop; end; Cursor c1 is select * from * ERROR at line 2: ORA-06550: line 2, column 16: PL/SQL: ORA-00980: synonym translation is no longer valid ORA-06550: line 2, column 16: PL/SQL: SQL Statement ignored Thanks

Check in remote database grants for "my_synonym" must be almost "select" for the user you use in connect string, check also the object which this synonym points at (maybe someone deleted the table).

ORA-00980 synonym translation no longer valid in PLSQL, ORA-00980: synonym translation is no longer valid. 4723 views Less than a minute 0. While querying a view, got below error: SQL> select * from DBA_SYN; Error: ORA-00980: synonym translation is no longer valid. Cause. This can occur when public synonyms for these operators exist on the Oracle server, but the operators themselves do not exist. These operators were not implemented in version 9.2, but were implemented in version 9.3.

I found this issue when owner of the table/view/procedure are not match with owner mentioned in SYNONYM.

Example : If owner of table TABLE_BRACH is ownerA and in Synonym mentioned table owner is something else (Not ownerA).

Solution: 1. Drop the SYNONYM 2. Create that with same name with correct owner.

CREATE PUBLIC SYNONYM BRANCH FOR ownerA.TABLE_BRACH ;

ORA-00980: synonym translation is no longer valid DBACLASS, ORA-00980: synonym translation is no longer valid This error occurs when you have a public synonym defined and the object referencing or� The message is: "PL/SQL:ORA-00980: synonym translation is no longer valid." How can I rectify this problem? PL/SQL:ORA-00980: synonym translation is no longer

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;

ORA-00980: synonym translation is no longer valid, Error: ORA-00980: synonym translation is no longer valid. TIP: Another option is to use dyanmic SQL in the PL/SQL block as a work around. Hi; Please check below note which could be helpful for your issue: ORA-00980 - Synonym Translation is No Longer Valid [ID 202536.1] ORA-00980 when PL/SQL Block uses Synonym to Synonym to Table over Dblinks [ID 453754.1] Regard Helios

Error: ORA-00980: synonym translation is no longer valid, ORA-00980: synonym translation is no longer valid. 14 views July 25, 2020. 0. Avatar statement into a PL/SQL stored procedure on the s ame server, ORA-00980: synonym translation is no longer valid Cause: The synonym used is based on a table, view, or synonym that no longer exists. Action: Replace the synonym with the name of the object it references or re-create the synonym so that it refers to a valid table, view, or synonym.

ORA-00980: synonym translation is no longer valid, Error: ORA-00980: synonym translation is no longer valid. Error Message. When using any of the following Spatial Type for Oracle (ESRI� Learn the cause and how to resolve the ORA-00980 error message in Oracle. You tried to reference a synonym that no longer exists.

ORA-00980: synonym translation is no longer valid, synonym translation is no longer valid. Cause: The synonym used is based on a table, view, or synonym that no longer exists. Action: Replace the synonym with� ORA-00980: synonym translation is no longer valid when creating VIEW object it is referring a synonym that is no longer valid. synonym translation no longer

Comments
  • Ask Tom has an entry on this topic with scenarios similar to yours here, asktom.oracle.com/pls/apex/…
  • Good addition to the answers below (from Burleson Consulting) : dba-oracle.com/…
  • Thank you for answering my question, but in this particular case no grants are involved: my_synonym is accessed via the database link my_database_link.
  • @DVeloper: ... and the user that is stored in the DBLink needs grants to access the underlying table.
  • I think it is good/fine to cite the Oracle documentation, yet you should not copy its contents verbatim. Provide your own example. :)
  • Good addition to this answer (from Burleson Consulting) : dba-oracle.com/…