Frequently asked question
What are the best practices for writing SQL in Oracle with ST_Geometry attributes?
Answer
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:
SQL> CREATE TABLE rivers
2 (name VARCHAR2(32),
3 shape st_geometry);
shape st_geometry)
*
ERROR at line 3:
ORA-00902: invalid datatype
SQL> CREATE TABLE rivers
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 states.name FROM states, rivers
2 WHERE rivers.name = 'MISSOURI'
3 AND st_intersects(states.name, rivers.name) = 1;
AND st_intersects(states.name, rivers.name) = 1
*
ERROR at line 3:
ORA-00904: "ST_INTERSECTS": invalid identifier
Article ID:000009699
Software:
- ArcMap 9 x
- Legacy Products
Receive notifications and find solutions for new or common issues
Get summarized answers and video solutions from our new AI chatbot.
Discover more on this topic
Search for related information
Find training related to this topic
Explore ideas and give feedback
Get help from ArcGIS experts
Download the Esri Support App