English

Bug: Encountering Oracle error ORA-29902: error in executing ODCIIndexStart() routine when creating spatial index with st_geometry

Description

Schemas in Oracle, approximately 24 characters or greater, which attempt to create a spatial index with st_geometry can result in the following Oracle error:

Code:
ORA-29902: error in executing ODCIIndexStart() routine
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SDE.ST_DOMAIN_METHODS", line 125.

The error may also appear in the sde_<service>.log with verbose enabled when creating a spatial index through ArcGIS.

For example, a schema named FACILITIES_MANAGEMENT_DEPT (the user name that owns the data) encounters the above error messages when creating an st_geometry spatial index.

The index is created, but the issue causes problems when displaying, querying, and identifying features.

Cause

In the sde.st_domain_methods package, the ODCIIndexStart function has a variable string defined as 32 characters, that is not long enough to support schemas names which exceed 24 characters and the name of the spatial index.

Workaround

Use a schema name with fewer characters or query the data without a spatial index (which encounters performance issues with large tables because each spatial query results in a full table scan).