English

Error: Encountering Oracle error ORA-29855 when creating an st_spatial_index in Oracle

Error Message

When creating an ST_Geometry spatial index, sde.st_spatial_index, the following Oracle error may display: "ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine".

The following example demonstrates the CREATE INDEX statement and the Oracle error when creating a st_spatial_index on the BUILDINGS table's shape attribute.

Code:
SQL> CREATE INDEX shape_idx ON buildings (shape) INDEXTYPE IS
2 sde.st_spatial_index PARAMETERS ('ST_GRIDS=500 ST_SRID=38
3 ST_COMMIT_ROWS=10000 PCTFREE 0 INITRANS 4');
CREATE INDEX shape_idx ON buildings (shape) INDEXTYPE IS
*

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 182
ORA-06512: at "SDE.SPX_UTIL", line 680
ORA-06512: at "SDE.ST_DOMAIN_METHODS", line 925

Cause

A carriage return is included in the parameters clause, which is passed to the sde.spx_util procedure used for parsing the parameters string.

The procedure's role is to parse the parameters clause and extract each token as an argument for the creation of the st_spatial_index.

When carriage returns are present, this additional character causes the parsing routines to fail while converting a string to a number.

The carriage returns are created each time a new line is inserted in the parameters clause.

Solution or Workaround

To avoid the Oracle error encountered in the sde.spx_util procedure, do not enter any carriage returns in the parameters clause of the CREATE INDEX statement.

For example, the carriage line entered at the end of line 2 after the st_srid=38 argument causes the creation of the index to fail.

Code:
SQL> CREATE INDEX shape_idx ON buildings (shape) INDEXTYPE IS
2 sde.st_spatial_index PARAMETERS ('ST_GRIDS=500 ST_SRID=38
3 ST_COMMIT_ROWS=10000 PCTFREE 0 INITRANS 4');


One can avoid the carriage return by allowing the parameters clause syntax to wrap on the current input line.

Code:
SQL> CREATE INDEX shape_idx ON buildings (shape) INDEXTYPE IS
2 sde.st_spatial_index PARAMETERS ('ST_GRIDS=500 ST_SRID=38 ST_COMMIT_ROWS=10000 PCTFREE 0 INITRANS 4');

Index created.