Loading Shapefiles (SHP) into Oracle Spatial

When working with SHP files be aware that polygon data with holes or multi-part polygons (with/without hole) can often have holes (2003) and multi-parts (1003) with the wrong rotation. Wrong rotation is for outer shells (1003) that means Clockwise as against Clockwise and for inner shells or holes (2003) this means anti-clockwise when they should be clockwise. Many shape to Oracle loading tools will not correct this (not even MapBuilder’s shapefile loader). So, unless you know that your loader is correcting the rotation I would run MDSYS.SDO_GEOM.SDO_UNION against the loaded polygons to ensure that they are correct.

So, after loading, if multi-part polygons (sdo_gtype X007) are appearing as single-part polygons (sdo_gtype x003) with sdo_elem_info showing the disparate parts appearing as holes (2003 when they should be 1003), then do this:

update <my_table> a
   set a.<geom_column> = mdsys.sdo_geom.sdo_union(a.<geom_column>,a.<geom_column>,<your tolerance>);
commit;