Frequently asked question

What are the best practices for writing SQL in Oracle with ST_Geometry attributes?

Last Published: April 25, 2020


When creating Oracle tables with the spatial type, ST_Geometry or writing SQL statements that reference ST_Geometry constructors and operators, the best practice is to fully qualify the ownership of the ST_Geometry type, constructors, and operators.

Fully qualifying means to include the owner of the object when referencing the object, for example: sde.st_geometry. The owner of the ST_Geometry type is 'sde'.

Without fully qualifying the ST_Geometry type, constructor, or operator, there is potential for ambiguity if the public synonym for the object is not present in the Oracle instance.

The following examples demonstrate creating a table in SQL*Plus without fully qualifying the ST_Geometry attribute, the error encountered when the ST_Geometry attribute is not fully qualified, and successfully creating a table with a fully-qualified attribute:
2 (name VARCHAR2(32),
3 shape st_geometry);
shape st_geometry)
ERROR at line 3:
ORA-00902: invalid datatype

2 (name VARCHAR2(32),
3 shape sde.st_geometry);

Table created.

The following example demonstrates an error one might encounter when attempting to execute the ST_Intersects operator without fully qualifying the operator as 'sde.st_intersects'.
SQL> SELECT FROM states, rivers
3 AND st_intersects(, = 1;
AND st_intersects(, = 1
ERROR at line 3:
ORA-00904: "ST_INTERSECTS": invalid identifier

Article ID:000009699

  • ArcMap 9 x
  • Legacy Products

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Discover more on this topic