Rem Rem $Header: sdo/admin/sdonetmd.sql /main/59 2009/08/24 14:07:33 hgong Exp $ Rem Rem sdonetmd.sql Rem Rem Copyright (c) 2002, 2009, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem sdonetmd.sql - Network Manager Metadata Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem hgong 08/19/09 - not to raise exceptions Rem hgong 06/17/09 - change create type to create or replace type Rem hgong 03/24/09 - bug 8343434: use sequence number to fix Rem concurrent writepath problem Rem hgong 12/08/08 - add enclosing and assigned partition_ids for Rem SDO_NET_UPD_HIST_NTBL Rem hgong 12/01/08 - add CATEGORY_ID to user data metadata Rem ningan 11/19/08 - add type SDO_NET_LINK & SDO_NET_LINK_NTBL Rem ningan 11/11/08 - add type SDO_NET_UPD_HIST & SDO_NET_UPD_HIST_NTBL Rem ningan 07/31/08 - consolidate AFTER DROP ON DATABASE triggers Rem ningan 07/24/08 - add sdo_network_timestamps Rem ningan 07/15/08 - lrg-3417773 Rem ningan 05/13/08 - bug-7038166 Rem hgong 02/28/08 - add NODE_LEVEL_TABLE_NAME to network metadata Rem ningan 09/20/07 - bug b6415891: enhance trigger sdo_network_cons_ins_trig Rem ningan 09/19/07 - bug b6415891: enhance trigger sdo_network_cons_drop_trig Rem jcwang 09/11/07 - add network_java_objects views Rem ningan 08/03/07 - add triggers relates to user_sdo_network_histories Rem ningan 08/03/07 - add views user_& all_ sdo_network_histories Rem ningan 08/02/07 - add sdo_network_histories Rem hgong 06/06/07 - add owner to all_sdo_network_user_data Rem jcwang 03/29/07 - bug-5963167 Rem hgong 09/29/06 - add component_table_name column to Rem sdo_network_metadata_table Rem hgong 09/06/06 - add partition_blob_table_name column to Rem sdo_network_metadata_table Rem ningan 06/28/06 - fix lrg-2247076 Rem ningan 04/24/06 - Alter the type of class_name column in Rem sdo_network_constraints table Rem jcwang 04/07/06 - fix metadata upgrade Rem jcwang 02/14/06 - add subpath to metadata Rem ningan 02/05/06 - Add adjusted filters to sdo_network_locks_wm Rem ningan 02/03/06 - Fix sdo_network_locks_wm table defintion Rem ningan 02/01/06 - Add owner to all_sdo_network_constraints Rem ningan 01/27/06 - Add sdo_network_locks_wm table Rem ningan 01/27/06 - Add user_sdo_network_locks_wm view Rem ningan 01/27/06 - Add all_sdo_network_locks_wm view Rem jcwang 11/29/05 - fix upgrade diff Rem jcwang 11/14/05 - use constraint to check user data type Rem jcwang 03/24/05 - add external_references for 11g Rem jcwang 03/21/05 - add user_data_metadata for 11g Rem ningan 03/02/05 - restrict the visibility of Rem all_sdo_network_constraints Rem jcwang 02/21/05 - sql injection bugs Rem ningan 02/15/05 - Change user_sdo_network_constraint to Rem user_sdo_network_constraints and Rem change all_sdo_network_constraint to Rem all_sdo_network_constraints and Rem change sdo_network_constraint to Rem sdo_network_constraints Rem ningan 02/01/05 - add sdo_network_constraint table; Rem add user_sdo_network_constraint and Rem all_sdo_network_constraint views; Rem add related triggers Rem jcwang 12/20/04 - fix update trigger Rem jcwang 12/17/04 - remove bidirected from network level Rem jcwang 10/22/04 - add LINK_PARTITION_COLUMN Rem jcwang 10/14/04 - add topology in metadata Rem jcwang 10/06/04 - add topo_geom_column Rem jcwang 09/29/04 - add duration column Rem sravada 07/29/04 - add exception handlers Rem jcwang 05/27/04 - Fix NETWORK_ID sequence Rem jcwang 05/18/04 - add partitionColumnName Rem jcwang 04/21/04 - fix insert trigger security bug Rem jcwang 04/09/04 - use network_type to inter-network flag in network metadata Rem jcwang 04/06/04 - create sequence for network_id Rem jcwang 12/19/03 - Rem jcwang 12/17/03 - convert all names to upper case Rem jcwang 12/09/03 - add network_id Rem jcwang 09/24/03 - remove insert,delete, and update from Rem all_sdo_network_metadata view Rem jcwang 09/05/03 - make no_of_partition default 1 Rem sravada 09/12/03 - remove SDO_NETWORK_IDX Rem jcwang 08/28/03 - add network_id Rem jcwang 08/26/03 - add external network reference Rem jcwang 06/05/03 - enable function-based costs Rem jcwang 03/17/03 - add path-link table name Rem jcwang 03/11/03 - add node cost, change network_type to network_category, link_type to link_direction Rem jcwang 02/21/03 - change to hierarchy_level Rem jcwang 02/17/03 - add metadata constraints Rem jcwang 01/24/03 - formatting Rem jcwang 01/10/03 - add partition information Rem sravada 12/18/02 - Rem jcwang 11/20/02 - network test Rem jcwang 10/25/02 - Add Network Hierarchical Structure Rem jcwang 10/16/02 - Add LRS Geometry Option, Hierarchy, and Path table Rem jcwang 10/04/02 - add to catmd10i Rem jcwang 09/30/02 - jcwang_network_admin Rem jcwang 09/19/02 - Created Rem -- -- sdo network manager (sdo_net) metadata table -- -- drop the network metadata table if exists -- don't drop as we lose data if upgrading from version to version -- drop constraints -- create the NDM network metadata table -- NETWORK_CATEGORY: 'LOGICAL' OR 'SPATIAL' -- NETWORK_TYPE : 'SIMPLE' OR 'COMPLEX' -- LINK_DIRECTION : 'DIRECTED', or 'UNDIRECTED' -- NO_OF_HIERARCHY_LEVELS: Number of Hierarchy Levels in the network -- GEOM_TABLE_NAME(NETWORK_GEOM_COLUMN) represents referenced LRS geometry layer -- for node and link geometry -- -- default table/columns names: -- node table : _node$ -- link table : _link$ -- path table : _path$ -- path link table : _plink$ (path_id, link_id) -- node geom. column : geometry -- node lrs geom column : (geom_id and measure) -- node topo geom column : topo_geometry -- link geom. column : geometry -- link lrs geom column : (geom_id, start_measure, and end_measure) -- link topo geom column : topo_geometry -- path geom. column : geometry -- node cost column : cost -- link cost column : cost -- path cost column : cost -- subPath table :_spath$ declare begin begin execute immediate ' CREATE TABLE SDO_NETWORK_METADATA_TABLE ( SDO_OWNER varchar2(32) DEFAULT sys_context(''userenv'',''CURRENT_SCHEMA'') NOT NULL, NETWORK varchar2(24) NOT NULL, NETWORK_ID number NOT NULL, NETWORK_CATEGORY varchar2(12) NOT NULL CHECK (NETWORK_CATEGORY IN (''SPATIAL'',''LOGICAL'')), GEOMETRY_TYPE varchar2(24) CHECK (GEOMETRY_TYPE IN (NULL,''SDO_GEOMETRY'',''LRS_GEOMETRY'', ''TOPO_GEOMETRY'')), NETWORK_TYPE varchar2(24), NO_OF_HIERARCHY_LEVELS number default 1, NO_OF_PARTITIONS number default 1, LRS_TABLE_NAME varchar2(32), LRS_GEOM_COLUMN varchar2(32), NODE_TABLE_NAME varchar2(32) NOT NULL, NODE_GEOM_COLUMN varchar2(32), NODE_COST_COLUMN varchar2(1024), LINK_TABLE_NAME varchar2(32) NOT NULL, LINK_GEOM_COLUMN varchar2(32), LINK_DIRECTION varchar2(12) NOT NULL CHECK (LINK_DIRECTION IN (''UNDIRECTED'',''BIDIRECTED'',''DIRECTED'')), LINK_COST_COLUMN varchar2(1024), PATH_TABLE_NAME varchar2(32), PATH_GEOM_COLUMN varchar2(32), PATH_LINK_TABLE_NAME varchar2(32), PARTITION_TABLE_NAME varchar2(32), CONSTRAINT unique_network_layer UNIQUE (SDO_OWNER, NETWORK), CONSTRAINT unique_network_node_layer UNIQUE (SDO_OWNER, NETWORK, NODE_TABLE_NAME), CONSTRAINT unique_network_link_layer UNIQUE (SDO_OWNER, NETWORK, LINK_TABLE_NAME), CONSTRAINT unique_lrs_geom_layer UNIQUE (SDO_OWNER, NETWORK, LRS_TABLE_NAME), CONSTRAINT unique_network_path_layer UNIQUE (SDO_OWNER, NETWORK, PATH_TABLE_NAME), CONSTRAINT unique_network_path_link_layer UNIQUE (SDO_OWNER, NETWORK, PATH_LINK_TABLE_NAME)) '; exception when others then NULL; end; end; / -- drop all network views to avoid view-trigger dependency declare begin execute immediate 'drop view user_sdo_network_metadata'; execute immediate 'drop view all_sdo_network_metadata' ; exception when others then NULL; end; / -- add new columns in the base metadata table -- node partition column -- link partition column -- node duration column -- link duration column -- topology -- link partition column -- subpath table name -- subpath geom column declare stmt varchar2(512); begin begin stmt := 'ALTER TABLE SDO_NETWORK_METADATA_TABLE ADD ' || '( NODE_PARTITION_COLUMN varchar2(32),' || ' LINK_PARTITION_COLUMN varchar2(32),' || ' NODE_DURATION_COLUMN varchar2(32),' || ' LINK_DURATION_COLUMN varchar2(32),' || ' TOPOLOGY varchar2(32),' || ' USER_DEFINED_DATA varchar2(1),' || ' EXTERNAL_REFERENCES varchar2(1))' ; execute immediate stmt; exception when others then NULL; end; end; / -- -- add sub path metadata information -- declare stmt varchar2(512); begin begin stmt := 'ALTER TABLE SDO_NETWORK_METADATA_TABLE ADD ' || '( SUBPATH_TABLE_NAME varchar2(32),' || ' SUBPATH_GEOM_COLUMN varchar2(32))'; execute immediate stmt; exception when others then NULL; end; end; / -- -- add partition blob table name -- declare stmt varchar2(512); begin begin stmt := 'ALTER TABLE SDO_NETWORK_METADATA_TABLE ADD ' || '( PARTITION_BLOB_TABLE_NAME varchar2(32),' || ' COMPONENT_TABLE_NAME varchar2(32))'; execute immediate stmt; exception when others then NULL; end; end; / -- -- add max node level table name -- declare stmt varchar2(512); begin begin stmt := 'ALTER TABLE SDO_NETWORK_METADATA_TABLE ADD ' || '( NODE_LEVEL_TABLE_NAME varchar2(32))'; execute immediate stmt; exception when others then NULL; end; end; / -- catch drop exception -- create network ID sequence declare begin begin execute immediate 'CREATE SEQUENCE SDO_NDM_NETWORK_ID_SEQ START WITH 1 INCREMENT BY 1 '; exception when others then NULL; end; end; / -- advance SDO_NDM_NETWORK_ID_SEQ DECLARE max_net_id number; id number; BEGIN EXECUTE IMMEDIATE 'SELECT MAX(NETWORK_ID) FROM SDO_NETWORK_METADATA_TABLE' into max_net_id; IF ( max_net_id IS NOT NULL ) THEN FOR i in 1..max_net_id LOOP EXECUTE IMMEDIATE 'SELECT sdo_ndm_network_id_seq.nextval FROM DUAL' into id; END LOOP; END IF; END; / SHOW ERRORS; -- create NDM ID sequence to be used for any stored paths, -- subpaths or network buffers declare begin execute immediate 'CREATE SEQUENCE SDO_NDM_ID_SEQ START WITH 1 INCREMENT BY 1 NOCACHE'; exception when others then NULL; end; / --grant select on SDO_NDM_ID_SEQ to all users GRANT SELECT ON SDO_NDM_ID_SEQ TO PUBLIC; -- crerate user_sdo_network_metadata -- DROP VIEW USER_SDO_NETWORK_METADATA; CREATE OR REPLACE VIEW USER_SDO_NETWORK_METADATA AS SELECT NETWORK, NETWORK_ID, NETWORK_CATEGORY, GEOMETRY_TYPE, NETWORK_TYPE, NO_OF_HIERARCHY_LEVELS, NO_OF_PARTITIONS, LRS_TABLE_NAME, LRS_GEOM_COLUMN, NODE_TABLE_NAME, NODE_GEOM_COLUMN, NODE_COST_COLUMN, NODE_PARTITION_COLUMN, NODE_DURATION_COLUMN, LINK_TABLE_NAME, LINK_GEOM_COLUMN, LINK_DIRECTION, LINK_COST_COLUMN, LINK_PARTITION_COLUMN, LINK_DURATION_COLUMN, PATH_TABLE_NAME, PATH_GEOM_COLUMN, PATH_LINK_TABLE_NAME, SUBPATH_TABLE_NAME, SUBPATH_GEOM_COLUMN, PARTITION_TABLE_NAME, PARTITION_BLOB_TABLE_NAME, COMPONENT_TABLE_NAME, NODE_LEVEL_TABLE_NAME, TOPOLOGY, USER_DEFINED_DATA, EXTERNAL_REFERENCES FROM SDO_NETWORK_METADATA_TABLE WHERE SDO_OWNER = sys_context('userenv','CURRENT_SCHEMA'); -- DROP VIEW ALL_SDO_NETWORK_METADATA; CREATE OR REPLACE VIEW ALL_SDO_NETWORK_METADATA AS SELECT SDO_OWNER OWNER, NETWORK, NETWORK_ID, NETWORK_CATEGORY, GEOMETRY_TYPE, NETWORK_TYPE, NO_OF_HIERARCHY_LEVELS, NO_OF_PARTITIONS, LRS_TABLE_NAME, LRS_GEOM_COLUMN, NODE_TABLE_NAME, NODE_GEOM_COLUMN, NODE_COST_COLUMN, NODE_PARTITION_COLUMN, NODE_DURATION_COLUMN, LINK_TABLE_NAME, LINK_GEOM_COLUMN, LINK_DIRECTION, LINK_COST_COLUMN, LINK_PARTITION_COLUMN, LINK_DURATION_COLUMN, PATH_TABLE_NAME, PATH_GEOM_COLUMN, PATH_LINK_TABLE_NAME, SUBPATH_TABLE_NAME, SUBPATH_GEOM_COLUMN, PARTITION_TABLE_NAME, PARTITION_BLOB_TABLE_NAME, COMPONENT_TABLE_NAME, NODE_LEVEL_TABLE_NAME, TOPOLOGY, USER_DEFINED_DATA, EXTERNAL_REFERENCES FROM SDO_NETWORK_METADATA_TABLE; -- grant priviledges to the views GRANT SELECT, INSERT, DELETE, UPDATE ON USER_SDO_NETWORK_METADATA TO PUBLIC; GRANT SELECT ON ALL_SDO_NETWORK_METADATA To PUBLIC; -- drop public synonyms for the views -- DROP PUBLIC SYNONYM USER_SDO_NETWORK_METADATA; -- DROP PUBLIC SYNONYM ALL_SDO_NETWORK_METADATA; -- create public synynoms CREATE OR REPLACE PUBLIC SYNONYM USER_SDO_NETWORK_METADATA FOR MDSYS.USER_SDO_NETWORK_METADATA; CREATE OR REPLACE PUBLIC SYNONYM ALL_SDO_NETWORK_METADATA FOR MDSYS.ALL_SDO_NETWORK_METADATA; COMMIT; --DROP TRIGGER SDO_NETWORK_DROP_USER; -- trigger for droping user CREATE OR REPLACE TRIGGER SDO_NETWORK_DROP_USER AFTER DROP ON DATABASE DECLARE stmt VARCHAR2(256); BEGIN IF sys.dbms_standard.dictionary_obj_type = 'USER' THEN stmt := 'DELETE FROM SDO_NETWORK_METADATA_TABLE WHERE SDO_OWNER = :name'; EXECUTE IMMEDIATE stmt using NLS_UPPER(sys.dbms_standard.dictionary_obj_name); stmt := 'DELETE FROM SDO_NETWORK_CONSTRAINTS WHERE SDO_OWNER = :name'; EXECUTE IMMEDIATE stmt using NLS_UPPER(sys.dbms_standard.dictionary_obj_name); stmt := 'DELETE FROM SDO_NETWORK_LOCKS_WM WHERE SDO_OWNER = :name'; EXECUTE IMMEDIATE stmt using NLS_UPPER(sys.dbms_standard.dictionary_obj_name); stmt := 'DELETE FROM SDO_NETWORK_USER_DATA WHERE SDO_OWNER = :name'; EXECUTE IMMEDIATE stmt using NLS_UPPER(sys.dbms_standard.dictionary_obj_name); --lrg-3417773 stmt := 'DELETE FROM SDO_NETWORK_HISTORIES WHERE OWNER = :name'; EXECUTE IMMEDIATE stmt using NLS_UPPER(sys.dbms_standard.dictionary_obj_name); stmt := 'DELETE FROM SDO_NETWORK_TIMESTAMPS WHERE OWNER = :name'; EXECUTE IMMEDIATE stmt using NLS_UPPER(sys.dbms_standard.dictionary_obj_name); END IF; END ; / SHOW ERRORS; -- trigger for inserting, deleting, and updating graph metadata views CREATE OR REPLACE TRIGGER SDO_NETWORK_TRIG_INS INSTEAD OF INSERT ON USER_SDO_NETWORK_METADATA REFERENCING NEW AS n FOR EACH ROW declare user_name varchar2(32); net_id number; no_of_partitions number := 0; no_of_hierarchy_levels number := 1; direction VARCHAR2(12); BEGIN EXECUTE IMMEDIATE 'SELECT user FROM dual' into user_name; -- find out the network ID from network ID sequence EXECUTE IMMEDIATE 'SELECT sdo_ndm_network_id_seq.nextval FROM DUAL' INTO net_id; -- insert network metadata and network ID into SDO_NETWORK_METADATA_TABLE IF ( :n.no_of_partitions is NOT null ) THEN no_of_partitions := :n.no_of_partitions; END IF; IF ( :n.no_of_hierarchy_levels is NOT null ) THEN no_of_hierarchy_levels := :n.no_of_hierarchy_levels; END IF; -- change bidirected to undirected direction := :n.link_direction ; IF ( NLS_UPPER(direction) = 'BIDIRECTED' ) THEN direction := 'UNDIRECTED'; END IF ; insert into SDO_NETWORK_METADATA_TABLE ( SDO_OWNER, NETWORK, NETWORK_ID, NETWORK_CATEGORY, GEOMETRY_TYPE, NETWORK_TYPE, NO_OF_HIERARCHY_LEVELS, NO_OF_PARTITIONS, LRS_TABLE_NAME, LRS_GEOM_COLUMN, NODE_TABLE_NAME, NODE_GEOM_COLUMN, NODE_COST_COLUMN, NODE_PARTITION_COLUMN, NODE_DURATION_COLUMN, LINK_TABLE_NAME, LINK_GEOM_COLUMN, LINK_DIRECTION, LINK_COST_COLUMN, LINK_PARTITION_COLUMN, LINK_DURATION_COLUMN, PATH_TABLE_NAME, PATH_GEOM_COLUMN, PATH_LINK_TABLE_NAME, SUBPATH_TABLE_NAME, SUBPATH_GEOM_COLUMN, PARTITION_TABLE_NAME, PARTITION_BLOB_TABLE_NAME, COMPONENT_TABLE_NAME, NODE_LEVEL_TABLE_NAME, TOPOLOGY, USER_DEFINED_DATA, EXTERNAL_REFERENCES ) values ( NLS_UPPER(user_name), NLS_UPPER(:n.network), net_id, NLS_UPPER(:n.network_category), NLS_UPPER(:n.geometry_type), NLS_UPPER(:n.network_type), no_of_hierarchy_levels, no_of_partitions, NLS_UPPER(:n.lrs_table_name), NLS_UPPER(:n.lrs_geom_column), NLS_UPPER(:n.node_table_name), NLS_UPPER(:n.node_geom_column), NLS_UPPER(:n.node_cost_column), NLS_UPPER(:n.node_partition_column), NLS_UPPER(:n.node_duration_column), NLS_UPPER(:n.link_table_name), NLS_UPPER(:n.link_geom_column), NLS_UPPER(direction), NLS_UPPER(:n.link_cost_column), NLS_UPPER(:n.link_partition_column), NLS_UPPER(:n.link_duration_column), NLS_UPPER(:n.path_table_name), NLS_UPPER(:n.path_geom_column), NLS_UPPER(:n.path_link_table_name), NLS_UPPER(:n.subpath_table_name), NLS_UPPER(:n.subpath_geom_column), NLS_UPPER(:n.partition_table_name), NLS_UPPER(:n.partition_blob_table_name), NLS_UPPER(:n.component_table_name), NLS_UPPER(:n.node_level_table_name), NLS_UPPER(:n.topology), NLS_UPPER(:n.user_defined_data), NLS_UPPER(:n.external_references) ); END; / SHOW ERRORS; CREATE OR REPLACE TRIGGER SDO_NETWORK_TRIG_DEL INSTEAD OF DELETE ON user_sdo_network_metadata REFERENCING OLD AS o FOR EACH ROW DECLARE user_name VARCHAR2(256); BEGIN EXECUTE IMMEDIATE 'SELECT USER FROM DUAL' INTO user_name; DELETE FROM SDO_NETWORK_METADATA_TABLE WHERE NLS_UPPER(SDO_OWNER) = NLS_UPPER(user_name) AND NLS_UPPER(network) = NLS_UPPER(:o.network); END; / SHOW ERRORS; CREATE OR REPLACE TRIGGER SDO_NETWORK_TRIG_UPD INSTEAD OF UPDATE ON user_sdo_network_metadata REFERENCING OLD AS o NEW AS n FOR EACH ROW DECLARE user_name VARCHAR2(256); direction VARCHAR2(12); BEGIN EXECUTE IMMEDIATE 'SELECT USER FROM DUAL' INTO user_name; direction := :n.link_direction ; IF ( UPPER(direction) = 'BIDIRECTED' ) THEN direction := 'UNDIRECTED'; END IF; UPDATE sdo_network_metadata_table SET (NETWORK, NETWORK_CATEGORY, GEOMETRY_TYPE, NETWORK_TYPE, NO_OF_HIERARCHY_LEVELS, NO_OF_PARTITIONS, LRS_TABLE_NAME, LRS_GEOM_COLUMN, NODE_TABLE_NAME, NODE_GEOM_COLUMN, NODE_COST_COLUMN, NODE_PARTITION_COLUMN, NODE_DURATION_COLUMN, LINK_TABLE_NAME, LINK_GEOM_COLUMN, LINK_DIRECTION, LINK_COST_COLUMN, LINK_PARTITION_COLUMN, LINK_DURATION_COLUMN, PATH_TABLE_NAME, PATH_GEOM_COLUMN, PATH_LINK_TABLE_NAME, SUBPATH_TABLE_NAME, SUBPATH_GEOM_COLUMN, PARTITION_TABLE_NAME, PARTITION_BLOB_TABLE_NAME, COMPONENT_TABLE_NAME, NODE_LEVEL_TABLE_NAME, TOPOLOGY, USER_DEFINED_DATA, EXTERNAL_REFERENCES ) = ( SELECT NLS_UPPER(:n.network), NLS_UPPER(:n.network_category), NLS_UPPER(:n.geometry_type), NLS_UPPER(:n.network_type), :n.no_of_hierarchy_levels, :n.no_of_partitions, NLS_UPPER(:n.lrs_table_name), NLS_UPPER(:n.lrs_geom_column), NLS_UPPER(:n.node_table_name), NLS_UPPER(:n.node_geom_column), NLS_UPPER(:n.node_cost_column), NLS_UPPER(:n.node_partition_column), NLS_UPPER(:n.node_duration_column), NLS_UPPER(:n.link_table_name), NLS_UPPER(:n.link_geom_column), NLS_UPPER(direction), NLS_UPPER(:n.link_cost_column), NLS_UPPER(:n.link_partition_column), NLS_UPPER(:n.link_duration_column), NLS_UPPER(:n.path_table_name), NLS_UPPER(:n.path_geom_column), NLS_UPPER(:n.path_link_table_name), NLS_UPPER(:n.subpath_table_name), NLS_UPPER(:n.subpath_geom_column), NLS_UPPER(:n.partition_table_name), NLS_UPPER(:n.partition_blob_table_name), NLS_UPPER(:n.component_table_name), NLS_UPPER(:n.node_level_table_name), NLS_UPPER(:n.topology), NLS_UPPER(:n.user_defined_data), NLS_UPPER(:n.external_references) FROM DUAL ) WHERE NLS_UPPER(SDO_OWNER) = NLS_UPPER(user_name) AND NLS_UPPER(NETWORK) = NLS_UPPER(:o.network); END; / SHOW ERRORS; ------------------------------------------------------------------------------- -- SDO_NETWORK_CONSTRAINTS ------------------------------------------------------------------------------- DECLARE BEGIN EXECUTE IMMEDIATE 'CREATE TABLE SDO_NETWORK_CONSTRAINTS( sdo_owner VARCHAR2(32) DEFAULT SYS_CONTEXT(''USERENV'', ''CURRENT_SCHEMA'') NOT NULL, constraint VARCHAR2(32) NOT NULL, description VARCHAR2(200), class_name VARCHAR2(32), class BLOB, CONSTRAINT unique_user_constraint UNIQUE(SDO_OWNER, CONSTRAINT)) '; EXCEPTION WHEN others THEN NULL; END; / SHOW ERRORS; -- change type of class_name to be complaint with column NAME of all_java_classes DECLARE BEGIN EXECUTE IMMEDIATE 'ALTER TABLE SDO_NETWORK_CONSTRAINTS MODIFY (class_name VARCHAR2(4000))'; EXCEPTION WHEN OTHERS THEN NULL; END; / SHOW ERRORS; -- add java_interface for storing all network java classes including network constraints DECLARE BEGIN EXECUTE IMMEDIATE 'ALTER TABLE SDO_NETWORK_CONSTRAINTS ADD (java_interface VARCHAR2(4000))'; EXCEPTION WHEN OTHERS THEN NULL; END; / SHOW ERRORS; -- add unique constraint BEGIN EXECUTE IMMEDIATE 'ALTER TABLE SDO_NETWORK_CONSTRAINTS ADD (CONSTRAINT ' || 'unique_class_constraint UNIQUE(SDO_OWNER, CLASS_NAME))'; EXCEPTION WHEN OTHERS THEN NULL; END; / SHOW ERRORS; -- create indices on base table to boost the performance of metadata lookup DECLARE BEGIN EXECUTE IMMEDIATE 'DROP INDEX sdo_net_cons_owner'; EXCEPTION WHEN OTHERS THEN NULL; END; / SHOW ERRORS; DECLARE BEGIN EXECUTE IMMEDIATE 'DROP INDEX sdo_net_cons_cons'; EXCEPTION WHEN OTHERS THEN NULL; END; / SHOW ERRORS; DECLARE BEGIN EXECUTE IMMEDIATE 'DROP INDEX sdo_net_cons_class'; EXCEPTION WHEN OTHERS THEN NULL; END; / SHOW ERRORS; DECLARE BEGIN EXECUTE IMMEDIATE 'CREATE INDEX sdo_net_cons_owner ON sdo_network_constraints(sdo_owner)'; EXECUTE IMMEDIATE 'CREATE INDEX sdo_net_cons_cons ON sdo_network_constraints(constraint)'; EXECUTE IMMEDIATE 'CREATE INDEX sdo_net_cons_class ON sdo_network_constraints(class_name)'; EXCEPTION WHEN OTHERS THEN NULL; END; / SHOW ERRORS; -- drop views to avoid view-trigger dependency DECLARE BEGIN EXECUTE IMMEDIATE 'DROP VIEW user_sdo_network_constraints'; EXECUTE IMMEDIATE 'DROP VIEW all_sdo_network_constraints'; EXCEPTION WHEN OTHERS THEN NULL; END; / SHOW ERRORS; -- view creations CREATE OR REPLACE VIEW user_sdo_network_constraints AS SELECT constraint, description, class_name, class FROM sdo_network_constraints WHERE sdo_owner = sys_context('USERENV', 'CURRENT_SCHEMA'); CREATE OR REPLACE VIEW all_sdo_network_constraints AS SELECT sdo_owner owner, constraint, description, class_name FROM sdo_network_constraints; -- change definition of all_sdo_network_constraints DECLARE BEGIN EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW all_sdo_network_constraints AS ' || ' SELECT sdo_owner owner, constraint, description, class_name, class ' || ' FROM sdo_network_constraints '|| ' WHERE EXISTS ' || ' (SELECT NULL' || ' FROM all_java_classes ' || ' WHERE owner = sdo_owner ' || ' AND name = class_name )'; EXCEPTION WHEN OTHERS THEN RAISE; END; / SHOW ERRORS; -- granting view priviledges GRANT SELECT, INSERT, DELETE, UPDATE ON user_sdo_network_constraints TO PUBLIC; GRANT SELECT ON all_sdo_network_constraints To PUBLIC; -- creating public synonyms CREATE OR REPLACE PUBLIC SYNONYM user_sdo_network_constraints FOR mdsys.user_sdo_network_constraints; CREATE OR REPLACE PUBLIC SYNONYM all_sdo_network_constraints FOR mdsys.all_sdo_network_constraints; -- trigger for insertion on user_sdo_network_constraints -- note: dba_java_classes instead of all_java_classes is -- needed here because the invoker might not grant -- necessary privileges on his/her Java Class objects -- to mdsys. CREATE OR REPLACE TRIGGER sdo_network_cons_ins_trig INSTEAD OF INSERT ON user_sdo_network_constraints REFERENCING NEW AS n FOR EACH ROW DECLARE err_msg VARCHAR2(200); num NUMBER := -1; stmt VARCHAR2(200); BEGIN stmt := 'select count(*) from dba_java_classes where owner = :owner '|| ' and name = :name'; execute immediate stmt into num using user, :n.class_name; if (num<>1) then err_msg := 'Could not find Java class schema object: ' || :n.class_name || '. Did not load Java class properly.'; mdsys.mderr.raise_md_error('MD', 'SDO','13385', err_msg); else stmt := 'insert into sdo_network_constraints(sdo_owner, constraint, ' || 'description, class_name, class) values (:owner, :constraint, '|| ':description, :class_name, :class)'; execute immediate stmt using NLS_UPPER(user),:n.constraint, :n.description, :n.class_name,:n.class; end if; EXCEPTION when others then raise; END sdo_network_cons_ins_trig; / SHOW ERRORS; -- trigger for deletion on user_sdo_network_constraints CREATE OR REPLACE TRIGGER sdo_network_cons_del_trig INSTEAD OF DELETE ON user_sdo_network_constraints FOR EACH ROW DECLARE err_msg VARCHAR2(200); num NUMBER := -1; stmt VARCHAR2(200); BEGIN stmt := 'select count(*) from dba_java_classes where owner = :owner '|| ' and name = :name'; execute immediate stmt into num using user, :old.class_name; if (num=1) then err_msg := 'Java class schema object: '||:old.class_name||' exists. Please '|| 'drop it first.'; mdsys.mderr.raise_md_error('MD', 'SDO', '13385', err_msg); else stmt := 'delete from sdo_network_constraints where sdo_owner = :owner '|| ' and class_name = :name'; execute immediate stmt using user, :old.class_name; end if; EXCEPTION when others then raise; END sdo_network_cons_del_trig; / SHOW ERRORS; -- trigger for update on user_sdo_network_constraints CREATE OR REPLACE TRIGGER sdo_network_cons_upd_trig INSTEAD OF UPDATE ON user_sdo_network_constraints REFERENCING OLD AS o NEW AS n FOR EACH ROW DECLARE user_name VARCHAR2(256); BEGIN EXECUTE IMMEDIATE 'SELECT USER FROM DUAL' INTO user_name; UPDATE sdo_network_constraints SET (constraint, description, class_name, class) = (SELECT :n.constraint, :n.description, :n.class_name, :n.class FROM DUAL) WHERE NLS_UPPER(sdo_owner) = NLS_UPPER(user_name) AND constraint = :o.constraint; END; / SHOW ERRORS; -- drop views to avoid view-trigger dependency DECLARE BEGIN EXECUTE IMMEDIATE 'DROP VIEW user_sdo_network_java_objects'; EXECUTE IMMEDIATE 'DROP VIEW all_sdo_network_java_objects'; EXCEPTION WHEN OTHERS THEN NULL; END; / SHOW ERRORS; -- view creations CREATE OR REPLACE VIEW user_sdo_network_java_objects AS SELECT constraint name, description, class_name, class,java_interface FROM sdo_network_constraints WHERE sdo_owner = sys_context('USERENV', 'CURRENT_SCHEMA'); DECLARE BEGIN EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW all_sdo_network_java_objects AS ' || ' SELECT sdo_owner owner, constraint name, description, class_name, class,java_interface ' || ' FROM sdo_network_constraints '|| ' WHERE EXISTS ' || ' (SELECT NULL' || ' FROM all_java_classes ' || ' WHERE owner = sdo_owner ' || ' AND name = class_name )'; EXCEPTION WHEN OTHERS THEN RAISE; END; / SHOW ERRORS; -- granting view priviledges GRANT SELECT, INSERT, DELETE, UPDATE ON user_sdo_network_java_objects TO PUBLIC; GRANT SELECT ON all_sdo_network_java_objects To PUBLIC; -- creating public synonyms CREATE OR REPLACE PUBLIC SYNONYM user_sdo_network_java_objects FOR mdsys.user_sdo_network_java_objects; CREATE OR REPLACE PUBLIC SYNONYM all_sdo_network_java_objects FOR mdsys.all_sdo_network_java_objects; -- trigger for insertion on user_sdo_network_java_objects CREATE OR REPLACE TRIGGER sdo_network_java_ins_trig INSTEAD OF INSERT ON user_sdo_network_java_objects REFERENCING NEW AS n FOR EACH ROW DECLARE user_name VARCHAR2(32); dir_name VARCHAR2(200); file_name VARCHAR2(32); class_file_name VARCHAR2(32); BEGIN EXECUTE IMMEDIATE 'SELECT user FROM dual' INTO user_name; INSERT INTO sdo_network_constraints (sdo_owner, constraint, description, class_name, class, java_interface) VALUES ( NLS_UPPER(user_name), :n.name, :n.description, :n.class_name, :n.class, :n.java_interface ); END; / SHOW ERRORS; -- trigger for deletion on user_sdo_network_java_objects CREATE OR REPLACE TRIGGER sdo_network_java_del_trig INSTEAD OF DELETE ON user_sdo_network_java_objects REFERENCING OLD AS o FOR EACH ROW DECLARE user_name VARCHAR2(256); BEGIN EXECUTE IMMEDIATE 'SELECT USER FROM DUAL' INTO user_name; DELETE FROM sdo_network_constraints WHERE NLS_UPPER(SDO_OWNER) = NLS_UPPER(user_name) AND constraint = :o.name; END; / SHOW ERRORS; -- trigger for update on user_sdo_network_java_objects CREATE OR REPLACE TRIGGER sdo_network_java_upd_trig INSTEAD OF UPDATE ON user_sdo_network_java_objects REFERENCING OLD AS o NEW AS n FOR EACH ROW DECLARE user_name VARCHAR2(256); BEGIN EXECUTE IMMEDIATE 'SELECT USER FROM DUAL' INTO user_name; UPDATE sdo_network_constraints SET (constraint, description, class_name, class, java_interface) = (SELECT :n.name, :n.description, :n.class_name, :n.class, :n.java_interface FROM DUAL) WHERE NLS_UPPER(sdo_owner) = NLS_UPPER(user_name) AND constraint = :o.name; END; / SHOW ERRORS; ------------------------------------------------------------------------------- -- SDO_NETWORK_LOCKS_WM ------------------------------------------------------------------------------- -- base table creation DECLARE BEGIN EXECUTE IMMEDIATE 'CREATE TABLE SDO_NETWORK_LOCKS_WM( sdo_owner VARCHAR2(32) DEFAULT SYS_CONTEXT(''USERENV'', ''CURRENT_SCHEMA'') NOT NULL, lock_id NUMBER NOT NULL, network VARCHAR2(24), workspace VARCHAR2(32), original_node_filter VARCHAR2(200), original_link_filter VARCHAR2(200), original_path_filter VARCHAR2(200), adjusted_node_filter VARCHAR2(200), adjusted_link_filter VARCHAR2(200), adjusted_path_filter VARCHAR2(200), CONSTRAINT unique_ndm_locks UNIQUE(SDO_OWNER, lock_id)) '; EXCEPTION WHEN others THEN NULL; END; / SHOW ERRORS; -- drop views to avoid view-trigger dependency DECLARE BEGIN EXECUTE IMMEDIATE 'DROP VIEW user_sdo_network_locks_wm'; EXECUTE IMMEDIATE 'DROP VIEW all_sdo_network_locks_wm'; EXCEPTION WHEN OTHERS THEN NULL; END; / SHOW ERRORS; -- view creations CREATE OR REPLACE VIEW user_sdo_network_locks_wm AS SELECT lock_id, network, workspace, original_node_filter, original_link_filter, original_path_filter, adjusted_node_filter, adjusted_link_filter, adjusted_path_filter FROM sdo_network_locks_wm WHERE sdo_owner = sys_context('USERENV', 'CURRENT_SCHEMA'); CREATE OR REPLACE VIEW all_sdo_network_locks_wm AS SELECT sdo_owner owner, lock_id, network, workspace, original_node_filter, original_link_filter, original_path_filter, adjusted_node_filter, adjusted_link_filter, adjusted_path_filter FROM sdo_network_locks_wm; -- granting view priviledges GRANT SELECT, INSERT, DELETE, UPDATE ON user_sdo_network_locks_wm TO PUBLIC; GRANT SELECT ON all_sdo_network_locks_wm To PUBLIC; -- creating public synonyms CREATE OR REPLACE PUBLIC SYNONYM user_sdo_network_locks_wm FOR mdsys.user_sdo_network_locks_wm; CREATE OR REPLACE PUBLIC SYNONYM all_sdo_network_locks_wm FOR mdsys.all_sdo_network_locks_wm; -- trigger for insertion on user_sdo_network_locks_wm CREATE OR REPLACE TRIGGER sdo_network_locks_ins_trig INSTEAD OF INSERT ON user_sdo_network_locks_wm REFERENCING NEW AS n FOR EACH ROW DECLARE user_name VARCHAR2(32); BEGIN EXECUTE IMMEDIATE 'SELECT user FROM dual' INTO user_name; INSERT INTO sdo_network_locks_wm(sdo_owner, lock_id, network, workspace, original_node_filter, original_link_filter, original_path_filter, adjusted_node_filter, adjusted_link_filter, adjusted_path_filter) VALUES (NLS_UPPER(user_name), :n.lock_id, :n.network, :n.workspace, :n.original_node_filter,:n.original_link_filter,:n.original_path_filter, :n.adjusted_node_filter,:n.adjusted_link_filter,:n.adjusted_path_filter); END; / SHOW ERRORS; -- trigger for deletion on user_sdo_network_locks_wm CREATE OR REPLACE TRIGGER sdo_network_locks_del_trig INSTEAD OF DELETE ON user_sdo_network_locks_wm REFERENCING OLD AS o FOR EACH ROW DECLARE user_name VARCHAR2(256); BEGIN EXECUTE IMMEDIATE 'SELECT USER FROM DUAL' INTO user_name; DELETE FROM sdo_network_locks_wm WHERE NLS_UPPER(SDO_OWNER) = NLS_UPPER(user_name) AND lock_id = :o.lock_id; END; / SHOW ERRORS; -- trigger for update on user_sdo_network_locks_wm CREATE OR REPLACE TRIGGER sdo_network_locks_upd_trig INSTEAD OF UPDATE ON user_sdo_network_locks_wm REFERENCING OLD AS o NEW AS n FOR EACH ROW DECLARE user_name VARCHAR2(256); BEGIN EXECUTE IMMEDIATE 'SELECT USER FROM DUAL' INTO user_name; UPDATE sdo_network_locks_wm SET (lock_id, network, workspace, original_node_filter, original_link_filter, original_path_filter, adjusted_node_filter, adjusted_link_filter, adjusted_path_filter) = (SELECT :n.lock_id, :n.network, :n.workspace, :n.original_node_filter, :n.original_link_filter, :n.original_path_filter, :n.adjusted_node_filter, :n.adjusted_link_filter, :n.adjusted_path_filter FROM DUAL) WHERE NLS_UPPER(sdo_owner) = NLS_UPPER(user_name) AND lock_id = :o.lock_id; END; / SHOW ERRORS; ----------------------------- -- Network User Data Metadata ----------------------------- -- -- this metadata provides user defined data mapping for NDM -- use users register their user defined data, NDM Java API will manage -- user data as well as connectivity data -- -- schema definition -- owner: owner of the user data -- network: target network -- table_type: user can defined their own user data on node,link, and path table -- data_name: name of the user data. It is a column in the corresponding table -- dat_type : data type of the user data. Only the following data types are supported: -- 'NUMBER' : -> Java Double -- 'INTEGER': -> Java Integer -- 'VARCHAR2': -> Java String -- 'SDO_GEOMETRY'-> Java JGeometry -- 'DATE': -> Java Date -- 'TIMESTAMP' -> Java Timestamp -- data_length: the length of user data. for varchar2 -- create metadata base table -- 102 version DECLARE BEGIN EXECUTE IMMEDIATE 'CREATE TABLE SDO_NETWORK_USER_DATA( sdo_owner VARCHAR2(32) DEFAULT SYS_CONTEXT(''USERENV'', ''CURRENT_SCHEMA'') NOT NULL, network VARCHAR2(32) NOT NULL, table_type VARCHAR2(12) NOT NULL CHECK (NLS_UPPER(table_type) IN (''NODE'',''LINK'',''PATH'')), data_name VARCHAR2(32) NOT NULL, data_type VARCHAR2(12) NOT NULL CHECK (NLS_UPPER(data_type) IN (''NUMBER'',''INTEGER'',''VARCHAR2'')), data_length INTEGER, CONSTRAINT unique_ndm_user_data UNIQUE(SDO_OWNER,NETWORK,TABLE_TYPE,DATA_NAME))'; EXCEPTION WHEN others THEN NULL; END; / SHOW ERRORS; -- -- add category id -- declare stmt varchar2(512); begin begin stmt := 'ALTER TABLE SDO_NETWORK_USER_DATA ADD ' || '( CATEGORY_ID INTEGER )'; execute immediate stmt; exception when others then NULL; end; end; / -- -- drop 102 constraints on sdo_network_user_data -- DECLARE TYPE CURSOR_TYPE IS REF CURSOR; c CURSOR_TYPE; stmt VARCHAR2 (256); cname VARCHAR2(128); BEGIN OPEN c for SELECT constraint_name from all_constraints where table_name = 'SDO_NETWORK_USER_DATA' and owner = 'MDSYS'; LOOP FETCH c into cname; EXIT WHEN c%NOTFOUND; execute immediate 'alter table sdo_network_user_data drop constraint ' || cname ; END LOOP; CLOSE C; EXCEPTION WHEN others THEN NULL; END ; / SHOW ERRORS; -- -- add supported table types and data types in 11g -- declare begin execute immediate ' alter table sdo_network_user_data add constraint ud_tab_type_con ' || ' check ( NLS_UPPER(table_type) IN (''NODE'',''LINK'',''PATH'',''SUBPATH''))'; execute immediate ' alter table sdo_network_user_data add constraint ud_data_type_con ' || ' check ( NLS_UPPER(data_type) IN (''VARCHAR2'',''INTEGER'',''NUMBER'',''SDO_GEOMETRY'',''DATE'',''TIMESTAMP'',''OBJECT''))'; execute immediate ' alter table sdo_network_user_data add constraint unique_ndm_user_data ' || ' UNIQUE(SDO_OWNER,NETWORK,TABLE_TYPE,DATA_NAME)'; execute immediate ' alter table sdo_network_user_data modify sdo_owner not null' ; execute immediate ' alter table sdo_network_user_data modify network not null' ; execute immediate ' alter table sdo_network_user_data modify table_type not null' ; execute immediate ' alter table sdo_network_user_data modify data_name not null' ; execute immediate ' alter table sdo_network_user_data modify data_type not null' ; exception when others then null; end; / show errors; -- drop views to avoid view-trigger dependency DECLARE BEGIN EXECUTE IMMEDIATE 'DROP VIEW user_sdo_network_user_data'; EXECUTE IMMEDIATE 'DROP VIEW all_sdo_network_user_data'; EXCEPTION WHEN OTHERS THEN NULL; END; / SHOW ERRORS; -- create views CREATE OR REPLACE VIEW user_sdo_network_user_data AS SELECT network, table_type, data_name, data_type,data_length, category_id FROM sdo_network_user_data WHERE sdo_owner = sys_context('USERENV', 'CURRENT_SCHEMA'); CREATE OR REPLACE VIEW all_sdo_network_user_data AS SELECT sdo_owner owner, network, table_type, data_name, data_type,data_length, category_id FROM sdo_network_user_data; -- granting view priviledges GRANT SELECT, INSERT, DELETE, UPDATE ON user_sdo_network_user_data TO PUBLIC; GRANT SELECT ON all_sdo_network_user_data To PUBLIC; -- creating public synonyms CREATE OR REPLACE PUBLIC SYNONYM user_sdo_network_user_data FOR mdsys.user_sdo_network_user_data; CREATE OR REPLACE PUBLIC SYNONYM all_sdo_network_user_data FOR mdsys.all_sdo_network_user_data; -- create triggers for insert/del/update -- trigger for insertion on user_sdo_network_user_data CREATE OR REPLACE TRIGGER sdo_network_ud_ins_trig INSTEAD OF INSERT ON user_sdo_network_user_data REFERENCING NEW AS n FOR EACH ROW DECLARE user_name VARCHAR2(32); no number ; table_name VARCHAR2(32); BEGIN EXECUTE IMMEDIATE 'SELECT user FROM dual' INTO user_name; -- check if network already exists in the networkwork metadata EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM sdo_network_metadata_table ' || ' where sdo_owner = :owner AND NLS_UPPER(network) = :net ' into no using NLS_UPPER(user_name), NLS_UPPER(:n.network); IF ( no = 0 ) THEN mderr.raise_md_error('MD', 'SDO', -13385, user_name||'.'||:n.network || ' NOT IN NETWORK METADATA!'); END IF; INSERT INTO sdo_network_user_data ( sdo_owner, network, table_type, data_name, data_type, data_length, category_id ) VALUES ( NLS_UPPER(user_name), NLS_UPPER(:n.network), NLS_UPPER(:n.table_type), NLS_UPPER(:n.data_name), NLS_UPPER(:n.data_type), :n.data_length, :n.category_id ); END; / SHOW ERRORS; -- trigger for deletion on user_sdo_network_user_data CREATE OR REPLACE TRIGGER sdo_network_ud_del_trig INSTEAD OF DELETE ON user_sdo_network_user_data REFERENCING OLD AS o FOR EACH ROW DECLARE user_name VARCHAR2(256); BEGIN EXECUTE IMMEDIATE 'SELECT USER FROM DUAL' INTO user_name; DELETE FROM sdo_network_user_data WHERE NLS_UPPER(SDO_OWNER) = NLS_UPPER(user_name) AND network = NLS_UPPER(:o.network) AND table_type = NLS_UPPER(:o.table_type) AND data_name = NLS_UPPER(:o.data_name); END; / SHOW ERRORS; -- trigger for update on user_sdo_network_user_data CREATE OR REPLACE TRIGGER sdo_network_ud_upd_trig INSTEAD OF UPDATE ON user_sdo_network_user_data REFERENCING OLD AS o NEW AS n FOR EACH ROW DECLARE user_name VARCHAR2(256); no number ; table_name VARCHAR2(32); BEGIN EXECUTE IMMEDIATE 'SELECT USER FROM DUAL' INTO user_name; -- check if network already exists in the networkwork metadata EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM sdo_network_metadata_table ' || ' where sdo_owner = :owner AND NLS_UPPER(network) = :net ' into no using NLS_UPPER(user_name), NLS_UPPER(:n.network); IF ( no = 0 ) THEN mderr.raise_md_error('MD', 'SDO', -13385, user_name||'.'||:n.network || ' NOT IN NETWORK METADATA!'); END IF; UPDATE sdo_network_user_data SET ( network, table_type, data_name, data_type, data_length, category_id) = (SELECT NLS_UPPER(:n.network), NLS_UPPER(:n.table_type), NLS_UPPER(:n.data_name), NLS_UPPER(:n.data_type), :n.data_length, :n.category_id FROM DUAL) WHERE NLS_UPPER(sdo_owner) = NLS_UPPER(user_name) AND NLS_UPPER(network) = NLS_UPPER(:o.network) AND NLS_UPPER(table_type) = NLS_UPPER(:o.table_type) AND NLS_UPPER(data_name) = NLS_UPPER(:o.data_name); END; / SHOW ERRORS; ------------------------------------------------------------------------------- -- SDO_NETWORK_HISTORIES & RELATED VIEWS AND TRIGGERS ------------------------------------------------------------------------------- DECLARE BEGIN EXECUTE IMMEDIATE 'CREATE TABLE SDO_NETWORK_HISTORIES( owner VARCHAR2(32) DEFAULT SYS_CONTEXT(''USERENV'', ''CURRENT_SCHEMA'') NOT NULL, network VARCHAR2(24) NOT NULL, node_history_table VARCHAR2(32), link_history_table VARCHAR2(32), node_trigger VARCHAR2(32), link_trigger VARCHAR2(32), CONSTRAINT network_histroy_constraint UNIQUE(owner, network)) '; EXCEPTION WHEN OTHERS THEN NULL; END; / SHOW ERRORS; -- create indices on base table to boost the performance of metadata lookup DECLARE BEGIN EXECUTE IMMEDIATE 'DROP INDEX sdo_net_his_owner'; EXCEPTION WHEN OTHERS THEN NULL; END; / SHOW ERRORS; DECLARE BEGIN EXECUTE IMMEDIATE 'DROP INDEX sdo_net_his_net'; EXCEPTION WHEN OTHERS THEN NULL; END; / SHOW ERRORS; DECLARE BEGIN EXECUTE IMMEDIATE 'CREATE INDEX sdo_net_his_owner ON sdo_network_histories(owner)'; EXECUTE IMMEDIATE 'CREATE INDEX sdo_net_his_net ON sdo_network_histories(network)'; EXCEPTION WHEN OTHERS THEN NULL; END; / SHOW ERRORS; -- drop views to avoid view-trigger dependency DECLARE BEGIN EXECUTE IMMEDIATE 'DROP VIEW user_sdo_network_histories'; EXCEPTION WHEN OTHERS THEN NULL; END; / SHOW ERRORS; DECLARE BEGIN EXECUTE IMMEDIATE 'DROP VIEW all_sdo_network_histories'; EXCEPTION WHEN OTHERS THEN NULL; END; / SHOW ERRORS; -- view creations CREATE OR REPLACE VIEW user_sdo_network_histories AS SELECT network, node_history_table, link_history_table, node_trigger, link_trigger FROM sdo_network_histories WHERE owner = sys_context('USERENV', 'CURRENT_SCHEMA'); CREATE OR REPLACE VIEW all_sdo_network_histories AS SELECT * FROM sdo_network_histories; -- granting view priviledges GRANT SELECT, INSERT, DELETE, UPDATE ON user_sdo_network_histories TO PUBLIC; GRANT SELECT ON all_sdo_network_histories To PUBLIC; -- creating public synonyms CREATE OR REPLACE PUBLIC SYNONYM user_sdo_network_histories FOR mdsys.user_sdo_network_histories; CREATE OR REPLACE PUBLIC SYNONYM all_sdo_network_histories FOR mdsys.all_sdo_network_histories; -- trigger for insertion on user_sdo_network_histories CREATE OR REPLACE TRIGGER sdo_network_his_ins_trig INSTEAD OF INSERT ON user_sdo_network_histories REFERENCING NEW AS n FOR EACH ROW DECLARE user_name VARCHAR2(32); BEGIN EXECUTE IMMEDIATE 'SELECT user FROM dual' INTO user_name; INSERT INTO sdo_network_histories( owner, network, node_history_table, link_history_table, node_trigger, link_trigger) VALUES( NLS_UPPER(user_name),:n.network,:n.node_history_table, :n.link_history_table,:n.node_trigger,:n.link_trigger); EXCEPTION WHEN OTHERS THEN RAISE; END; / SHOW ERRORS; -- trigger for update on user_sdo_network_histories CREATE OR REPLACE TRIGGER sdo_network_his_upd_trig INSTEAD OF UPDATE ON user_sdo_network_histories REFERENCING OLD AS o NEW AS n FOR EACH ROW DECLARE user_name VARCHAR2(256); BEGIN EXECUTE IMMEDIATE 'SELECT USER FROM DUAL' INTO user_name; UPDATE sdo_network_histories SET(network,node_history_table,link_history_table,node_trigger,link_trigger) = (SELECT :n.network,:n.node_history_table,:n.link_history_table,:n.node_trigger, :n.link_trigger FROM DUAL) WHERE NLS_UPPER(owner) = NLS_UPPER(user_name) AND network = :o.network; END; / SHOW ERRORS; -- trigger for deletion on user_sdo_network_histories CREATE OR REPLACE TRIGGER sdo_network_his_del_trig INSTEAD OF DELETE ON user_sdo_network_histories REFERENCING OLD AS o FOR EACH ROW DECLARE user_name VARCHAR2(256); BEGIN EXECUTE IMMEDIATE 'SELECT USER FROM DUAL' INTO user_name; DELETE FROM sdo_network_histories WHERE NLS_UPPER(OWNER) = NLS_UPPER(user_name) AND network = :o.network; END; / SHOW ERRORS; ------------------------------------------------------------------------------- -- SDO_NETWORK_TIMESTAMPS & RELATED VIEWS AND TRIGGERS ------------------------------------------------------------------------------- DECLARE BEGIN EXECUTE IMMEDIATE 'CREATE TABLE SDO_NETWORK_TIMESTAMPS( owner VARCHAR2(32) DEFAULT SYS_CONTEXT(''USERENV'', ''CURRENT_SCHEMA'') NOT NULL, network VARCHAR2(24) NOT NULL, table_name VARCHAR2(32) NOT NULL, last_dml_time TIMESTAMP NOT NULL, CONSTRAINT network_timestamp_constraint UNIQUE(owner, network, table_name)) '; EXCEPTION WHEN OTHERS THEN NULL; END; / SHOW ERRORS; -- create indices on base table to boost the performance of metadata lookup DECLARE BEGIN EXECUTE IMMEDIATE 'DROP INDEX sdo_net_time_owner'; EXCEPTION WHEN OTHERS THEN NULL; END; / SHOW ERRORS; DECLARE BEGIN EXECUTE IMMEDIATE 'DROP INDEX sdo_net_time_net'; EXCEPTION WHEN OTHERS THEN NULL; END; / SHOW ERRORS; DECLARE BEGIN EXECUTE IMMEDIATE 'DROP INDEX sdo_net_time_tab'; EXCEPTION WHEN OTHERS THEN NULL; END; / SHOW ERRORS; DECLARE BEGIN EXECUTE IMMEDIATE 'CREATE INDEX sdo_net_time_owner ON sdo_network_timestamps(owner)'; EXECUTE IMMEDIATE 'CREATE INDEX sdo_net_time_net ON sdo_network_timestamps(network)'; EXECUTE IMMEDIATE 'CREATE INDEX sdo_net_time_tab ON sdo_network_timestamps(table_name)'; EXCEPTION WHEN OTHERS THEN NULL; END; / SHOW ERRORS; -- drop views to avoid view-trigger dependency DECLARE BEGIN EXECUTE IMMEDIATE 'DROP VIEW user_sdo_network_timestamps'; EXCEPTION WHEN OTHERS THEN NULL; END; / SHOW ERRORS; DECLARE BEGIN EXECUTE IMMEDIATE 'DROP VIEW all_sdo_network_timestamps'; EXCEPTION WHEN OTHERS THEN NULL; END; / SHOW ERRORS; -- view creations CREATE OR REPLACE VIEW user_sdo_network_timestamps AS SELECT network, table_name, last_dml_time FROM sdo_network_timestamps WHERE owner = sys_context('USERENV', 'CURRENT_SCHEMA'); CREATE OR REPLACE VIEW all_sdo_network_timestamps AS SELECT * FROM sdo_network_timestamps; -- granting view priviledges GRANT SELECT, INSERT, DELETE, UPDATE ON user_sdo_network_timestamps TO PUBLIC; GRANT SELECT ON all_sdo_network_timestamps To PUBLIC; -- creating public synonyms CREATE OR REPLACE PUBLIC SYNONYM user_sdo_network_timestamps FOR mdsys.user_sdo_network_timestamps; CREATE OR REPLACE PUBLIC SYNONYM all_sdo_network_timestamps FOR mdsys.all_sdo_network_timestamps; -- trigger for insertion on user_sdo_network_timestamps CREATE OR REPLACE TRIGGER sdo_network_time_ins_trig INSTEAD OF INSERT ON user_sdo_network_timestamps REFERENCING NEW AS n FOR EACH ROW DECLARE user_name VARCHAR2(32); BEGIN EXECUTE IMMEDIATE 'SELECT user FROM dual' INTO user_name; INSERT INTO sdo_network_timestamps( owner, network, table_name, last_dml_time) VALUES(NLS_UPPER(user_name),:n.network,:n.table_name, :n.last_dml_time); EXCEPTION WHEN OTHERS THEN RAISE; END; / SHOW ERRORS; -- trigger for update on user_sdo_network_timestamps CREATE OR REPLACE TRIGGER sdo_network_time_upd_trig INSTEAD OF UPDATE ON user_sdo_network_timestamps REFERENCING OLD AS o NEW AS n FOR EACH ROW DECLARE user_name VARCHAR2(256); BEGIN EXECUTE IMMEDIATE 'SELECT USER FROM DUAL' INTO user_name; UPDATE sdo_network_timestamps SET(network,table_name,last_dml_time) = (SELECT :n.network,:n.table_name,:n.last_dml_time FROM DUAL) WHERE owner = NLS_UPPER(user_name) AND NLS_UPPER(network) = NLS_UPPER(:o.network) AND NLS_UPPER(table_name) = NLS_UPPER(:o.table_name); END; / SHOW ERRORS; -- trigger for deletion on user_sdo_network_timestamps CREATE OR REPLACE TRIGGER sdo_network_time_del_trig INSTEAD OF DELETE ON user_sdo_network_timestamps REFERENCING OLD AS o FOR EACH ROW DECLARE user_name VARCHAR2(256); BEGIN EXECUTE IMMEDIATE 'SELECT USER FROM DUAL' INTO user_name; DELETE FROM sdo_network_timestamps WHERE owner = NLS_UPPER(user_name) AND network = :o.network AND table_name = :o.table_name; END; / SHOW ERRORS; -- DBSYNC: create types for JDBC DECLARE BEGIN EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE SDO_NET_UPD_HIST AS OBJECT (id NUMBER, link_level NUMBER, partition_id NUMBER, operation VARCHAR2(3))'; EXCEPTION WHEN OTHERS then NULL; END; / SHOW ERRORS; grant execute on mdsys.SDO_NET_UPD_HIST to public; create or replace public synonym SDO_NET_UPD_HIST for mdsys.SDO_NET_UPD_HIST; DECLARE BEGIN EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE SDO_NET_UPD_HIST_TBL IS TABLE OF SDO_NET_UPD_HIST'; EXCEPTION WHEN OTHERS then NULL; END; / SHOW ERRORS; grant execute on mdsys.SDO_NET_UPD_HIST_TBL to public; create or replace public synonym SDO_NET_UPD_HIST_TBL for mdsys.SDO_NET_UPD_HIST_TBL; DECLARE BEGIN EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE SDO_NET_UPD_HIST_N AS OBJECT (id NUMBER, link_level NUMBER, partition_id NUMBER, assigned_partition_id NUMBER, operation VARCHAR2(3))'; EXCEPTION WHEN OTHERS then NULL; END; / SHOW ERRORS; grant execute on mdsys.SDO_NET_UPD_HIST_N to public; create or replace public synonym SDO_NET_UPD_HIST_N for mdsys.SDO_NET_UPD_HIST_N; DECLARE BEGIN EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE SDO_NET_UPD_HIST_NTBL IS TABLE OF SDO_NET_UPD_HIST_N'; EXCEPTION WHEN OTHERS then NULL; END; / SHOW ERRORS; grant execute on mdsys.SDO_NET_UPD_HIST_NTBL to public; create or replace public synonym SDO_NET_UPD_HIST_NTBL for mdsys.SDO_NET_UPD_HIST_NTBL; -- DBSYNC: create types for Internal Process DECLARE BEGIN EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE SDO_NET_LINK AS OBJECT (link_id NUMBER, start_node_id NUMBER, end_node_id NUMBER, operation VARCHAR2(3))'; EXCEPTION WHEN OTHERS then NULL; END; / SHOW ERRORS; grant execute on mdsys.SDO_NET_LINK to public; create or replace public synonym SDO_NET_LINK for mdsys.SDO_NET_LINK; DECLARE BEGIN EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE SDO_NET_LINK_NTBL IS TABLE OF SDO_NET_LINK'; EXCEPTION WHEN OTHERS then NULL; END; / SHOW ERRORS; grant execute on mdsys.SDO_NET_LINK_NTBL to public; create or replace public synonym SDO_NET_LINK_NTBL for mdsys.SDO_NET_LINK_NTBL; DECLARE BEGIN EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE SDO_NET_OP AS OBJECT (id NUMBER, operation VARCHAR2(3))'; EXCEPTION WHEN OTHERS then NULL; END; / SHOW ERRORS; grant execute on mdsys.SDO_NET_OP to public; create or replace public synonym SDO_NET_OP for mdsys.SDO_NET_OP; DECLARE BEGIN EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE SDO_NET_OP_NTBL IS TABLE OF SDO_NET_OP'; EXCEPTION WHEN OTHERS THEN NULL; END; / SHOW ERRORS; grant execute on mdsys.SDO_NET_OP_NTBL to public; create or replace public synonym SDO_NET_OP_NTBL for mdsys.SDO_NET_OP_NTBL;