
DROP TABLE test_geom2d;
DROP TABLE test_geom3d;

DELETE FROM user_sdo_geom_metadata
      WHERE table_name = 'test_geom2d';
DELETE FROM user_sdo_geom_metadata
      WHERE table_name = 'test_geom3d';

CREATE TABLE test_geom2d (
  ora_fid      NUMBER PRIMARY KEY,
  ora_geometry MDSYS.SDO_GEOMETRY
);

CREATE TABLE test_geom3d (
  ora_fid      NUMBER PRIMARY KEY,
  ora_geometry MDSYS.SDO_GEOMETRY
);

INSERT INTO user_sdo_geom_metadata
     VALUES ('test_geom2d', 'ora_geometry',
             MDSYS.sdo_dim_array (MDSYS.sdo_dim_element ('X', 0, 20, 0.01),
                                  MDSYS.sdo_dim_element ('Y', 0, 20, 0.01)
                                 ),
             NULL);

INSERT INTO user_sdo_geom_metadata
     VALUES ('test_geom3d', 'ora_geometry',
             MDSYS.sdo_dim_array (MDSYS.sdo_dim_element ('X', 0, 20, 0.01),
                                  MDSYS.sdo_dim_element ('Y', 0, 20, 0.01),
                                  MDSYS.sdo_dim_element ('Z', 0, 20, 0.01)
                                 ),
             NULL);

INSERT INTO test_geom2d
     VALUES (1,
             MDSYS.SDO_GEOMETRY (2003,                           -- 2D polygon
                                 NULL,
                                 NULL,
                                 MDSYS.sdo_elem_info_array (1, 1003, 1),
                                 MDSYS.sdo_ordinate_array (1, 1,
                                                           5, 1,
                                                           5, 7,
                                                           1, 7,
                                                           1, 1 -- Ending point
                                                          )    
                                ));

INSERT INTO test_geom3d
     VALUES (1,
             MDSYS.SDO_GEOMETRY (3003,                           -- 3D polygon
                                 NULL,
                                 NULL,
                                 MDSYS.sdo_elem_info_array (1, 1003, 1),
                                 MDSYS.sdo_ordinate_array (1, 1, 0,
                                                           5, 1, 1,
                                                           5, 7, 2,
                                                           1, 7, 1,
                                                           1, 1, 0 -- Ending point
                                                          )  
                                ));

CREATE INDEX test_geom2d_idx ON test_geom2d( ora_geometry) INDEXTYPE IS MDSYS.spatial_index;
CREATE INDEX test_geom3d_idx ON test_geom3d( ora_geometry) INDEXTYPE IS MDSYS.spatial_index;
