DELETE FROM USER_SDO_GEOM_METADATA WHERE USER_SDO_GEOM_METADATA.TABLE_NAME='ORA_POLY_POINTS';
DELETE FROM USER_SDO_GEOM_METADATA WHERE USER_SDO_GEOM_METADATA.TABLE_NAME='SHAPE_POLY_LAYER';
DROP VIEW SHAPE_POLY_LAYER;
DROP INDEX ORA_POLY_POINTS_SHAPE_IDX;

DELETE FROM ORA_POLY_POINTS;
DROP TABLE  ORA_POLY_POINTS;

CREATE TABLE ORA_POLY_POINTS(
     NAME VARCHAR2(15),
     INTVAL NUMBER(12,0),
     ID NUMBER(3,0) PRIMARY KEY,
     SHAPE MDSYS.SDO_GEOMETRY
);

INSERT INTO  USER_SDO_GEOM_METADATA VALUES 
( 
  'ORA_POLY_POINTS',      -- TABLE_NAME 
  'SHAPE',       -- COLUMN_NAME 
  SDO_DIM_ARRAY     -- DIMINFO attribute for storing dimension bounds, tolerance 
  ( 
    SDO_DIM_ELEMENT 
    ( 
      'LONGITUDE',  -- DIMENSION NAME for first dimension 
      -180,         -- SDO_LB for the dimension 
      180,          -- SDO_UB for the dimension 
      0.5           -- Tolerance of 0.5 meters 
    ), 
    SDO_DIM_ELEMENT 
    ( 
      'LATITUDE',   -- DIMENSION NAME for second dimension 
      -90,          -- SDO_LB for the dimension 
      90,           -- SDO_UB for the dimension 
      0.5           -- Tolerance of 0.5 meters 
     ) 
   ), 
   4326             -- SRID value for specifying a geodetic coordinate system 
);

CREATE INDEX ORA_POLY_POINTS_SHAPE_IDX ON ORA_POLY_POINTS (SHAPE) INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS ('SDO_INDX_DIMS=2,LAYER_GTYPE=POLYGON');


INSERT INTO ORA_POLY_POINTS VALUES 
(
  'Steve',
    3,
  '3',  
  SDO_GEOMETRY 
  ( 
    2003,  -- SDO_GTYPE format: D00T. Set to = POLYGON 
    4326,  -- SDO_SRID 
    NULL,  -- SDO_POINT attribute set to NULL 
    SDO_ELEM_INFO_ARRAY  -- SDO_ELEM_INFO attribute (see Table 4-2 for values) 
    ( 
      1,   -- Offset is 1 
      1003,   -- For polygons, the element-type is 1003 (the T value in SDO_GTYPE is 3).
      1    -- For line strings and polygons, the interpretation is 1 if the connectivity is by straight lines
    ), 
   SDO_ORDINATE_ARRAY   -- SDO_ORDINATES attribute 
   ( 
           -77.04487, 38.9043742,  -- coordinates of first vertex 
      -77.046645, 38.9040983, -- other vertices 
      -77.04815, 38.9033127, 
	  -77.049155, 38.9021368, 
      -77.049508, 38.9007499, 
	  -77.049155, 38.899363, 
	  -77.048149, 38.8981873, 
      -77.046645, 38.8974017, 
	  -77.04487, 38.8971258, 
	  -77.043095, 38.8974017, 
      -77.041591, 38.8981873, 
	  -77.040585, 38.899363, 
	  -77.040232, 38.9007499, 
      -77.040585, 38.9021368, 
	  -77.04159, 38.9033127, 
	  -77.043095, 38.9040983, 
      -77.04487, 38.9043742   -- coordinates of last vertex same as first vertex
    ) 
  ) 
);


CREATE OR REPLACE FORCE VIEW SHAPE_POLY_LAYER ("ID", "GEOMETRY")
AS
  SELECT ORA_POLY_POINTS.id, ORA_POLY_POINTS.SHAPE AS geometry FROM ORA_POLY_POINTS WHERE ORA_POLY_POINTS.id != 0;

INSERT INTO  USER_SDO_GEOM_METADATA VALUES 
( 
  'SHAPE_POLY_LAYER',      -- TABLE_NAME 
  'GEOMETRY',       -- COLUMN_NAME 
  SDO_DIM_ARRAY     -- DIMINFO attribute for storing dimension bounds, tolerance 
  ( 
    SDO_DIM_ELEMENT 
    ( 
      'LONGITUDE',  -- DIMENSION NAME for first dimension 
      -180,         -- SDO_LB for the dimension 
      180,          -- SDO_UB for the dimension 
      0.5           -- Tolerance of 0.5 meters 
    ), 
    SDO_DIM_ELEMENT 
    ( 
      'LATITUDE',   -- DIMENSION NAME for second dimension 
      -90,          -- SDO_LB for the dimension 
      90,           -- SDO_UB for the dimension 
      0.5           -- Tolerance of 0.5 meters 
     ) 
   ), 
   4326             -- SRID value for specifying a geodetic coordinate system 
);

SELECT SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(a.SHAPE, 0.5) 
is_valid 
FROM ORA_POLY_POINTS a 
WHERE a.id=3;