Rem Rem $Header: sdo/admin/sdoe111.sql /main/15 2009/09/18 01:39:16 bkazar Exp $ Rem Rem sdoe111.sql Rem Rem Copyright (c) 2006, 2009, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem sdoe111.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem bkazar 09/17/09 - Drop WFS_VERSION col from WFS_FeatureType$ table for downgrade Rem bkazar 09/05/09 - Drop WFS_VERSION col from WFS_GetCapabilitiesInfo$ table for downgrade Rem matperry 05/19/09 - add semantic technologies downgrade Rem hzhu 05/14/09 - Drop GCP related types constructors Rem sravada 04/02/09 - bug 8400863 Rem ningan 12/01/08 - drop TYPE SDO_NET_OP and SDO_NET_OP_NTBL Rem ningan 11/25/08 - drop NDM DB_SYNC feature related types Rem sravada 10/20/08 - add DOWNGRADED Rem ningan 07/31/08 - drop NDM metadata: sdo_network_histories, views Rem drop sdo_network_timestamps, views & triggers Rem ningan 07/18/08 - drop NDM PL/SQL Wrapper Object Type Rem zzhang 02/20/08 - Rem sravada 02/14/08 - Rem mhorhamm 08/30/07 - Drop new column in cs_srs Rem sravada 04/10/06 - Created Rem SET ECHO ON SET FEEDBACK 1 SET NUMWIDTH 10 SET LINESIZE 80 SET TRIMSPOOL ON SET TAB OFF SET PAGESIZE 100 ALTER SESSION SET CURRENT_SCHEMA = MDSYS; EXECUTE dbms_registry.downgrading('SDO'); -- Downgrade Semantic Technologies @@seme111.sql ALTER SESSION SET CURRENT_SCHEMA = MDSYS; SET ECHO ON; -- Downgrade Locator stuff first @@loce111.sql ALTER SESSION SET CURRENT_SCHEMA = MDSYS; --drop georaster java classes call dbms_java.dropjava('-force -synonym -schema MDSYS md/jlib/sdogr.jar'); DECLARE BEGIN begin EXECUTE IMMEDIATE ' ALTER TYPE SDO_GEOR_SRS drop attribute(xRMS,yRMS,zRMS,modelTotalRMS,GCPgeoreferenceModel)'; EXCEPTION WHEN OTHERS THEN null; end; begin EXECUTE IMMEDIATE ' ALTER TYPE SDO_GEOR_SRS drop CONSTRUCTOR FUNCTION SDO_GEOR_SRS( isReferenced VARCHAR2, isRectified VARCHAR2, isOrthoRectified VARCHAR2, srid NUMBER, spatialResolution SDO_NUMBER_ARRAY, spatialTolerance NUMBER, coordLocation NUMBER, rowOff NUMBER, columnOff NUMBER, xOff NUMBER, yOff NUMBER, zOff NUMBER, rowScale NUMBER, columnScale NUMBER, xScale NUMBER, yScale NUMBER, zScale NUMBER, rowRMS NUMBER, columnRMS NUMBER, totalRMS NUMBER, rowNumerator SDO_NUMBER_ARRAY, rowDenominator SDO_NUMBER_ARRAY, columnNumerator SDO_NUMBER_ARRAY, columnDenominator SDO_NUMBER_ARRAY) RETURN SELF AS RESULT'; EXCEPTION WHEN OTHERS THEN NULL; END; END; / SHOW ERRORS; -- Drop SDO_GEOR_GCP type body DECLARE BEGIN begin EXECUTE IMMEDIATE 'DROP TYPE BODY SDO_GEOR_GCP'; EXCEPTION WHEN OTHERS THEN NULL; END; END; / SHOW ERRORS; -- Drop SDO_GEOR_GCPGEOREFTYPE type body DECLARE BEGIN begin EXECUTE IMMEDIATE 'DROP TYPE BODY SDO_GEOR_GCPGEOREFTYPE'; EXCEPTION WHEN OTHERS THEN NULL; END; END; / SHOW ERRORS; --- NDM: downgrade PL/SQL Wrapper Object Type DECLARE BEGIN EXECUTE IMMEDIATE ' DROP TYPE SDO_NETWORK_MANAGER_I FORCE'; EXECUTE IMMEDIATE ' DROP TYPE SDO_NETWORK_MANAGER_T FORCE'; EXECUTE IMMEDIATE ' DROP TYPE SDO_NETWORK_I FORCE'; EXECUTE IMMEDIATE ' DROP TYPE SDO_NETWORK_T FORCE'; EXECUTE IMMEDIATE ' DROP TYPE SDO_NODE_I FORCE'; EXECUTE IMMEDIATE ' DROP TYPE SDO_NODE_T FORCE'; EXECUTE IMMEDIATE ' DROP TYPE SDO_LINK_I FORCE'; EXECUTE IMMEDIATE ' DROP TYPE SDO_LINK_T FORCE'; EXECUTE IMMEDIATE ' DROP TYPE SDO_PATH_I FORCE'; EXECUTE IMMEDIATE ' DROP TYPE SDO_PATH_T FORCE'; execute immediate 'drop trigger MDSYS.SDO_NETWORK_JAVA_INS_TRIG '; execute immediate 'drop trigger MDSYS.SDO_NETWORK_JAVA_DEL_TRIG '; execute immediate 'drop trigger MDSYS.SDO_NETWORK_JAVA_UPD_TRIG '; execute immediate 'drop PUBLIC SYNONYM user_sdo_network_java_objects '; execute immediate 'drop PUBLIC SYNONYM all_sdo_network_java_objects '; execute immediate 'drop view mdsys.user_sdo_network_java_objects '; execute immediate 'drop view mdsys.all_sdo_network_java_objects '; execute immediate 'drop type sdonumlist '; EXCEPTION WHEN OTHERS THEN NULL; END; / SHOW ERRORS; --- NDM: drop new metadata and associated views and triggers BEGIN EXECUTE IMMEDIATE ' DROP VIEW user_sdo_network_histories FORCE'; EXECUTE IMMEDIATE ' DROP VIEW all_sdo_network_histories FORCE'; EXECUTE IMMEDIATE ' DROP TABLE sdo_network_histories FORCE'; EXECUTE IMMEDIATE ' DROP VIEW user_sdo_network_timestamps FORCE'; EXECUTE IMMEDIATE ' DROP VIEW all_sdo_network_timestamps FORCE'; EXECUTE IMMEDIATE ' DROP TABLE sdo_network_timestamps FORCE'; EXCEPTION WHEN OTHERS THEN NULL; END; / SHOW ERRORS; --- NDM: drop DB_SYNC feature related data types BEGIN EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM SDO_NET_OP_NTBL'; EXECUTE IMMEDIATE 'DROP TYPE SDO_NET_OP_NTBL'; EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM SDO_NET_OP'; EXECUTE IMMEDIATE 'DROP TYPE SDO_NET_OP'; EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM SDO_NET_LINK_NTBL'; EXECUTE IMMEDIATE 'DROP TYPE SDO_NET_LINK_NTBL'; EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM SDO_NET_LINK'; EXECUTE IMMEDIATE 'DROP TYPE SDO_NET_LINK'; EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM SDO_NET_UPD_HIST_NTBL'; EXECUTE IMMEDIATE 'DROP TYPE SDO_NET_UPD_HIST_NTBL'; EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM SDO_NET_UPD_HIST'; EXECUTE IMMEDIATE 'DROP TYPE SDO_NET_UPD_HIST'; EXCEPTION WHEN OTHERS THEN NULL; END; / SHOW ERRORS; DECLARE stmt varchar2(100); BEGIN BEGIN stmt := 'alter table MDSYS.WFS_CapabilitiesInfo$ drop (WFS_VERSION)'; execute immediate stmt; exception when others then null; END; BEGIN stmt := 'alter table MDSYS.WFS_FeatureType$ drop (WFS_VERSION)'; execute immediate stmt; exception when others then null; END; END; / SHOW ERRORS; ALTER SESSION SET CURRENT_SCHEMA = SYS; EXECUTE dbms_registry.downgraded('SDO', '11.1.0');