English

Error: ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine

Error Message

When attempting to create a spatial index on a st_geometry attribute with SQL, the command fails with the following Oracle error:

"ORA-29855 error occurred in the execution of ODCIINDEXCREATE routine".

Cause

In this specific case, the Oracle error ORA-29855 is being encountered because the parameters string clause contains an unnecessary comma between the st_srid and st_grids parameters.

Code:
SQL> CREATE INDEX shp_idx_1k ON st_relation_perf (shape) INDEXTYPE IS sde.st_spatial_index
2 parameters('st_srid=12, st_grids=25000');
CREATE INDEX shp_idx_1k ON st_relation_perf (shape) INDEXTYPE IS sde.st_spatial_index
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "SDE.SPX_UTIL", line 190
ORA-06512: at "SDE.SPX_UTIL", line 696
ORA-06512: at "SDE.ST_DOMAIN_METHODS", line 1438

Solution or Workaround

  • The correct syntax for creating the index is the following:

    Code:
    SQL> CREATE INDEX shp_idx_1k ON st_relation_perf (shape) INDEXTYPE IS sde.st_spatial_index
    2 parameters('st_srid=12 st_grids=25000');

  • When creating a spatial index do not insert any commas between the arguments in the parameters clause.