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

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.

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;