Rem
Rem $Header: sdo/admin/sdoe102.sql /main/61 2009/05/10 15:36:56 alwu Exp $
Rem $Header: sdo/admin/sdoe102.sql /main/61 2009/05/10 15:36:56 alwu Exp $
Rem
Rem sdoe102.sql
Rem
Rem Copyright (c) 2005, 2009, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      sdoe102.sql - script for downgrading from 11g to 10.2
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    alwu        04/30/09 - add fix for ORA-30512
Rem    sravada     02/14/08 - 
Rem    ningan      12/05/07 - bug 6653556
Rem    mhorhamm    11/20/07 - Truncate sdo_cs_srs
Rem    sravada     06/25/07 - downgrade prvtpc
Rem    sdas        06/17/07 - upgrade pkg drop done by seme102.sql
Rem    ningan      05/31/07 - Drop SDO_NETWORK_T & SDO_NETWORK_I
Rem    sdas        05/24/07 - Move semantic store stuff to seme102.sql
Rem    sdas        05/17/07 - fix RDF downgrade
Rem    sravada     05/11/07 - Move locator stuff to loce102.sql
Rem    mhorhamm    03/19/07 - Continue
Rem    mhorhamm    02/19/07 - Drop trigger SDO_coord_op_param_val_TRIGGER
Rem    sravada     05/02/07 - delete type body for TST_TEXT types
Rem    wexu        05/03/07 - drop sdo_geor_srs constructor
Rem    rkothuri    04/29/07 - fix 6021893
Rem    sdas        04/16/07 - remove rdf dummy upgrade/downgrade routines
Rem    rkothuri    04/11/07 - drop reset_inpgor_index
Rem    sravada     04/02/07 - add st_geometry modifiers
Rem    mhorhamm    02/28/07 - Shorten table name for WM
Rem    mhorhamm    01/12/07 - app user-related roles not required, any more
Rem    sravada     12/26/06 - downgrade geocoder/router jars
Rem    sravada     12/20/06 - down grade geocoder type
Rem    jcwang      11/30/06 - drop package sdo_net_partition
Rem    sravada     11/27/06 - drop sdoapi.jar
Rem    hgong       11/08/06 - drop sdotype.jar
Rem    alwu        11/03/06 - add fix the invalid objects
Rem    mhorhamm    10/11/06 - Drop tables for WS conference
Rem    alwu        09/29/06 - add logic to handle sdo_sem_perf package
Rem    mhorhamm    09/25/06 - Drop WS-related objects
Rem    hgong       10/05/06 - remove dropping sdondmx.jar
Rem    jxyang      09/08/06 - remove matchVector from sdo_geo_addr
Rem    mannamal    08/28/06 - Add drop of sem indextype stats package
Rem    alwu        08/15/06 - add drop of rdf_hist$ related
Rem    alwu        08/11/06 - bug: 4693687 invalidate index when truncate
Rem                           happens
Rem    rkothuri    08/06/06 - add drop sdo_tin_blk_tpe
Rem    mannamal    07/10/06 - Add drop of sem btree package 
Rem    mannamal    06/15/06 - Add DROP of SEM ancillary operators 
Rem    sdas        06/14/06 - RDF downgrade to 10.2 
Rem    mannamal    06/12/06 - Add FORCE option to dropping SEM operators 
Rem    mannamal    06/09/06 - Add SEM primary operator drops 
Rem    rkothuri    05/31/06 - add separate sdo_tin_blk types 
Rem    alwu        04/19/06 - remove the SDO_SEM_CTX created 
Rem    zzhang      04/18/06 - drop package sdo_geor_admin
Rem    rkothuri    04/07/06 - drop tin types/pkgs 
Rem    ningan      04/11/06 - Drop sdonm.jar & sdondmx.jar
Rem    mhorhamm    04/04/06 - Drop further constraint 
Rem    mhorhamm    04/04/06 - Downgrade some changes to SDO_UNITS_OF_MEASURE 
Rem    jcwang      03/29/06 - fix NDM PL/SQL Wrapper type for downgrade 
Rem    rchatter    03/28/06 - drop wfs/cs metadata 
Rem    wexu        03/28/06 - drop package sdo_geor_aux 
Rem    mhorhamm    02/28/06 - Change sequence when dropping tables 
Rem    mhorhamm    02/22/06 - Downgrade OpenLS 
Rem    sravada     02/22/06 - downgrade the indextype 
Rem    wexu        01/17/06 - drop range types 
Rem    sravada     01/11/06 - downgrade sdo_topo_geometry type 
Rem    zzhang      11/18/05 - Created
Rem

ALTER SESSION SET CURRENT_SCHEMA = MDSYS;
 
EXECUTE dbms_registry.downgrading('SDO');

@@sdoe111.sql
-- Downgrade Locator stuff first
@@loce102.sql

ALTER SESSION SET CURRENT_SCHEMA = MDSYS;

-- TIN specific functions
DROP PUBLIC  SYNONYM sdo_tin_pkg;
DROP PACKAGE sdo_tin_pkg;
DROP PACKAGE prvt_tin;

DROP TYPE SDO_TIN_BLK_TYPE;
DROP TYPE SDO_PC_BLK_TYPE;
DROP PUBLIC SYNONYM SDO_TIN;
DROP TYPE SDO_TIN;

DROP TABLE SDO_TIN_BLK_TABLE;

DROP PUBLIC SYNONYM SDO_TIN_BLK;
DROP TYPE SDO_TIN_BLK;

-- Point-cloud specific functions
DROP PUBLIC  SYNONYM sdo_pc_pkg;
DROP PACKAGE sdo_pc_pkg;
DROP PACKAGE prvt_pc;
DROP PACKAGE prvtpc;

DROP PUBLIC SYNONYM SDO_PC;
DROP TYPE SDO_PC;

DROP TABLE SDO_PC_BLK_TABLE;

DROP PUBLIC SYNONYM SDO_PC_BLK;
DROP TYPE SDO_PC_BLK;

DROP PUBLIC SYNONYM SDO_ORGSCL_TYPE;
DROP TYPE SDO_ORGSCL_TYPE force;

drop library ORDMD_TNPC_LIBS;

-- TIN/PC Metadata
DROP PACKAGE sdotnpc;
DROP PACKAGE prvttnpc;

DROP PUBLIC SYNONYM ALL_SDO_TIN_PC_SYSDATA;
DROP PUBLIC SYNONYM USER_SDO_TIN_PC_SYSDATA;
DROP VIEW ALL_SDO_TIN_PC_SYSDATA;
DROP VIEW USER_SDO_TIN_PC_SYSDATA;
DROP TABLE SDO_TIN_PC_SYSDATA_TABLE;

-- For OpenLS
drop package sdo_ols;
drop package sdo_ols_locutl;
drop package sdo_ols_presentation;
drop package sdo_ols_route;
drop package sdo_ols_directory;

drop table ols_dir_synonyms;
drop table ols_dir_categorizations;
drop table ols_dir_businesses;
drop table ols_dir_categories;
drop table ols_dir_category_types;
drop table ols_dir_business_chains;

drop table openls_namespaces;
drop table openls_classifications;
drop table openls_xpaths;
drop table openls_nodes;
drop table openlsservices;

drop type OPENLS_ORDINATES force;

-- For WS

drop sequence mdsys.sdo_ws_conference_ids;
drop table mdsys.sdo_ws_conference_participants;
drop table mdsys.sdo_ws_conference_results;
drop table mdsys.sdo_ws_conference;

@@sdoewfcs.sql


--for GeoRaster
drop type sdo_range_array;
drop type sdo_range;

BEGIN
 BEGIN
  EXECUTE IMMEDIATE 
   'ALTER TYPE SDO_GEOR_SRS DROP ' ||
    'CONSTRUCTOR FUNCTION SDO_GEOR_SRS(SELF IN OUT NOCOPY SDO_GEOR_SRS) ' ||
    ' RETURN SELF AS RESULT';
 EXCEPTION
   WHEN OTHERS THEN
     NULL;
 END;

 BEGIN
  EXECUTE IMMEDIATE 'DROP TYPE BODY SDO_GEOR_SRS';
 EXCEPTION
   WHEN OTHERS THEN
     NULL;
 END;
END;
/

drop trigger sdo_geor_err_trigger;
drop trigger sdo_geor_bddl_trigger;
drop trigger sdo_geor_addl_trigger;

drop package body sdo_geor_aux;
drop package sdo_geor_aux;

drop package body sdo_geor_admin;
drop package sdo_geor_admin;

-- ----------------------------------------------------------------
--    Trigger for dropping a GeoRaster user or table
-- ----------------------------------------------------------------
CREATE OR REPLACE TRIGGER SDO_GEOR_DROP_USER
AFTER DROP ON DATABASE
DECLARE
  type vcurType is REF CURSOR;
  vcur vcurType;
  stmt VARCHAR2(1000);
  stm2 VARCHAR2(200);
  rdt  VARCHAR2(80);
  rsid number;
  cnt  number;
BEGIN
  IF sys.dbms_standard.dictionary_obj_type = 'USER' THEN
    stmt := 'DELETE FROM SDO_GEOR_SYSDATA_TABLE WHERE SDO_OWNER = :name';
    EXECUTE IMMEDIATE stmt using sys.dbms_standard.dictionary_obj_name; 

  ELSIF sys.dbms_standard.dictionary_obj_type = 'TABLE' AND
        sys.dbms_standard.dictionary_obj_owner <> 'MDSYS' AND
        sys.dbms_standard.dictionary_obj_name <> 'SDO_GEOR_SYSDATA_TABLE' THEN
    stmt := 'SELECT COUNT(*) FROM SDO_GEOR_SYSDATA_TABLE ' ||
            ' WHERE SDO_OWNER = :1 AND GEORASTER_TABLE_NAME = :2';
    EXECUTE IMMEDIATE stmt INTO cnt USING sys.dbms_standard.dictionary_obj_owner,
                                          sys.dbms_standard.dictionary_obj_name;
    IF cnt > 0 THEN
      stmt := 'SELECT RDT_TABLE_NAME, RASTER_ID FROM SDO_GEOR_SYSDATA_TABLE' ||
              ' WHERE SDO_OWNER = :1 AND GEORASTER_TABLE_NAME = :2';
      open vcur for stmt using sys.dbms_standard.dictionary_obj_owner, sys.dbms_standard.dictionary_obj_name;
      loop
        fetch vcur into rdt, rsid;
        exit when vcur%NOTFOUND;
        if instr(rdt, '.') = 0 then
          rdt := sys.dbms_standard.dictionary_obj_owner || '.' || rdt;
        end if;
        stm2 := 'DELETE FROM ' || rdt || ' WHERE rasterid=:1';
        begin
          execute immediate stm2 using rsid;
        exception
          when others then
            -- it is ok if the rasterDataTable does not exists
            if SQLCODE <> -942 then
              raise;
            end if;
        end;
      end loop;
      stmt := 'DELETE FROM SDO_GEOR_SYSDATA_TABLE ' ||
              ' WHERE SDO_OWNER = :name AND GEORASTER_TABLE_NAME = :2';
      EXECUTE IMMEDIATE stmt USING sys.dbms_standard.dictionary_obj_owner, sys.dbms_standard.dictionary_obj_name;
    END IF;
  END IF;
END;
/
SHOW ERRORS;

-- ----------------------------------------------------------------
--    Trigger for truncating a GeoRaster table
-- ----------------------------------------------------------------
CREATE OR REPLACE TRIGGER SDO_GEOR_TRUNC_TABLE
AFTER TRUNCATE ON DATABASE
DECLARE
  type vcurType is REF CURSOR;
  vcur vcurType;
  stmt VARCHAR2(1000);
  stm2 VARCHAR2(250);
  rdt  VARCHAR2(80);
  rsid number;
  cnt  number;
  m_id number;
  model varchar2(100);

  e_cannot_multi_mod EXCEPTION;
  PRAGMA EXCEPTION_INIT (e_cannot_multi_mod, -30512);
BEGIN
  IF sys.dbms_standard.dictionary_obj_type <> 'TABLE' THEN
    return;
  END IF;

  stmt := 'SELECT COUNT(*) FROM SDO_GEOR_SYSDATA_TABLE ' ||
          ' WHERE SDO_OWNER = :1 AND GEORASTER_TABLE_NAME = :2';
  EXECUTE IMMEDIATE stmt INTO cnt USING sys.dbms_standard.dictionary_obj_owner,
                                        sys.dbms_standard.dictionary_obj_name;
  IF cnt > 0 THEN
    stmt := 'SELECT RDT_TABLE_NAME, RASTER_ID FROM SDO_GEOR_SYSDATA_TABLE' ||
            ' WHERE SDO_OWNER = :1 AND GEORASTER_TABLE_NAME = :2';
    open vcur for stmt using sys.dbms_standard.dictionary_obj_owner, sys.dbms_standard.dictionary_obj_name;
    loop
      fetch vcur into rdt, rsid;
      exit when vcur%NOTFOUND;
      if instr(rdt, '.') = 0 then
        rdt := sys.dbms_standard.dictionary_obj_owner || '.' || rdt;
      end if;
      stm2 := 'DELETE FROM ' || rdt || ' WHERE rasterid=:1';
      begin
        execute immediate stm2 using rsid;
      exception
        when others then
          -- it is ok if the rasterDataTable does not exists
          if SQLCODE <> -942 then
            raise;
          end if;
      end;
    end loop;
    stmt := 'DELETE FROM SDO_GEOR_SYSDATA_TABLE ' ||
            ' WHERE SDO_OWNER = :name AND GEORASTER_TABLE_NAME = :2';
    EXECUTE IMMEDIATE stmt USING sys.dbms_standard.dictionary_obj_owner, sys.dbms_standard.dictionary_obj_name;
  END IF;


  -- RDF trigger for TRUNCATE handler
  stmt := 'SELECT COUNT(*) FROM ALL_TABLES ' ||
          ' WHERE OWNER = ''MDSYS'' AND TABLE_NAME = ''RDF_MODEL$'' ';
  EXECUTE IMMEDIATE stmt INTO cnt;
  if (cnt = 1) then
  begin
    cnt := 0;
    stmt := 'SELECT count(*) FROM MDSYS.RDF_MODEL$ ' ||
            ' WHERE OWNER = :1 AND TABLE_NAME = :2';
    begin
      EXECUTE IMMEDIATE stmt INTO cnt USING sys.dbms_standard.dictionary_obj_owner,
                                            sys.dbms_standard.dictionary_obj_name;
    exception
      when e_cannot_multi_mod then
        if (substr(sys.dbms_standard.dictionary_obj_name, 1, 4)='BIN$') then
          return;
        else
          raise;
        end if;
    end;
 
    if (cnt > 0)  then
      stmt := 'SELECT model_name, model_id FROM MDSYS.RDF_MODEL$ ' ||
            ' WHERE OWNER = :1 AND TABLE_NAME = :2';
       begin
         EXECUTE IMMEDIATE stmt INTO model, m_id USING sys.dbms_standard.dictionary_obj_owner,
                                                       sys.dbms_standard.dictionary_obj_name;
       exception
        when e_cannot_multi_mod then
          if (substr(sys.dbms_standard.dictionary_obj_name, 1, 4)='BIN$') then
            return;
          else
            raise;
          end if;
       end;

       begin
         EXECUTE IMMEDIATE 
            'BEGIN MDSYS.RDF_APIS_INTERNAL.NOTIFY_MODEL_DML('''||model||''', ''DEL''); END;';
       exception
        when others then NULL;
       end;

       stmt := ' DELETE  MDSYS.RDF_LINK$ WHERE  model_id = '||to_char(m_id) ;
       EXECUTE IMMEDIATE stmt;
    end if;
      
     exception
        when others then NULL;
  end;

  end if;
  
END;
/
SHOW ERRORS;


--
-- For NDM
--

--
-- NDM PL/SQL Wrapper object type downgrade
-- 


--
-- drop the whole type as they are not only used in sdonet_mem package
-- downgrade will load/create these types with scripts of downgraded version
--

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';


  EXCEPTION WHEN OTHERS THEN NULL;
 
END;
/
SHOW ERRORS;


-- drop sdo_net_partition synonym package

DROP TYPE NDM_VECTOR_2D;
DROP PUBLIC SYNONYM SDO_NET_PARTITION;
DROP PACKAGE SDO_NET_PARTITION; 
drop package SDO_ROUTER_PARTITION;
drop procedure ELOCATIONSETJVMHEAPSIZE;

drop trigger SDO_NETWORK_CONS_DROP_TRIG;
drop trigger SDO_NETWORK_CONS_INS_TRIG;
drop trigger SDO_NETWORK_CONS_DEL_TRIG;
drop trigger SDO_NETWORK_CONS_UPD_TRIG;
drop trigger SDO_NETWORK_LOCKS_DROP_TRIG;
drop trigger SDO_NETWORK_LOCKS_INS_TRIG;
drop trigger SDO_NETWORK_LOCKS_DEL_TRIG;
drop trigger SDO_NETWORK_LOCKS_UPD_TRIG;
drop trigger SDO_NETWORK_UD_DROP_TRIG;
drop trigger SDO_NETWORK_UD_INS_TRIG;
drop trigger SDO_NETWORK_UD_DEL_TRIG;
drop trigger SDO_NETWORK_UD_UPD_TRIG;
drop trigger SDO_ST_SYN_CREATE;

--RDF section
@@seme102.sql
 
ALTER SESSION SET CURRENT_SCHEMA = SYS;

EXECUTE dbms_registry.downgraded('SDO', '10.2.0');
commit;