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
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
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 len 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