ERROR
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
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.
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;
Article ID:000010450
Get help from ArcGIS experts
Download the Esri Support App