Skip to content

Instantly share code, notes, and snippets.

@thbaumann
Created November 3, 2021 10:37
Show Gist options
  • Save thbaumann/6f2b5da6e5f6b74f802dde7230835055 to your computer and use it in GitHub Desktop.
Save thbaumann/6f2b5da6e5f6b74f802dde7230835055 to your computer and use it in GitHub Desktop.
Testlayer QGIS Oracle
--DROP TABLE testlayer_polygone;
CREATE TABLE testlayer_polygone
(id NUMBER(20),
name VARCHAR2(15) NOT NULL,
geom "MDSYS"."SDO_GEOMETRY",
CONSTRAINT testlayer_polygone_pk PRIMARY KEY (id)
);
--select * from all_sdo_geom_metadata WHERE table_name = upper('testlayer_polygone');
--DELETE user_sdo_geom_metadata WHERE table_name = upper('testlayer_polygone');
INSERT INTO user_sdo_geom_metadata VALUES (upper('testlayer_polygone'),'GEOM', sdo_dim_array(sdo_dim_element('X',389071.991483242,393260.181068965,0.005),sdo_dim_element('Y',5303890.76972698,5306215.18506897,0.005)),25832);
--DROP INDEX testlayer_polygone_idx FORCE;
CREATE INDEX testlayer_polygone_idx ON testlayer_polygone(geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS ('sdo_indx_dims=2, layer_gtype=MULTIPOLYGON');
DROP SEQUENCE s_testlayer_id;
CREATE SEQUENCE s_testlayer_id
INCREMENT BY 1
START WITH 1
MINVALUE 1
MAXVALUE 99999999999999999999
NOCYCLE
NOORDER
CACHE 20;
-- ID Trigger
--DROP TRIGGER trg_testlayer_polygone_id;
create or replace trigger trg_testlayer_polygone
before insert on "TESTLAYER_POLYGONE"
for each row
begin
if inserting then
if :NEW."ID" is null then
select S_TESTLAYER_ID.nextval into :NEW."ID" from dual;
end if;
end if;
end;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment