Rem Rem $Header: sdo/admin/sdotpmd.sql /main/16 2009/02/18 07:02:36 zzhang Exp $ Rem Rem sdotpmd.sql Rem Rem Copyright (c) 2002, 2009, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem sdotpmd.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem sravada 03/08/06 - bug 5076980 Rem zzhang 05/03/05 - replace sdo_topo_drop_ftbl for ningan Rem to accomodate MDSYS privilege change Rem on DELETE ANY TABLE Rem sravada 07/29/04 - add exception handlers Rem sravada 04/28/04 - add metadata for grid snapping Rem ningan 01/29/04 - fix exception handling in trigger Rem sdo_topo_drop_ftbl Rem sravada 01/29/04 - grant public grant option Rem ningan 01/23/04 - fix bug 2869471 - add trigger Rem sravada 08/12/03 - add hierarchy level Rem sravada 06/10/03 - remove topology type Rem rkothuri 01/02/03 - fix all_topo_info, all_topo_metadata views Rem sravada 11/18/02 - add topology id Rem sravada 10/31/02 - change feature to topo_geometry Rem sravada 07/29/02 - add layer feature type Rem sravada 07/22/02 - add tolerance Rem sravada 07/19/02 - sravada_topo_metadata_views Rem sravada 07/19/02 - Rem sravada 07/17/02 - Created Rem declare begin begin execute immediate 'Create Table SDO_TOPO_METADATA_TABLE ( SDO_OWNER varchar2(32), Topology varchar2(20), Topology_id NUMBER, Topo_Geometry_Layers SDO_Topo_Geometry_Layer_Array, Tolerance NUMBER, SRID NUMBER) '; exception when others then NULL; end; begin execute immediate ' alter table SDO_TOPO_METADATA_TABLE add (digits_right_of_decimal number default 16) '; exception when others then NULL; end; begin execute immediate ' Create index sdo_topo_metadata_idx on SDO_TOPO_METADATA_TABLE (SDO_OWNER, Topology, Topology_id) '; exception when others then NULL; end; end; / Create or replace View user_sdo_topo_info as SELECT SDO_OWNER OWNER, Topology, Topology_id, Tolerance, SRID, b.owner Table_Schema, b.Table_Name Table_Name, b.Column_Name Column_Name, b.Layer_ID TG_Layer_ID, b.Layer_Type TG_Layer_Type, b.Layer_Level TG_Layer_Level, b.Child_Layer_id Child_Layer_id, Digits_Right_Of_Decimal FROM SDO_TOPO_METADATA_TABLE a, TABLE (a.Topo_Geometry_Layers) b WHERE sdo_owner = sys_context('userenv', 'CURRENT_SCHEMA'); Create or replace View all_sdo_topo_info as SELECT SDO_OWNER OWNER, Topology, Topology_id, Tolerance, SRID, b.owner Table_Schema, b.Table_Name Table_Name, b.Column_Name Column_Name, b.Layer_ID TG_Layer_ID, b.Layer_Type TG_Layer_Type, b.Layer_Level TG_Layer_Level, b.Child_Layer_id Child_Layer_id, Digits_Right_Of_Decimal FROM SDO_TOPO_METADATA_TABLE a, TABLE (a.Topo_Geometry_Layers) b WHERE (exists (select table_name from all_tables where table_name=topology || '_NODE$' and owner = sdo_owner union all select view_name from all_views where view_name=topology || '_NODE$' and owner = sdo_owner)); Create or replace View user_sdo_topo_metadata as SELECT SDO_OWNER OWNER, Topology, Topology_id, Tolerance, SRID, b.owner Table_Schema, b.Table_Name Table_Name, b.Column_Name Column_Name, b.Layer_ID TG_Layer_ID, b.Layer_Type TG_Layer_Type, b.Layer_Level TG_Layer_Level, b.Child_Layer_id Child_Layer_id, Topology||'_NODE_S' Node_Sequence, Topology||'_EDGE_S' Edge_Sequence, Topology||'_FACE_S' Face_Sequence, Topology||'_TG_S' TG_Sequence, Digits_Right_Of_Decimal FROM SDO_TOPO_METADATA_TABLE a, TABLE (a.Topo_Geometry_Layers) b WHERE sdo_owner = sys_context('userenv', 'CURRENT_SCHEMA'); Create or replace View all_sdo_topo_metadata as SELECT SDO_OWNER OWNER, Topology, Topology_id, Tolerance, SRID, b.owner Table_owner, b.Table_Name Table_Name, b.Column_Name Column_Name, b.Layer_ID TG_Layer_ID, b.Layer_Type TG_Layer_Type, b.Layer_Level TG_Layer_Level, b.Child_Layer_id Child_Layer_id, Topology||'_NODE_S' Node_Sequence, Topology||'_EDGE_S' Edge_Sequence, Topology||'_FACE_S' Face_Sequence, Topology||'_TG_S' TG_Sequence, Digits_Right_Of_Decimal FROM SDO_TOPO_METADATA_TABLE a, TABLE (a.Topo_Geometry_Layers) b WHERE (exists (select table_name from all_tables where table_name=topology || '_NODE$' and owner = sdo_owner union all select view_name from all_views where view_name=topology || '_NODE$' and owner = sdo_owner)); grant select on user_sdo_topo_metadata to public with grant option; grant select on all_sdo_topo_metadata to public with grant option; create or replace public synonym user_sdo_topo_metadata for mdsys.user_sdo_topo_metadata; create or replace public synonym all_sdo_topo_metadata for mdsys.all_sdo_topo_metadata; grant select on user_sdo_topo_info to public; grant select on all_sdo_topo_info to public; create or replace public synonym user_sdo_topo_info for mdsys.user_sdo_topo_info; create or replace public synonym all_sdo_topo_info for mdsys.all_sdo_topo_info; -- create a trigger to fix bug 2869471. More speically, after a feature -- table has been registered in the topology with add_topo_geometry_layer() -- function call, it can not be dropped until it has been deregistered from -- the topology by calling function delete_topo_geometry_layer(). CREATE OR REPLACE TRIGGER sdo_topo_drop_ftbl BEFORE DROP ON DATABASE DECLARE cnt NUMBER; stmt VARCHAR(500); topology VARCHAR(100); REG_TBL EXCEPTION; BEGIN if(sys.dbms_standard.dictionary_obj_type != 'TABLE') then return; end if; stmt := 'SELECT topology ' || ' FROM SDO_TOPO_METADATA_TABLE a, TABLE(a.Topo_Geometry_Layers) b ' || ' WHERE b.owner = :owner AND b.table_name = :tab ' ; EXECUTE IMMEDIATE stmt into topology USING sys.dbms_standard.DICTIONARY_OBJ_OWNER,sys.dbms_standard.DICTIONARY_OBJ_NAME; RAISE REG_TBL; EXCEPTION WHEN REG_TBL THEN MDSYS.MDERR.raise_md_error( 'MD', 'SDO', -13199, 'Need use delete_topo_geometry_layer() to deregister table ' || sys.dbms_standard.DICTIONARY_OBJ_NAME || ' from topology ' || topology || ' before drop it'); WHEN OTHERS THEN RETURN; END; / SHOW errors;