Encountering Oracle error ORA-29903: error in executing ODCIIndexFetch() routine when using spatial relational operators with st_geometry

Description

Executing an SQL statement with an st_geometry spatial relational operator (st_intersects, st_within, etc.) in very specific situations, only with ArcGIS 9.3.1 Service Pack 1, can encounter the Oracle errors:

"ORA-29903: error in executing ODCIIndexFetch() routine
ORA-01002: fetch out of sequence
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1209
ORA-06512: at "SYS.DBMS_SQL", line 328
ORA-06512: at "SDE.SPX_UTIL", line 3082
ORA-06512: at "SDE.ST_DOMAIN_METHODS", line 636".

The following example demonstrates the error condition.

Code:
SQL> SELECT a.owner, a.address FROM parcels a, centerlines b
WHERE b.name = 'Highland Ave' 
AND sde.st_intersects(a.shape,sde.st_buffer(b.shape, 100)) = 1;

ORA-29903: error in executing ODCIIndexFetch() routine
ORA-01002: fetch out of sequence
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1209
ORA-06512: at "SYS.DBMS_SQL", line 328
ORA-06512: at "SDE.SPX_UTIL", line 3082
ORA-06512: at "SDE.ST_DOMAIN_METHODS", line 636

Cause

This potential error is limited to ArcGIS 9.3.1 Service Pack 1 only.

The problem is only encountered when using a spatial relational operator (st_intersects, st_within, etc.) when the access path uses the spatial index, and when the cursor fetching candidate geometries for the relation being performed, fetches exactly 200 objects.

When the spatial index is used as the access path when processing spatial relational operators, an internal cursor is used to query and fetch potential candidate geometries. The loop to process the candidate rows does not properly exit from the loop. Because the process re-enters the loop and attempts to fetch from the cursor which was previously exhausted, the routine encounters the Oracle error "ORA-01002: fetch out of sequence".

Workaround