Underlying Oracle DBMS error ORA-21500 with ST_Geometry attributes

Description

Using ArcGIS to identify or select a feature from a feature class stored as an ST_Geometry attribute or attempting to open the layer's attribute table results in the underlying Oracle DBMS error ORA-21500.

The error is either logged in the ArcSDE error log or the following error message is displayed in ArcGIS:

"Underlying DBMS error [ORA-21500: internal error code, arguments: [%s],[%s],[%s],[%s],[%s],[%s],[%s],[%s]]".

Cause

The cause of the error is an internal Oracle error which neither ArcGIS nor ArcSDE can control. The error is encountered when the application generates a SQL statement using an asterisk in the SELECT list (SELECT * FROM...).

For further information on the Oracle error please see Oracle's Metalink Note:49375.1.

Workaround

There are two possible workarounds for this issue. Ensure there is a spatial index present for the feature class and/or add an additional attribute after the ST_Geometry attribute.

To verify if a spatial index is present, using ArcCatalog, connect to the ArcSDE instance as the feature class owner. Select the feature class. Open the properties dialog box. Select the indexes tab and verify that the spatial index is present.

To add a new attribute to a feature class in ArcCatalog, open the feature class properties. Select the fields tab and add the new attribute.

Once the ST_Geometry attribute is no longer in the last position of the SELECT * list, the internal ORA-21500 error is no longer encountered.