Ошибка ora 00980

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.

We usually use synonyms to point to a base object that we don’t own it. Let’s see a case.

SQL> conn oe/oe@orclpdb1
Connected.
SQL> select * from countries;
select * from countries
              *
ERROR at line 1:
ORA-00980: synonym translation is no longer valid

In this case, when we tried to access the base object, we got an error. ORA-00980 means that the synonym that you’re trying to use is invalid, furthermore, the base object is not existing.

Let’s see what base object that the synonym points to.

SQL> column table_owner format a15;
SQL> column table_name format a15;
SQL> select table_owner, table_name from all_synonyms where owner = 'OE' and synonym_name = 'COUNTRIES';

If the base object is a table, we can check whether the table is existing or not.

SQL> select t.owner, t.table_name from all_synonyms s, all_tables t where s.owner = 'OE' and s.synonym_name = 'COUNTRIES' and s.table_owner = t.owner and s.table_name = t.table_name;

Apparently, it’s no longer existing. Next, we can check all tables of HR that we can access.

SQL> select table_name from all_tables where owner = 'HR' order by 1;

In this case, the table has been renamed to COUNTRIES_BAK. In most cases, the base object don’t exist, which caused the synonym no longer valid.

Solutions

Here I provide 4 ways to solve ORA-00980.

Restore the Base Object

If you need the base object very much, you should ask for the owner or DBA to restore it. After that, the synonym will go valid again.

Replace Synonym Translation

You can create or replace the synonym with the same name to point to the new table.

SQL> create or replace synonym countries for hr.countries_bak;

The good thing is that, you don’t have to change our PL/SQL or application codes because you just replaced the translation underneath.

Recreate a New Synonym

To align with the new name of the base object, you can drop it then create a new synonym.

SQL> drop synonym countries;

That is to say, we use the new synonym instead of the old synonym from now on.

Drop the Invalid Synonym

If the base object is no way to restore and the above solutions is not applicable to your case, then the synonym is useless, you can drop it anytime.

While querying a view, got below error:

SQL> select * from DBA_SYN;
select * from DBA_SYN
*
ERROR at line 1:
ORA-00980: synonym translation is no longer valid

SOLUTION:

This error indicates that synonym is invalid , which is likely due to invalid dependent objects.

Lets check this below scenarios:

1. Create a public synonym for a table:

SQL>  CREATE PUBLIC SYNONYM DBA_SYN FOR DBA_PART;

Synonym created.

SQL> select count(*) from DBA_SYN;

  COUNT(*)
----------
         0

2. Metadata of the synonym:

SQL> select dbms_metadata.get_ddL('SYNONYM','DBA_SYN','PUBLIC') from dual;

DBMS_METADATA.GET_DDL('SYNONYM','DBA_SYN','PUBLIC')
--------------------------------------------------------------------------------

  CREATE OR REPLACE NONEDITIONABLE PUBLIC SYNONYM "DBA_SYN" FOR "SYS"."DBA_PART"

3. Drop the dependent table(DBA_PART) and query the synonym:

— Query dependent objects of that synonym:

SQL> select owner,SYNONYM_NAME,TABLE_OWNER,TABLE_NAME from dba_synonyms where SYNONYM_NAME='DBA_SYN';

OWNER    SYNONYM_NAME  TABLE_OWNER  TABLE_NAME
-------- ------------- ------------ ---------------
PUBLIC   DBA_SYN       SYS          DBA_PART


SQL> select owner,object_name,object_type from dba_objects where object_name='DBA_PART';

OWNER    OBJECT_NAME   OBJECT_TYPE
-------- ------------- -----------------------
SYS       DBA_PART      TABLE 

SQL> drop table DBA_PART;

Table dropped.

SQL> select owner,object_name,object_type from dba_objects where object_name='DBA_PART';

no rows selected

SQL> select count(*) from DBA_SYN;
select count(*) from DBA_SYN
*
ERROR at line 1:
ORA-00980: synonym translation is no longer valid

Now querying the synonym throwing ORA-00980: synonym translation is no longer valid error.Because the dependent object is missing.

CONCLUSION:

When we get this error, we have to check the status of dependent object of that synonym. If this dependent object is missing or invalid, then take required action like compiling/recreating the object.