English

Error: ORA-00980: synonym translation is no longer valid

Error Message

When using any of the following Spatial Type for Oracle (ESRI ST_GEOMETRY) operators from SQL with ArcSDE 9.2 for Oracle server,

ST_LineFromWKB
ST_MlineFromWKB
ST_MpointFromWKB
ST_MpolyFromWKB
ST_PointFromWKB
ST_PolyFromWKB

an error message similar to the following may appear:

Code:
SQL> UPDATE TABLE1
2 SET GEOMCOLUMN = ST_LINEFROMWKB(LOBCOLUMN)
3 WHERE OBJECTID = 27;

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. Public synonyms for these and other operators are created when installing early Beta versions of ArcSDE 9.3. The installer for ArcSDE 9.3 final does not create public synonyms for operators; therefore, this condition can occur under the following circumstances:

1. An early version of ArcSDE 9.3 Beta was installed, then

2. The Beta was uninstalled, then

3. ArcSDE 9.2 was installed.

Solution or Workaround

Beginning with ArcSDE 9.2 Service Pack 6 and above, please qualify the names of spatial type operators with the name of the owning schema 'SDE', for example: 'SDE.ST_MPointFromWKB'.

Uninstalling ArcSDE, or even dropping the Oracle user SDE, does not automatically drop any public synonyms.

  1. As the SDE user, these PUBLIC synonyms can be dropped. The following are the SQL commands to do this:
    Code:
    DROP PUBLIC SYNONYM ST_LineFromWKB;
    DROP PUBLIC SYNONYM ST_MLineFromWKB;
    DROP PUBLIC SYNONYM ST_MPointFromWKB;
    DROP PUBLIC SYNONYM ST_MPolyFromWKB;
    DROP PUBLIC SYNONYM ST_PointFromWKB;
    DROP PUBLIC SYNONYM ST_PolyFromWKB;