Adding metadata entries for Oracle spatial tables or views

Oracle Metadata

For Oracle spatial tables and views to be recognized by the FME:

• Oracle Spatial Reader,
• FeatureReader

there needs to be metadata entries in the MDSYS.SDO_GEOM_METADATA table. This only applies to spatial queries. If you use a WHERE to do an attribute query this problem does not arise.

If you get an FME error message something like:

 |ERROR |Execution of statement `SELECT "PARCEL_VIEW"."PARCEL_ID", "PARCEL_VIEW"."PRIMARYINDEX", "PARCEL_VIEW"."LANDUSE", "PARCEL_VIEW"."USES", "PARCEL_VIEW"."GEOM" FROM ( SELECT * FROM "PARCEL_VIEW" A  WHERE MDSYS.SDO_RELATE( A.GEOM, mdsys.sdo_geometry(2003,<strong>NULL</strong>,null,mdsys.sdo_elem_info_array(1,1003,3),mdsys.sdo_ordinate_array(3118280.75,10081402,3137693.75,10098911)),'mask=ANYINTERACT querytype=WINDOW') = 'TRUE' ) "PARCEL_VIEW" WHERE 1 = 1' did not succeed; error was `ORA-29902: error in executing ODCIIndexStart() routine

Then the spatial metadata probably does not exist. The highlighted NULL entry in the message above should be the SRID.

Spatial Tables

When Oracle spatial tables are created using SQL Plus or some other tool there may not be entries in the MDSYS.SDO_GEOM_METADATA table that defines the extents and coordinate system of data. You can check for your metadata entries usiing:

select * from USER_SDO_GEOM_METADATA where table_name='<table name>'

If you don't get any values returned you need to insert the metadata. To add entries issue an SQL command that is similiar to this:

insert into user_sdo_geom_metadata (table_name,column_name,diminfo,srid) values ('TABLE_NAME','GEOM_COLUMN_NAME', sdo_dim_array(sdo_dim_element('X',80000,100000,0.005),sdo_dim_element('Y',425000,450000,0.005)), 32039);  	  

...where the X, Y values define the minimum bounding box of the spatial data. In this case, the values are minX=80000, minY=425000, maxX=100000, maxY=450000 and the SRID value is 32039.

Spatial Views

If you create a spatial view in an Oracle database you also need to add information to the metadata table for the view to be defined. If you don't do this you will see the view in a table list with the Oracle (non-spatial) reader but not the Oracle Spatial Object reader. To add the metadata information use a command similar to this:

insert into user_sdo_geom_metadata using select 'RT43495_VIEW', column_name, diminfo, srid from all_sdo_geom_metadata where owner = 'DEV' and table_name = 'RT43495'; 	 		  

In this case the metadata entry is taken from the metadata entry for the table which is being used to generate the view. Don't forget to COMMIT the changes!!