Edition-Based Redefinition (EBR) in Action: Resolving Synonym Translation Errors

While working with Oracle Applications schemas, you may encounter the error ORA-00980: synonym translation is no longer valid when accessing editioning views or objects through a synonym. This article explains a practical example of how this issue arises and how to resolve it.

---

Scenario

A user TEST tried to access the APPS schema object FND_USER_RESP_GROUPS_DIRECT using a synonym.

SQL> SHOW USER
USER is "TEST"

The user then connected and created a synonym for the editioning view:

SQL> CONN test/***
Connected.

SQL> CREATE OR REPLACE SYNONYM "FND_USER_RESP_GROUPS_DIRECT" FOR APPS.FND_USER_RESP_GROUPS_DIRECT;

Synonym created.

However, when querying the synonym, the following error occurred:

SQL> SELECT COUNT(1) FROM FND_USER_RESP_GROUPS_DIRECT;
SELECT COUNT(1) FROM FND_USER_RESP_GROUPS_DIRECT
                     *
ERROR at line 1:
ORA-00980: synonym translation is no longer valid

---

Root Cause

This issue happens because the TEST user is not edition-enabled, while the target object in the APPS schema is an editioning view.
Edition-based redefinition (EBR) in Oracle allows for multiple versions of application objects (like packages and views) across different editions.
When a non-editioned user tries to access editioned objects, Oracle cannot resolve the synonym, leading to this error.

To verify the editioning status of the user:

SQL> SELECT EDITIONS_ENABLED FROM DBA_USERS WHERE USERNAME='TEST';

E
-
N

The result N indicates the user is not edition-enabled.

---

Resolution

Enable editions for the user to allow access to editioned objects:

SQL> ALTER USER test ENABLE EDITIONS;

User altered.

Confirm the change:

SQL> SELECT EDITIONS_ENABLED FROM DBA_USERS WHERE USERNAME='TEST';

E
-
Y

Reconnect as the user and retry the query:

SQL> CONN test/***
Connected.

SQL> SELECT COUNT(1) FROM FND_USER_RESP_GROUPS_DIRECT;

  COUNT(1)
----------
     18916

The query now executes successfully.

---

Key Takeaway

When creating synonyms for editioning views in Oracle E-Business Suite or any EBR-enabled schema, ensure that the referencing user has editioning enabled.
Otherwise, Oracle will fail to translate the synonym, resulting in the ORA-00980 error.