This discussion is archived
2 Replies Latest reply: Feb 5, 2013 10:58 PM by 989315 RSS

java.sql.SQLException: ORA-29902: error in executing ODCIIndexStart() routi

989315 Newbie
Currently Being Moderated
Hope someone help me..

After executing the query

select C.NN_NODE_ID, C.name
FROM table_name c
WHERE SDO_INSIDE(c.geom,
SDO_GEOMETRY(2003, NULL, NULL,
SDO_ELEM_INFO_ARRAY(1,1003,3),
SDO_ORDINATE_ARRAY(443169,4205815,448648,4207356))) = 'TRUE';

I get the following error


ORA-29902: error in executing ODCIIndexStart() routine
ORA-13203: failed to read USER_SDO_GEOM_METADATA view
ORA-13203: failed to read USER_SDO_GEOM_METADATA view
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 416
29902. 00000 - "error in executing ODCIIndexStart() routine"
*Cause:    The execution of ODCIIndexStart routine caused an error.
*Action:   Examine the error messages produced by the indextype code and
take appropriate action.


What should I do now?
Thank you..
  • 1. Re: java.sql.SQLException: ORA-29902: error in executing ODCIIndexStart() routi
    NoelKhan Journeyer
    Currently Being Moderated
    user13369336,

    That table probably doesn't have a spatial index.

    These instructions will get you going, but you'll want to know your data and read the documentation to guide your decisions.

    Login to the appropriate schema using Sql-Plus and try:
    -- "check if metadata present"
    SELECT * FROM user_sdo_geom_metadata WHERE UPPER(table_name) = UPPER('&table_name')
    
    -- "check if spatial_index present"
    SELECT index_name, status, ityp_name 
    FROM user_indexes 
    WHERE index_type = 'DOMAIN' AND table_name = UPPER('&table_name')
    
    -- "post all results on the forum"
    SELECT * FROM v$version;
    If there are no results, commit a row in user_sdo_geom_metadata table.
    -- "determine minimumally bounding extents: low_x, high_x, low_y, high_y"
    SELECT SDO_AGGR_MBR(geom) FROM &table_name
    INSERT INTO user_sdo_geom_metadata VALUES (
    UPPER('&table_name'), 'GEOM', SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', &low_x, &high_x, &&tol), SDO_DIM_ELEMENT('Y', &low_y, &high_y, &tol)), NULL)
    Commit then create a spatial index:
    COMMIT;
    CREATE INDEX &index_name ON &table_name (geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX
    Then try your query again.

    Regards,
    Noel
  • 2. Re: java.sql.SQLException: ORA-29902: error in executing ODCIIndexStart() routi
    989315 Newbie
    Currently Being Moderated
    Thanks ..
    Finally I found that after creating the spatial index and updating metadata, I renamed the table wo updating the metadata!!
    Also srid needed to be defined in my query...
    Your select queries from db saved me..
    Thank you..

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points