English

Error: ORA-22901: cannot compare nested table or VARRAY or LOB attributes of an object type

Error Message

When using the Oracle operators UNION, UNION ALL, INTERSECT or MINUS with an st_geometry attribute in the SELECT list, the following Oracle error is returned:

"ORA-22901: cannot compare nested table or VARRAY or LOB attributes of an object type".

Code:
SQL> SELECT p.shape
2 FROM parcels p
3 WHERE sde.st_envintersects(p.shape, 2115939, 141401, 2116397, 141805) = 1
4 UNION
5 SELECT e.shape
6 FROM easements e
7 WHERE sde.st_envintersects(e.shape, 2115939, 141401, 2116397, 141805) = 1;
SELECT p.shape
*
ORA-22901: cannot compare nested table or VARRAY or LOB attributes of an object type

Cause

The Oracle error, ORA-22901, indicates that the SQL statement is attempting a comparison of a nested table, VARRAY, or LOB attribute of an object type that was attempted in the absence of a MAP or ORDER method.

Solution or Workaround

When executing the SQL statement, remove the reference of the st_geometry attribute from the SELECT list or use the sde.st_astext operator to return the geometry in its well-known text representation, which can be compared by the specified operator.

Note:
Because the sde.st_astext operator returns the well-known text as an Oracle CLOB data type, the output value must be converted to a character string using the Oracle TO_CHAR function.


The following example demonstrates how to execute a query with the union operator when comparing geometries.

Code:
SQL> SELECT TO_CHAR(sde.st_astext(p.shape))
2 FROM parcels p
3 WHERE sde.st_envintersects(p.shape, 2115939, 141401, 2116397, 141805) = 1
4 UNION
5 SELECT TO_CHAR(sde.st_astext(e.shape))
6 FROM easements e
7 WHERE sde.st_envintersects(e.shape, 2115939, 141401, 2116397, 141805) = 1;