Rem Rem $Header: sdo/admin/sdoepsgd.sql /st_sdo_11.2.0/1 2011/03/29 08:44:05 yhu Exp $ Rem Rem sdoepsgd.sql Rem Rem Copyright (c) 2004, 2011, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem sdoepsgd.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem yhu 03/27/11 - Backport yhu_blr_backport_11828358_11.2.0.2.0 Rem from st_sdo_11.2.0 Rem sravada 05/08/08 - add trigger on SDO_PREFERRED_OPS_SYSTEM Rem mhorhamm 06/13/07 - Add column for vertical WKT to cs_srs Rem mhorhamm 04/06/07 - Put statements into a PL/SQL block Rem mhorhamm 04/03/06 - Add code to trigger Rem mhorhamm 03/10/06 - Change constraint on factor_b and factor_c Rem mhorhamm 03/09/06 - Change length of unit name Rem mhorhamm 02/17/06 - .xsd file not yet declared Rem mhorhamm 02/17/06 - Add restriction for XML column type of Rem ntv2_xml_data Rem mhorhamm 02/15/06 - Add table for NTv2 XML data Rem mhorhamm 02/14/05 - Create synonym for CS_SRS Rem sravada 11/29/04 - bug 3850851 Rem mhorhamm 08/09/04 - Make CS_SRS a view of SDO_CS_SRS Rem sravada 07/29/04 - change table to view Rem mhorhamm 08/03/04 - Add XML column to SDO_COORD_OP_PARAM_VALS Rem mhorhamm 06/25/04 - Add PARAM_VALUE_FILE CLOB column to Rem SDO_COORD_OP_PARAM_VALS table Rem mhorhamm 06/22/04 - Add column to SDO_COORD_REF_SYS Rem mhorhamm 06/10/04 - restrict SDO_COORD_SYS to list of possible types Rem and partition it accordingly Rem mhorhamm 06/07/04 - Move creation of empty table CS_SRS from Rem sdoepsgv.sql to sdoepsgd.sql Rem mhorhamm 06/04/04 - Add column to SDO_COORD_OP_PARAM_USE specifying Rem the legacy parameter name in that context Rem mhorhamm 06/02/04 - Add column to SDO_COORD_OP_METHODS for legacy Rem projection name Rem mhorhamm 05/11/04 - COORD_REF_SYS_NAME is not unique, any more Rem mhorhamm 05/06/04 - Do not require a unique datum name, any more Rem mhorhamm 05/04/04 - Delete column INTL_ID in SDO_ELLIPSOIDS Rem mhorhamm 05/04/04 - Delay creation of SDO_DATUM_* and SDO_CRS_* views Rem mhorhamm 04/27/04 - Add columns to views Rem mhorhamm 04/23/04 - Add column IS_IMPLEMENTED to non-elementary ops Rem mhorhamm 04/15/04 - Differentiate between IS_IMPLEMENTED_FORWARD and Rem IS_IMPLEMENTED_REVERSE Rem mhorhamm 04/14/04 - Add column IS_IMPLEMENTED to Rem mhorhamm 04/14/04 - Add integrity constraint Rem mhorhamm 04/13/04 - Use views for sdo_availablexxx Rem mhorhamm 04/12/04 - Simplify triggers for performance Rem mhorhamm 04/09/04 - Add triggers on SDO_COORD_OP_PATHS Rem mhorhamm 04/09/04 - Add tables with available ops Rem mhorhamm 04/07/04 - Give more intuitive names to constraints Rem mhorhamm 04/06/04 - Allow inverse operations in rules Rem mhorhamm 04/05/04 - Change lower bound on operation IDs Rem mhorhamm 04/02/04 - Add column to sdo_coord_ref_sys Rem mhorhamm 02/23/04 - Fix upper case letters in table names Rem mhorhamm 02/16/04 - Make calls calls conditional Rem mhorhamm 02/10/04 - add columns to paths table Rem mhorhamm 02/06/04 - Created Rem declare stm VARCHAR2(10000); rec_count NUMBER; begin select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'SDO_UNITS_OF_MEASURE'; if(rec_count = 0) then stm := 'CREATE TABLE MDSYS.SDO_UNITS_OF_MEASURE ( UOM_ID NUMBER(10) NOT NULL CONSTRAINT UOM_ID_MUST_BE_POS CHECK(UOM_ID > 0), UNIT_OF_MEAS_NAME VARCHAR2(80 byte) NOT NULL, SHORT_NAME VARCHAR2(80 byte), LEGACY_UNIT_NAME VARCHAR2(80 byte) DEFAULT NULL, UNIT_OF_MEAS_TYPE VARCHAR2(50 byte), TARGET_UOM_ID NUMBER(10), FACTOR_B NUMBER, FACTOR_C NUMBER, INFORMATION_SOURCE VARCHAR2(254 byte), DATA_SOURCE VARCHAR2(40 byte), IS_LEGACY VARCHAR2(5) NOT NULL, LEGACY_CODE NUMBER(10))'; execute immediate stm; end if; end; / alter table mdsys.sdo_units_of_measure modify(unit_of_meas_name varchar2(2083)); CREATE OR REPLACE TRIGGER MDSYS.SDO_UNITS_OF_MEASURE_TRIGGER AFTER INSERT OR UPDATE ON MDSYS.SDO_UNITS_OF_MEASURE DECLARE c NUMBER; BEGIN select count(*) into c from ( select name from ( select unit_of_meas_name name, factor_b, factor_c from mdsys.sdo_units_of_measure union select short_name name, factor_b, factor_c from mdsys.sdo_units_of_measure where not short_name is null ) group by name having count(*) > 1 and min(factor_b/factor_c) < max(factor_b/factor_c)); if(c > 0) then MDSYS.MDERR.RAISE_MD_ERROR('MD', 'SDO', -13199, 'This unit name/short name has already been defined with another factor or base unit.'); end if; ------------------------------------------ select count(*) into c from mdsys.sdo_units_of_measure uom1, mdsys.sdo_units_of_measure uom2 where uom1.target_uom_id = uom2.uom_id and not(lower(uom1.unit_of_meas_type) = lower(uom2.unit_of_meas_type)); if(c > 0) then MDSYS.MDERR.RAISE_MD_ERROR('MD', 'SDO', -13199, 'Unit and base unit have different types.'); end if; END; / declare stm VARCHAR2(10000); rec_count NUMBER; begin select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'SDO_PRIME_MERIDIANS'; if(rec_count = 0) then stm := 'CREATE TABLE MDSYS.SDO_PRIME_MERIDIANS ( PRIME_MERIDIAN_ID NUMBER(10) NOT NULL CONSTRAINT PRIME_MERIDIAN_ID_MUST_BE_POS CHECK(PRIME_MERIDIAN_ID > 0), PRIME_MERIDIAN_NAME VARCHAR2(80 byte) NOT NULL UNIQUE, GREENWICH_LONGITUDE FLOAT(49), UOM_ID NUMBER(10), INFORMATION_SOURCE VARCHAR2(254 byte), DATA_SOURCE VARCHAR2(254 byte))'; execute immediate stm; end if; end; / declare stm VARCHAR2(10000); rec_count NUMBER; begin select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'SDO_ELLIPSOIDS'; if(rec_count = 0) then stm := 'CREATE TABLE MDSYS.SDO_ELLIPSOIDS ( ELLIPSOID_ID NUMBER NOT NULL CONSTRAINT ELLIPSOID_ID_MUST_BE_POS CHECK(ELLIPSOID_ID > 0), ELLIPSOID_NAME VARCHAR2(80 byte) NOT NULL, SEMI_MAJOR_AXIS NUMBER CONSTRAINT SEMI_MAJOR_AXIS_MUST_BE_POS CHECK(SEMI_MAJOR_AXIS > 0), UOM_ID NUMBER, INV_FLATTENING NUMBER, SEMI_MINOR_AXIS NUMBER, INFORMATION_SOURCE VARCHAR2(254 byte), DATA_SOURCE VARCHAR2(40 byte), IS_LEGACY VARCHAR2(5) NOT NULL, LEGACY_CODE NUMBER)'; execute immediate stm; end if; end; / declare stm VARCHAR2(10000); rec_count NUMBER; begin select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'SDO_DATUMS'; if(rec_count = 0) then stm := 'CREATE TABLE MDSYS.SDO_DATUMS ( DATUM_ID NUMBER(10) NOT NULL CONSTRAINT DATUM_ID_MUST_BE_POS CHECK(DATUM_ID > 0), DATUM_NAME VARCHAR2(80 byte) NOT NULL, DATUM_TYPE VARCHAR2(24 byte), ELLIPSOID_ID NUMBER(10), PRIME_MERIDIAN_ID NUMBER(10), INFORMATION_SOURCE VARCHAR2(254 byte), DATA_SOURCE VARCHAR2(40 byte), SHIFT_X NUMBER, SHIFT_Y NUMBER, SHIFT_Z NUMBER, ROTATE_X NUMBER, ROTATE_Y NUMBER, ROTATE_Z NUMBER, SCALE_ADJUST NUMBER, IS_LEGACY VARCHAR2(5) NOT NULL, LEGACY_CODE NUMBER(10)) '; execute immediate stm; execute immediate ' create index MDSYS.SDO_DATUMS_DATUM_TYPE on ' || ' MDSYS.SDO_DATUMS(DATUM_TYPE) '; end if; end; / declare stm VARCHAR2(10000); rec_count NUMBER; begin select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'SDO_COORD_SYS'; if(rec_count = 0) then stm := 'CREATE TABLE MDSYS.SDO_COORD_SYS ( COORD_SYS_ID NUMBER(10) NOT NULL CONSTRAINT COORD_SYS_ID_MUST_BE_POS CHECK(COORD_SYS_ID > 0), COORD_SYS_NAME VARCHAR2(254 byte) NOT NULL UNIQUE, COORD_SYS_TYPE VARCHAR2(24 byte), DIMENSION NUMBER(5), INFORMATION_SOURCE VARCHAR2(254 byte), DATA_SOURCE VARCHAR2(50 byte)) '; execute immediate stm; execute immediate ' create index MDSYS.SDO_COORD_SYS_COORD_SYS_TYPE on ' || ' MDSYS.SDO_COORD_SYS(COORD_SYS_TYPE) '; end if; end; / declare stm VARCHAR2(10000); rec_count NUMBER; begin select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'SDO_COORD_AXIS_NAMES'; if(rec_count = 0) then stm := 'CREATE TABLE MDSYS.SDO_coord_axis_names ( COORD_AXIS_NAME_ID NUMBER(10), COORD_AXIS_NAME VARCHAR2(80) NOT NULL UNIQUE)'; execute immediate stm; end if; end; / declare stm VARCHAR2(10000); rec_count NUMBER; begin select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'SDO_COORD_AXES'; if(rec_count = 0) then stm := 'CREATE TABLE MDSYS.SDO_coord_axes ( COORD_SYS_ID NUMBER(10), COORD_AXIS_NAME_ID NUMBER(10), COORD_AXIS_ORIENTATION VARCHAR2(24), COORD_AXIS_ABBREVIATION VARCHAR2(24), UOM_ID NUMBER(10), "ORDER" NUMBER(5))'; execute immediate stm; end if; end; / declare stm VARCHAR2(10000); rec_count NUMBER; begin select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'SDO_COORD_REF_SYS'; if(rec_count = 0) then stm := 'CREATE TABLE MDSYS.SDO_COORD_REF_SYS ( SRID NUMBER(10) NOT NULL CONSTRAINT SRID_MUST_BE_POS CHECK(SRID > 0), COORD_REF_SYS_NAME VARCHAR2(80 byte) NOT NULL, COORD_REF_SYS_KIND VARCHAR2(24 byte) NOT NULL, COORD_SYS_ID NUMBER(10), DATUM_ID NUMBER(10), GEOG_CRS_DATUM_ID NUMBER(10), SOURCE_GEOG_SRID NUMBER(10), PROJECTION_CONV_ID NUMBER(10), CMPD_HORIZ_SRID NUMBER(10), CMPD_VERT_SRID NUMBER(10), INFORMATION_SOURCE VARCHAR2(254 byte), DATA_SOURCE VARCHAR2(40 byte), IS_LEGACY VARCHAR2(5) NOT NULL, LEGACY_CODE NUMBER(10), LEGACY_WKTEXT VARCHAR2(2046), LEGACY_CS_BOUNDS MDSYS.SDO_GEOMETRY, IS_VALID VARCHAR2(5) DEFAULT ''TRUE'', SUPPORTS_SDO_GEOMETRY VARCHAR2(5) DEFAULT ''TRUE'') '; execute immediate stm; execute immediate ' create index MDSYS.SDO_COORD_REF_SYS_KIND on ' || ' MDSYS.SDO_COORD_REF_SYS(COORD_REF_SYS_KIND) '; stm := 'CREATE OR REPLACE VIEW MDSYS.SDO_COORD_REF_SYSTEM AS ' || ' SELECT * FROM MDSYS.SDO_COORD_REF_SYS'; execute immediate stm; end if; end; / declare stm VARCHAR2(10000); rec_count NUMBER; begin select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'SDO_COORD_OP_METHODS'; if(rec_count = 0) then stm := 'CREATE TABLE MDSYS.SDO_COORD_OP_METHODS ( COORD_OP_METHOD_ID NUMBER(10) NOT NULL CONSTRAINT COORD_OP_METHOD_ID_MUST_BE_POS CHECK(COORD_OP_METHOD_ID > 0), COORD_OP_METHOD_NAME VARCHAR2(50 byte), LEGACY_NAME VARCHAR2(50 byte) DEFAULT NULL, REVERSE_OP NUMBER(1) NOT NULL CONSTRAINT REVERSE_OP_1_OR_0 CHECK(REVERSE_OP IN (0, 1)), INFORMATION_SOURCE VARCHAR2(254 byte), DATA_SOURCE VARCHAR2(40 byte), IS_IMPLEMENTED_FORWARD NUMBER(1) NOT NULL CONSTRAINT IS_IMPL_FW_1_OR_0 CHECK(IS_IMPLEMENTED_FORWARD IN (0, 1)), IS_IMPLEMENTED_REVERSE NUMBER(1) NOT NULL CONSTRAINT IS_IMPL_RV_1_OR_0 CHECK(IS_IMPLEMENTED_REVERSE IN (0, 1)))'; execute immediate stm; end if; end; / declare stm VARCHAR2(10000); rec_count NUMBER; begin select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'SDO_COORD_OPS'; if(rec_count = 0) then stm := 'CREATE TABLE MDSYS.SDO_COORD_OPS ( COORD_OP_ID NUMBER(10) NOT NULL CONSTRAINT COORD_OP_ID_MUST_BE_POS CHECK(COORD_OP_ID > 10), COORD_OP_NAME VARCHAR2(80 byte), COORD_OP_TYPE VARCHAR2(24 byte), SOURCE_SRID NUMBER(10), TARGET_SRID NUMBER(10), COORD_TFM_VERSION VARCHAR2(24 byte), COORD_OP_VARIANT NUMBER(5) CONSTRAINT COORD_OP_VARIANT_MUST_BE_POS CHECK(COORD_OP_VARIANT > 0), COORD_OP_METHOD_ID NUMBER(10), UOM_ID_SOURCE_OFFSETS NUMBER(10), UOM_ID_TARGET_OFFSETS NUMBER(10), INFORMATION_SOURCE VARCHAR2(254 byte), DATA_SOURCE VARCHAR2(40 byte), SHOW_OPERATION NUMBER(3) NOT NULL, IS_LEGACY VARCHAR2(5) NOT NULL, LEGACY_CODE NUMBER(10), REVERSE_OP NUMBER(1), IS_IMPLEMENTED_FORWARD NUMBER(1), IS_IMPLEMENTED_REVERSE NUMBER(1)) '; execute immediate stm; execute immediate ' create index MDSYS.SDO_COORD_OPS_COORD_OP_TYPE on ' || ' MDSYS.SDO_COORD_OPS(COORD_OP_TYPE) '; end if; end; / declare stm VARCHAR2(10000); rec_count NUMBER; begin select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'SDO_PREFERRED_OPS_SYSTEM'; if(rec_count = 0) then stm := 'CREATE TABLE MDSYS.SDO_PREFERRED_OPS_SYSTEM ( SOURCE_SRID NUMBER(10) NOT NULL, COORD_OP_ID NUMBER(10) NOT NULL, TARGET_SRID NUMBER(10) NOT NULL)'; execute immediate stm; end if; end; / CREATE OR REPLACE TRIGGER MDSYS.SDO_PREFERRED_OPS_SYSTEM_TRIG BEFORE INSERT OR UPDATE OR DELETE ON MDSYS.SDO_PREFERRED_OPS_SYSTEM FOR EACH ROW BEGIN MDSYS.sdo_cs.sdo_cs_context_invalidate; END; / declare stm VARCHAR2(10000); rec_count NUMBER; begin select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'SDO_PREFERRED_OPS_USER'; if(rec_count = 0) then stm := 'CREATE TABLE MDSYS.SDO_PREFERRED_OPS_USER ( USE_CASE VARCHAR2(32) NOT NULL, SOURCE_SRID NUMBER(10) NOT NULL, COORD_OP_ID NUMBER(10) NOT NULL, TARGET_SRID NUMBER(10) NOT NULL)'; execute immediate stm; end if; end; / declare stm VARCHAR2(10000); rec_count NUMBER; begin select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'SDO_COORD_OP_PATHS'; if(rec_count = 0) then stm := 'CREATE TABLE MDSYS.SDO_COORD_OP_PATHS ( CONCAT_OPERATION_ID NUMBER(10) NOT NULL CONSTRAINT CONCAT_OP_ID_MUST_BE_POS CHECK(CONCAT_OPERATION_ID > 10), SINGLE_OPERATION_ID NUMBER(10), SINGLE_OP_SOURCE_ID NUMBER(10), SINGLE_OP_TARGET_ID NUMBER(10), OP_PATH_STEP NUMBER(5) CONSTRAINT OP_PATH_STEP_MUST_BE_POS CHECK(OP_PATH_STEP > 0))'; execute immediate stm; end if; end; / declare stm VARCHAR2(10000); rec_count NUMBER; begin select count(*) into rec_count from ALL_VIEWS where owner = 'MDSYS' and view_name = 'SDO_AVAILABLE_NON_ELEM_OPS'; if(rec_count = 0) then stm := ' CREATE VIEW MDSYS.SDO_AVAILABLE_NON_ELEM_OPS AS SELECT SOURCE_SRID, COORD_OP_ID, TARGET_SRID, IS_IMPLEMENTED_FORWARD "IS_IMPLEMENTED" FROM MDSYS.SDO_COORD_OPS WHERE COORD_OP_TYPE = ''CONCATENATED OPERATION'' UNION SELECT TARGET_SRID "SOURCE_SRID", -COORD_OP_ID, SOURCE_SRID "TARGET_SRID", IS_IMPLEMENTED_REVERSE "IS_IMPLEMENTED" FROM MDSYS.SDO_COORD_OPS WHERE COORD_OP_TYPE = ''CONCATENATED OPERATION'' AND REVERSE_OP = 1 '; execute immediate stm; end if; end; / declare stm VARCHAR2(10000); rec_count NUMBER; begin select count(*) into rec_count from ALL_VIEWS where owner = 'MDSYS' and view_name = 'SDO_AVAILABLE_ELEM_OPS'; if(rec_count = 0) then stm := ' CREATE VIEW MDSYS.SDO_AVAILABLE_ELEM_OPS AS ( SELECT OPS.SOURCE_SRID, OPS.COORD_OP_ID "COORD_OP_ID", OPS.TARGET_SRID, OPS.IS_IMPLEMENTED_FORWARD "IS_IMPLEMENTED" FROM MDSYS.SDO_COORD_OPS OPS WHERE NOT (OPS.COORD_OP_TYPE = ''CONCATENATED OPERATION'') ) UNION ( SELECT OPS.TARGET_SRID "SOURCE_SRID", -OPS.COORD_OP_ID "COORD_OP_ID", OPS.SOURCE_SRID "TARGET_SRID", OPS.IS_IMPLEMENTED_REVERSE "IS_IMPLEMENTED" FROM MDSYS.SDO_COORD_OPS OPS WHERE NOT (OPS.COORD_OP_TYPE = ''CONCATENATED OPERATION'') AND OPS.REVERSE_OP = 1 ) '; execute immediate stm; end if; end; / declare stm VARCHAR2(10000); rec_count NUMBER; begin select count(*) into rec_count from ALL_views where owner = 'MDSYS' and view_name = 'SDO_AVAILABLE_OPS'; if(rec_count = 0) then stm := ' CREATE VIEW MDSYS.SDO_AVAILABLE_OPS AS SELECT SOURCE_SRID, COORD_OP_ID, TARGET_SRID, IS_IMPLEMENTED FROM MDSYS.SDO_AVAILABLE_ELEM_OPS UNION SELECT SOURCE_SRID, COORD_OP_ID, TARGET_SRID, IS_IMPLEMENTED FROM MDSYS.SDO_AVAILABLE_NON_ELEM_OPS '; execute immediate stm; end if; end; / declare stm VARCHAR2(10000); rec_count NUMBER; begin select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'SDO_COORD_OP_PARAMS'; if(rec_count = 0) then stm := 'CREATE TABLE MDSYS.SDO_COORD_OP_PARAMS ( PARAMETER_ID NUMBER(10) NOT NULL CONSTRAINT PARAMETER_ID_MUST_BE_POS CHECK(PARAMETER_ID > 0), PARAMETER_NAME VARCHAR2(80 byte), INFORMATION_SOURCE VARCHAR2(254 byte), DATA_SOURCE VARCHAR2(40 byte))'; execute immediate stm; end if; end; / declare stm VARCHAR2(10000); rec_count NUMBER; begin select count(*) into rec_count from ALL_TAB_COLUMNS where owner = 'MDSYS' and table_name = 'SDO_COORD_OP_PARAMS' and column_name = 'UNIT_OF_MEAS_TYPE'; if(rec_count = 0) then stm := 'ALTER TABLE MDSYS.SDO_COORD_OP_PARAMS ADD ( UNIT_OF_MEAS_TYPE VARCHAR2(50 byte))'; execute immediate stm; end if; end; / declare stm VARCHAR2(10000); rec_count NUMBER; begin select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'SDO_COORD_OP_PARAM_USE'; if(rec_count = 0) then stm := 'CREATE TABLE MDSYS.SDO_COORD_OP_PARAM_USE ( COORD_OP_METHOD_ID NUMBER(10), PARAMETER_ID NUMBER(10), LEGACY_PARAM_NAME VARCHAR2(80 byte) DEFAULT NULL, SORT_ORDER NUMBER(5), PARAM_SIGN_REVERSAL VARCHAR2(3 byte))'; execute immediate stm; end if; end; / declare stm VARCHAR2(10000); rec_count NUMBER; begin select count(*) into rec_count from ALL_TABLES where owner = 'MDSYS' and table_name = 'SDO_COORD_OP_PARAM_VALS'; if(rec_count = 0) then stm := 'CREATE TABLE MDSYS.SDO_COORD_OP_PARAM_VALS ( COORD_OP_ID NUMBER(10), COORD_OP_METHOD_ID NUMBER(10), PARAMETER_ID NUMBER(10), PARAMETER_VALUE FLOAT(49), PARAM_VALUE_FILE_REF VARCHAR2(254 byte), PARAM_VALUE_FILE CLOB DEFAULT NULL, PARAM_VALUE_XML XMLTYPE DEFAULT NULL, UOM_ID NUMBER(10))'; execute immediate stm; end if; end; / -- Create legacy table... -- Content will be added, later declare begin begin execute immediate ' DROP VIEW MDSYS.CS_SRS '; execute immediate ' DROP TABLE MDSYS.SDO_CS_SRS '; exception when others then NULL; end; end; / begin execute immediate ' CREATE TABLE MDSYS.SDO_CS_SRS ( CS_NAME VARCHAR2(80), SRID INTEGER NOT NULL PRIMARY KEY, AUTH_SRID INTEGER, AUTH_NAME VARCHAR2(256), WKTEXT VARCHAR2(2046), CS_BOUNDS MDSYS.SDO_GEOMETRY)'; exception when others then NULL; end; / begin execute immediate ' alter table mdsys.SDO_CS_SRS add( WKTEXT3D VARCHAR2(4000))'; end; / begin execute immediate 'CREATE VIEW MDSYS.CS_SRS AS (SELECT * FROM MDSYS.SDO_CS_SRS)'; exception when others then NULL; end; / begin execute immediate 'drop table mdsys.ntv2_xml_data'; exception when others then NULL; end; / begin execute immediate ' create table mdsys.ntv2_xml_data ( ntv2_file_id number not null, sequence_number number not null, xml xmltype not null, constraint ntv2_xml_data_pk primary key(ntv2_file_id, sequence_number) using index)'; exception when others then NULL; end; / grant select on mdsys.ntv2_xml_data to public; create or replace public synonym ntv2_xml_data for mdsys.ntv2_xml_data; begin execute immediate 'drop sequence mdsys.ntv2_sequence'; exception when others then NULL; end; / begin execute immediate 'create sequence mdsys.ntv2_sequence start with 1'; exception when others then NULL; end; / grant select on MDSYS.CS_SRS to public; begin execute immediate 'create or replace public synonym CS_SRS for MDSYS.CS_SRS'; exception when others then NULL; end; /