Database Software

I have a problem! I'm new in Oracle Spatial, but I've been working with pl/sql for a while. The thing is, I need to look for points (x,y) from geometry attribute from table B, inside geometry attribute (polygons) from table A. The issue: both tables have different data inside geometry field.

More details:

CREATE TABLE A 
(   
GEOMETRY MDSYS.SDO_GEOMETRY,
MANZENT VARCHAR2(255 BYTE), 
...
) 

CREATE TABLE B 
(  
ID_GIS NUMBER(8,0), 
GEOMETRY MDSYS.SDO_GEOMETRY,
...
)

When I run the Query:

SELECT A.MANZENT, A.GEOMETRY
FROM A, B
WHERE MDSYS.SDO_RELATE(A.GEOMETRY, B.GEOMETRY, 'mask=touch+coveredby') = 'TRUE';

It throw an error:

ORA-13226: Interface Not Supported Without a Spatial Index

ORA-06512: at "MDSYS.MD", line 1723

ORA-06512: at "MDSYS.MDERR", line 8

ORA-06512: at "MDSYS.SDO_3GL", line 70

13226. 00000 -  "interface not supported without a spatial index"

*Cause:    The geometry table does not have a spatial index.

*Action:   Verify that the geometry table referenced in the spatial operator

           has a spatial index on it.

Well, I looked for the Index in both tables, and it seems to be ok!:

  CREATE INDEX "DS_FDOX"."A_IDX" ON "DS_FDOX"."A" ("GEOMETRY")
   INDEXTYPE IS "MDSYS"."SPATIAL_INDEX" ;

  CREATE INDEX "DS_FDOX"."B_IDX" ON "DS_FDOX"."B" ("GEOMETRY")
   INDEXTYPE IS "MDSYS"."SPATIAL_INDEX" ;

Both tables has their metadata (ALL_SDO_GEOM_METADATA and USER_SDO_GEOM_METADATA) but; attribute RID NULL :

The SRDI should be 32719.

*** UPDATE ***

Checking the Index Status i get error on table A spatial Index:

select table_name, index_name, status, ityp_name, domidx_status, domidx_opstatus
from USER_INDEXES
where ityp_name='SPATIAL_INDEX';

A_IDX  I got "FAILED" in DOMIDX_OPSTATUS!

So, I deleted the spatial index A_IDX, but when I'm creating again the index:

CREATE INDEX A_IDX ON A (GEOMETRY)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;

I get this error:

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

ORA-13249: internal error in Spatial index: [mdidxrbd]

ORA-13249: Error in Spatial index: index build failed

ORA-13249: Error in spatial index: [mdrcrtxfergm]

ORA-13249: Error in spatial index: [mdpridxtxfergm]

ORA-13200: Internal error [ROWID:AAB64sAAEAAAGvsAAA] in spatial indexing

ORA-13206: Internal error [] while creating the spatial index

ORA-13365: Layer SRID does not match geometry SRID

ORA-06512: in "MDSYS.SDO_INDEX_METHOD_10I", line 10

The metadata from table A, in tables USER_SDO_GEOM_METADATA and ALL_SDO_GEOM_METADATA; in both tables the attribute SRID is null.

But the metadata from table B in tables USER_SDO_GEOM_METADATA and ALL_SDO_GEOM_METADATA is SRID 32719.

How can I determine the right SRID for table A? To  update the attribute SRID at tables USER_SDO_GEOM_METADATA and ALL_SDO_GEOM_METADATA.

More details:

- USER_SDO_GEOM_METADATA for table A:

Select * from USER_SDO_GEOM_METADATA WHERE table_name = 'A';

Result:

Thanks in advance!

Edited by: Sergio Palma H.