declare begin begin execute immediate 'CREATE TABLE SDO_GEOM_METADATA_TABLE ( SDO_OWNER varchar2(32), SDO_TABLE_NAME varchar2(32), SDO_COLUMN_NAME varchar2(1024), SDO_DIMINFO MDSYS.SDO_DIM_ARRAY, SDO_SRID number, CONSTRAINT unique_layers PRIMARY KEY (SDO_OWNER,SDO_TABLE_NAME, SDO_COLUMN_NAME)) '; exception when others then NULL; end; end; / Alter table SDO_GEOM_METADATA_TABLE modify (SDO_COLUMN_NAME varchar2(1024)); declare begin begin execute immediate 'CREATE INDEX SDO_GEOM_IDX ON SDO_GEOM_METADATA_TABLE(SDO_OWNER,SDO_TABLE_NAME) '; exception when others then NULL; end; end; / CREATE OR REPLACE TRIGGER chk_sdo_dimname BEFORE INSERT OR UPDATE ON MDSYS.SDO_GEOM_METADATA_TABLE FOR EACH ROW DECLARE cnt NUMBER; res NUMBER; BEGIN FOR cnt IN 1 .. :NEW.sdo_diminfo.COUNT LOOP SELECT REGEXP_INSTR(:NEW.sdo_diminfo(cnt).sdo_dimname, '[^[:alnum:]_]') INTO res FROM DUAL; IF (res > 0) THEN mderr.raise_md_error('MD', 'SDO_GEOM_METADATA_TABLE',-13249, 'Only alphanumeric characters and "_" are allowed in SDO_DIMNAME'); END IF; END LOOP; END; / CREATE OR REPLACE VIEW USER_SDO_GEOM_METADATA AS SELECT SDO_TABLE_NAME TABLE_NAME, SDO_COLUMN_NAME COLUMN_NAME, SDO_DIMINFO DIMINFO, SDO_SRID SRID FROM SDO_GEOM_METADATA_TABLE WHERE sdo_owner = sys_context('userenv', 'CURRENT_SCHEMA'); CREATE OR REPLACE VIEW all_sdo_geom_metadata AS SELECT b.SDO_OWNER OWNER, b.SDO_TABLE_NAME TABLE_NAME, b.SDO_COLUMN_NAME COLUMN_NAME, b.SDO_DIMINFO DIMINFO, b.SDO_SRID SRID FROM mdsys.SDO_GEOM_METADATA_TABLE b, all_objects a WHERE b.sdo_table_name = a.object_name AND b.sdo_owner = a.owner AND a.object_type in ('TABLE', 'SYNONYM', 'VIEW') UNION ALL SELECT b.SDO_OWNER OWNER, b.SDO_TABLE_NAME TABLE_NAME, b.SDO_COLUMN_NAME COLUMN_NAME, b.SDO_DIMINFO DIMINFO, b.SDO_SRID SRID FROM mdsys.SDO_GEOM_METADATA_TABLE b, all_object_tables a WHERE b.sdo_table_name = a.table_name AND b.sdo_owner = a.owner; grant select,insert, delete, update on user_sdo_geom_metadata to public; grant select on all_sdo_geom_metadata to public; create or replace public synonym user_sdo_geom_metadata for mdsys.user_sdo_geom_metadata; create or replace public synonym all_sdo_geom_metadata for mdsys.all_sdo_geom_metadata; commit; create or replace trigger sdo_drop_user after drop on DATABASE declare stmt varchar2(200); cnt number; BEGIN if sys.dbms_standard.dictionary_obj_type = 'USER' THEN stmt := 'DELETE FROM SDO_GEOM_METADATA_TABLE ' || ' WHERE SDO_OWNER = :owner '; EXECUTE IMMEDIATE stmt USING sys.dbms_standard.dictionary_obj_name; stmt := 'DELETE FROM SDO_MAPS_TABLE ' || ' WHERE SDO_OWNER = :owner '; EXECUTE IMMEDIATE stmt USING sys.dbms_standard.dictionary_obj_name; stmt := 'DELETE FROM SDO_CACHED_MAPS_TABLE ' || ' WHERE SDO_OWNER = :owner '; EXECUTE IMMEDIATE stmt USING sys.dbms_standard.dictionary_obj_name; stmt := 'DELETE FROM SDO_STYLES_TABLE ' || ' WHERE SDO_OWNER = :owner '; EXECUTE IMMEDIATE stmt USING sys.dbms_standard.dictionary_obj_name; stmt := 'DELETE FROM SDO_THEMES_TABLE ' || ' WHERE SDO_OWNER = :owner '; EXECUTE IMMEDIATE stmt USING sys.dbms_standard.dictionary_obj_name; stmt := 'DELETE FROM SDO_LRS_METADATA_TABLE ' || ' WHERE SDO_OWNER = :owner '; EXECUTE IMMEDIATE stmt USING sys.dbms_standard.dictionary_obj_name; stmt := 'DELETE FROM SDO_TOPO_METADATA_TABLE ' || ' WHERE SDO_OWNER = :owner '; EXECUTE IMMEDIATE stmt USING sys.dbms_standard.dictionary_obj_name; stmt := 'DELETE FROM SDO_ANNOTATION_TEXT_METADATA ' || ' WHERE F_TABLE_SCHEMA = :owner '; EXECUTE IMMEDIATE stmt USING sys.dbms_standard.dictionary_obj_name; end if; end; / show errors; CREATE OR REPLACE TRIGGER SDO_GEOM_TRIG_INS1 INSTEAD OF INSERT ON user_sdo_geom_metadata REFERENCING NEW AS n FOR EACH ROW declare tname varchar2(32); stmt varchar2(2048); vcount INTEGER; dimcount INTEGER; tolerance NUMBER; dimelement MDSYS.SDO_DIM_ELEMENT; idx number; BEGIN EXECUTE IMMEDIATE 'SELECT user FROM dual' into tname; if ( (instr(:n.table_name, ' ') > 0 ) OR (instr(:n.table_name, '''') > 0 ) ) then mderr.raise_md_error('MD', 'SDO', -13199, 'wrong table name: ' || :n.table_name); end if; if ( (instr(:n.column_name, ' ') > 0 ) OR (instr(:n.column_name, '''') > 0 ) ) then mderr.raise_md_error('MD', 'SDO', -13199, 'wrong column name: ' || :n.column_name); end if; stmt := 'SELECT count(*) FROM SDO_GEOM_METADATA_TABLE ' || 'WHERE sdo_owner = :tname AND sdo_table_name = :table_name '|| ' AND sdo_column_name = :column_name '; EXECUTE IMMEDIATE stmt INTO vcount USING upper(tname), upper(:n.table_name), upper(:n.column_name); IF vcount = 0 THEN dimcount := :n.diminfo.count; FOR idx in 1 .. dimcount LOOP dimelement := :n.diminfo(idx); tolerance := dimelement.SDO_TOLERANCE; if ( (tolerance is NULL) OR (tolerance <= 0) ) then mderr.raise_md_error('MD', 'SDO', -13224, :n.table_name||'.'||:n.column_name); end if; END LOOP; INSERT INTO sdo_geom_metadata_table values (tname, upper(:n.table_name), upper(:n.column_name), :n.diminfo, :n.srid); ELSE mderr.raise_md_error('MD', 'SDO', -13223, :n.table_name||'.'||:n.column_name); END IF; END; / show errors; CREATE OR REPLACE TRIGGER SDO_GEOM_TRIG_DEL1 INSTEAD OF DELETE ON user_sdo_geom_metadata REFERENCING OLD AS n FOR EACH ROW declare tname varchar2(32); stmt varchar2(2048); vcount INTEGER; BEGIN EXECUTE IMMEDIATE 'SELECT user FROM dual' into tname; DELETE FROM sdo_geom_metadata_table WHERE SDO_OWNER = tname AND SDO_TABLE_NAME = upper(:n.table_name) AND SDO_COLUMN_NAME = upper(:n.column_name); END; / show errors; CREATE OR REPLACE TRIGGER SDO_GEOM_TRIG_UPD1 INSTEAD OF UPDATE ON user_sdo_geom_metadata REFERENCING OLD AS old NEW AS n FOR EACH ROW declare tname varchar2(32); stmt varchar2(2048); vcount INTEGER; BEGIN EXECUTE IMMEDIATE 'SELECT user FROM dual' into tname; UPDATE sdo_geom_metadata_table SET (SDO_TABLE_NAME, SDO_COLUMN_NAME, SDO_DIMINFO, SDO_SRID) = (SELECT upper(:n.table_name), upper(:n.column_name), :n.diminfo, :n.srid FROM DUAL) WHERE SDO_OWNER = tname AND SDO_TABLE_NAME = upper(:old.table_name) AND SDO_COLUMN_NAME = upper(:old.column_name); END; / show errors; create or replace package SDO_META wrapped a000000 1 abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd 9 231 103 93v7rB4Ui7NchDBA+OFM8S6hDwYwg5Xxf8sVfHTpk/o+eqxGBFTavvQFYBTcQxQO3L61Yrgx L9PDVuSz+XKbxHqM7QZayEwn957hPGVCW2EYL5wrgr1w3wMUDOKeN5DIgCcE5rpr9wnP8cuZ HCWQPbuISBYnidm9dM/qryJpC1/SFUg1eZ4qm9qZyWQJ1k6zTsg8yfVC+sEy9mLfjq9DtFia A3M+g28zx/wu78J9LBZyKjMUIxbgsc0c9R2fxg== / show errors; create or replace package body SDO_META wrapped a000000 1 abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd b 3ac 1b1 e295rj9kk+qmbXKTQRoWi4qvUNcwgw3x2UjWfC/pmAJkdWxaTeVR0cWobx1BLwo/yiTTLQa1 tVFha9sxid8J1xUVvIYSwbS2y09X09Ls13BYhxStNqBDkdJnR/2s7qwnGkQCVFFwFGK3GGtq 8pqOiI+e4hGsUG7s00oHaodQUWklTwxbYjTDcj1l0b57HlRJWJDN9opJVtERdP8quLozIcCU /3tIRb54s5Fr7NcazIfjRWKhLTNCdWlv6Qidj/HpUlrB6hw8uG8ejZwAWDZ3eymfA/zSM/nz DnSXeewzE86S++81So9+oEcJenCj00YO7UwWL/D41VPLJW6sUeDeSR08AdIcIa1Y0cF3p2uI DNQC+ilPTtul6o5BVsM/7IXYxJN4S/lV7f5jRyYZTy6iBOc7u0BhRrPlEDrhLiYFJQ== / show errors; grant execute on SDO_META to public; create or replace function SDO_VERSION wrapped a000000 1 abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd abcd 8 b1 c2 EkpqEbvhrE3NFNCAIWM5nplFga8wg1xKLcvWfHSi2vjVNLNIXIiMy6h/bNO0jXlNdZu1qrNW w/XaKRnaVVPAm2Zfp2NIGjxlDvFkl1PpLl53W2DWgqXV4nNzb3AO6t7xs1n6CHCemORkSsWZ i6greay+uc5YWr23VrujmwP29R+m+9AdFySmUsuclskj0Q8= / grant execute on SDO_VERSION to public; create or replace public synonym SDO_VERSION for mdsys.SDO_VERSION;