Translation of ORA-00980 syntax translation in PLSQL is no longer valid

Question

I have a synonym in a remote Oracle database, access to which I can get in SQL using a database link, for example

insert into my_table select * from my_synonym@my _database_link; 

If I put the above statement in the PLSQL block, it will not compile with the error message "ORA-00980: synonym translation is no longer valid." The standard explanation is a table in which a synonym is indicated, etc., but this is not so, because the statement works in SQL.

Answer 1

Thanks to everyone who tried to help. This turned out to be a limitation of Oracle:

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

The information in this article applies to the following products.

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

Problem

The PL / SQL block fails with an error: ORA-00980: synonym translation no longer duration when selecting data from a remote database. The following code demonstrates this problem:

In DB3 (create table)

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

In DB2 (create a synonym for tables in DB3)

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

In DB1 (create a synonym for DB2)

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

This works in SQL, but it fails when calling from PL / SQL

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

ERROR on row 4: ORA-06550: row 4, column 3: PL / SQL: ORA-00980: synonym for translation is no longer valid ORA-06550: row 4, column 3: PL / SQL: SQL expression is ignored

CAUSE

This issue was reported in Bug 2829591 QUERING FROM A PL / SQL PROCEDURE IN 9I → 8I-> 7.3.4, OBTAINING OPA-980. This error was closed as "NOT BUG" for the following reasons.

PL / SQL cannot instruct the secondary database (DB2) to keep track of the database link at compile time. Therefore, for this PL / SQL block to compile and run, both database links dblink1 and dblink2 must be defined in the foreground database - DB1. During operation, a link to the dblink2 database will search in DB2 as expected.

DECISION

To implement the solution, follow these steps:

• Create dblink2 database link in DB1, pointing to DB3

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

2. Create and compile a PL / SQL block in DB1.

CREATE DATABASE LINK dblink2 CONNECT to u3 IDENTIFIED u3 USE 'EMT102U6';

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

TIP. Another option is to use dyanmic SQL in the PL / SQL block how to work around. When using dynamic SQL, a database reference is not resolved at compile time, but at run time.

Answer 2

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

Any privilege received by the user through the role is not active when entering the PL / SQL block. Therefore, most likely, the SELECT privilege in the base table was granted through the role and, therefore, is not "active" in the PL / SQL block.

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

Answer 3

Checking remote databases for "my_synonym" should be almost "selected" for the user you use in the connection string, also check the object that this synonym points to (maybe someone deleted the table).

Answer 4

I found this problem when the owner of the table / view / procedure does not match the owner specified in SYNONYM.

Example: if the owner of table TABLE_BRACH is owner A, and in Synonym, the mentioned owner of the table is something else (Not ownerA).

Solution: 1. Uncheck SYNONYM 2. Create this with the same name with the correct owner.

 CREATE PUBLIC SYNONYM BRANCH FOR ownerA.TABLE_BRACH ; 

Answer 5

A workaround is to use the Oracle view instead.

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

Then refer to the view in your package or procedure, i.e.

 insert into my_table select * from v_my_synonym;