oracle ORA-00980 同义词翻译在 PLSQL 中不再有效

ORA-00980 synonym translation no longer valid in PLSQL

提问by D Veloper

I've got a synonym on a remote Oracle database that I can access in SQL over a database link, eg,

我有一个远程 Oracle 数据库的同义词，我可以通过数据库链接在 SQL 中访问它，例如，

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.

如果我将上述语句放入 PLSQL 块中，它将无法编译，并给出错误消息“ORA-00980：同义词翻译不再有效”。标准解释是同义词指向的表已被删除等，但事实并非如此，因为该语句在 SQL 中有效。

采纳答案by D Veloper

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

感谢所有试图提供帮助的人。结果证明这是 Oracle 限制：

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

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.

适用于：

PL/SQL - 版本 9.2.0.8 及更高版本 本文档中的信息适用于任何平台。 在 2015 年 4 月 1 日检查相关性

症状

PL/SQL 块失败并显示错误：ORA-00980：从远程数据库中选择数据时，同义词翻译不再有效。下面的代码演示了这个问题：

在 DB3 上（创建表）

连接 u3/u3 DROP TABLE 选项卡；创建表选项卡（c1 号）；插入标签值 (1); 犯罪;

在 DB2 上（为 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; 删除同义词 syn2; 为 tab@dblink2 创建同义词 syn2; SELECT * FROM syn2;

在 DB1 上（创建 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; 删除同义词 syn1; 为 syn2@dblink1 创建同义词 syn1；从syn1中选择c1；

这在 SQL 中有效，但从 PL/SQL 调用时失败

申报号码；BEGIN SELECT c1 INTO num FROM syn1; 结尾; /

第 4 行错误：ORA-06550：第 4 行，第 3 列：PL/SQL：ORA-00980：同义词翻译不再有效 ORA-06550：第 4 行，第 3 列：PL/SQL：SQL 语句被忽略

原因

此问题已在错误 2829591 QUERING FROM A PL/SQL PROCEDURE IN 9I -> 8I-> 7.3.4, GETTING ORA-980 中报告。由于以下原因，此错误已作为“NOT A BUG”关闭

PL/SQL 无法在编译阶段指示中间数据库（DB2）跟随数据库链接。因此，为了编译和运行这个 PL/SQL 块，应该在前端数据库 DB1 上定义数据库链接 dblink1 和 dblink2。在运行时数据库链接期间，dblink2 将按预期在 DB2 中查找。

解决方案

要实施该解决方案，请执行以下步骤：

1. 在 DB1 上创建一个指向 DB3 的数据库链接 dblink2

SQL> create database link dblink2 connect to u3 使用'EMT102U6'标识的u3；

2. 在 DB1 上创建和编译 PL/SQL 块。

CREATE DATABASE LINK dblink2 CONNECT to u3 IDENTIFIED BY u3 using 'EMT102U6';

SELECT * FROM global_name@dblink2; 申报号码；BEGIN
SELECT c1 INTO num FROM syn1; 结尾; /PL/SQL 程序成功完成。

提示：另一种选择是在 PL/SQL 块中使用动态 SQL 作为变通方法。使用动态 SQL 时，数据库链接不是在编译时解析而是在运行时解析。

回答by a_horse_with_no_name

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

如果某些东西在 SQL 中有效但在 PL/SQL 中无效，那么在大多数情况下，这是权限问题。

Any privilege that a user received through a roleis not active when you enter a PL/SQL block. So most probably the SELECTprivilege on the underlying table was granted through a role and thus is not "active" in the PL/SQL block.

当您进入 PL/SQL 块时，用户通过角色获得的任何权限都不是活动的。所以很可能SELECT底层表的特权是通过角色授予的，因此在 PL/SQL 块中不是“活动的”。

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

通常的解决方法是将权限直接授予用户，而不是通过角色。

回答by Md. Kamruzzaman

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

当表/视图/过程的所有者与 SYNONYM 中提到的所有者不匹配时，我发现了这个问题。

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

示例：如果表 TABLE_BRACH 的所有者是 ownerA 并且在同义词中提到的表所有者是其他东西（不是 ownerA）。

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

解决方案： 1. 删除同义词 2. 用正确的所有者创建同名。

CREATE PUBLIC SYNONYM BRANCH FOR ownerA.TABLE_BRACH ;

回答by Abdul Majid

Workaround solution is to use an Oracle view instead.

解决方法是改用 Oracle 视图。

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;

回答by NavyPier

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

对于您在连接字符串中使用的用户，检查远程数据库授予的“my_synonym”必须几乎是“选择”，还要检查该同义词指向的对象（可能有人删除了表）。