DROP TABLE AUTO_INCR_TAB CASCADE CONSTRAINTS;
DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'AUTO_INCR_TAB';
DROP SEQUENCE OBJECTID_SEQ;

CREATE TABLE AUTO_INCR_TAB
("OBJECTID" NUMBER(38,0),
"NAME" VARCHAR2(64),
"GEOM" MDSYS.SDO_GEOMETRY);
--CONSTRAINT AUTO_INCR_TAB_PK PRIMARY KEY("OBJECTID"));

INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME, COLUMN_NAME, DIMINFO, SRID)
VALUES ('AUTO_INCR_TAB', 'GEOM', SDO_DIM_ARRAY(SDO_DIM_ELEMENT('Y', 6987000, 7165000, 0.005),SDO_DIM_ELEMENT('X', 594000, 964000, 0.005)), 2154);

CREATE INDEX AUTO_INCR_TAB_SIDX
ON AUTO_INCR_TAB(GEOM)
INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS('sdo_indx_dims=2, layer_gtype=MULTIPOLYGON');

CREATE SEQUENCE OBJECTID_SEQ;

CREATE OR REPLACE TRIGGER AUTO_INCR_TAB_BI
BEFORE INSERT ON AUTO_INCR_TAB
FOR EACH ROW
BEGIN
  SELECT OBJECTID_SEQ.nextval INTO :new.OBJECTID FROM dual;
END;
/

INSERT INTO AUTO_INCR_TAB ("NAME","GEOM") VALUES
( 
'One',
MDSYS.SDO_GEOMETRY(2003,2154,NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,1),MDSYS.SDO_ORDINATE_ARRAY(703988.001692457,7059045.39410058,703792.311411992,7059061.04932302,703674.897243714,7058888.84187621,704066.277804642,7058841.8762089,703988.001692457,7059045.39410058))
);

commit;
