Database Software

Hello,

I'm trying to add a geometry column to an oracleXE table.

commands issued are:
1) alter table specimen add (geom mdsys.sdo_geometry);
2) update specimen set geom = mdsys.sdo_geometry(2001,8307,mdsys.sdo_point_type(dec_long,dec_lat,null),null,null);
3) insert into mdsys.user_sdo_geom_metadata (table_name,column_name,diminfo,srid) values ('specimen','geom',mdsys.sdo_dim_array(mdsys.sdo_dim_element('longitude',-180,180,1), mdsys.sdo_dim_element ('latitude',-90,90,1)),8307);

all above seem to work correctly and if I list user_sdo_geom_metadata I get:

TABLE_NAME
--------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
DIMINFO(SDO_DIMNAME, SDO_LB, SDO_UB, SDO_TOLERANCE)
--------------------------------------------------------------------------------
SRID
----------
SPECIMEN
GEOM
SDO_DIM_ARRAY(SDO_DIM_ELEMENT('longitude', -180, 180, 1), SDO_DIM_ELEMENT('latit
ude', -90, 90, 1))
8307

TABLE_NAME
--------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
DIMINFO(SDO_DIMNAME, SDO_LB, SDO_UB, SDO_TOLERANCE)
--------------------------------------------------------------------------------
SRID
----------

which also looks correct.


However, when I try to create a spatial index with:

create index specimen_sidx on specimen(geom) indextype is mdsys.spatial_index;

I get the error:


ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE 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 10

Does anyone have a fix or any idea why it fails?

Any help appreciated,
Dave