ERROR

Oracle error when the shape.area or shape.len attribute is not fully qualified when using a definition query with a layer stored using st_geometry

Last Published: April 25, 2020

Error Message

When using ArcMap and defining a layer's definition query, if the shape.area or shape.len attribute is referenced in the WHERE clause but not fully qualified, an underlying Oracle ORA-00904 error is encountered.

The following statement encountered a error during parse:

SELECT 1 SHAPE, WATER_BODIES.OBJECTID, WATER_BODIES.SHAPE.points, WATER_BODIES.SHAPE.numpts,
WATER_BODIES.SHAPE.entity, WATER_BODIES.SHAPE.minx, WATER_BODIES.SHAPE.miny, WATER_BODIES.SHAPE.maxx, 
WATER_BODIES.SHAPE.maxy, WATER_BODIES.rowid FROM TB.WATER_BODIES WORLDWATER 
WHERE SDE.ST_EnvIntersects(WATER_BODIES.SHAPE,:1,:2,:3,:4) = 1  AND (("NATION" = 52 AND "SHAPE.AREA" >= .000010))

Error encountered: ORA-00904

Cause

The Oracle parse error occurs because the shape.area or shape.len attribute is not fully qualified with its table name or a table alias in the SQL statement being executed in the database.

Without fully qualifying the attribute, the shape.area or shape.len attributes are not valid attributes for the table being queried. The area and length attributes are properties of the st_geometry type and therefore must be fully qualified with the table alias when referenced within the SQL statement.

Solution or Workaround

The solution to the error is to fully qualify the shape.area or shape.len attribute in the definition queries WHERE clause.

For example when adding a definition query for a layer named 'water_bodies' in ArcMap, an Oracle parse error with the following syntax is encountered:

"NATION" = 52 AND "SHAPE.AREA" >= .000010
By fully qualifying the attribute with the table name, no error is encountered.
"NATION" = 52 AND "WATER_BODIES.SHAPE.AREA" >= .000010

Article ID:000010328

Software:
  • Legacy Products

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Related Information

Discover more on this topic