Problem: Incorrect results returned when querying st_geometry attributes in Oracle when the access path is the spatial index
Incorrect results are returned when querying an st_geometry attribute in Oracle if the access path to produce the result set uses the spatial index. ESRI Binary and Oracle SDO_GEOMETRY spatial storage types are unaffected.
The cause of the problem is a bug in Oracle 10gR2 and 11gR1 (number 6756089 for UNIX Platforms and 6867052 for Windows):
"USING A DBMS TYPE IN OCI AND FLOATS ARE BEING ROUNDED OFF."
When constructing an st_geometry object, if the precision of the object's envelope coordinates (minx, miny, maxx, maxy) exceed five decimal places, Oracle truncates the value of the attribute at the five decimal. The truncation results in data corruption because the precision of the coordinate values are lost.
In addition, because the st_geometry envelope attributes have been truncated, when the object is indexed, the entries in the spatial index are incorrect. The inaccurate index entries can lead to incorrect results when querying the st_geometry attribute and the access path is the spatial index.
From ESRI's testing, currently this affects Oracle 10.2.0.2, 10.2.0.3, 10.2.0.4, and 18.104.22.168.
For additional information on the issue, search Oracle's Metalink Bug Database using bug number 6756089 (UNIX) or 6867052 (Windows).
Solution or Workaround
The only solution to the problem is to apply Oracle Patch 6756089 or 6867052.
The patch must be obtained from Oracle's, Metalink Web site.
To locate the patch on Metalink, perform a 'Quick Find', select 'Patch Number' from the drop-down list of sources to search, and enter the patch number 6756089 or 6867052.
When the patch is located, select the appropriate platform, and download the patch. Follow the instructions in the README file on how to install the patch.
If the platform being used is currently not available, continue to check Metalink until the patch becomes available, or contact Oracle support for details on when the platform patch will be released.