2 Replies Latest reply: Feb 6, 2013 12:58 AM by 989315 RSS

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

    989315
      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
          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
            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..