Rem
Rem $Header: sdo/admin/sdomigrt.sql /st_sdo_11.2.0/4 2011/06/15 16:38:23 sravada Exp $
Rem
Rem sdomigrt.sql
Rem
Rem Copyright (c) 2006, 2011, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      sdomigrt.sql - <one-line expansion of the name>
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    sravada     06/15/11 - bug 12625815
Rem    sravada     05/16/11 - add sdo_geometry_array type
Rem    sravada     05/04/11 - bug 12405988
Rem    sravada     10/07/10 - bug 10163256
Rem    yhu         01/27/10 - set 3D SDO_ROOT_MBR : gtype=3008, etype=1007
Rem    sravada     03/19/09 - alter the SDO_TOPO_OBJECT_ARRAY
Rem    sravada     12/02/08 - drop SDO_CACHED_MAP_DROP_USER
Rem    sravada     10/20/08 - lrg 3649854
Rem    sravada     09/15/08 - drop 10.2 not used objects
Rem    yhu         09/02/08 - bug 7372230: drop SDO_CMT_CBK_TRIG AND OTHER
Rem                           OBJECTS
Rem    sravada     05/14/08 - move sdomsttp.sql down
Rem    sravada     11/08/07 - revoke privs on LIBRARIES
Rem    sravada     04/09/07 - remove sdo_text_type
Rem    sravada     03/06/07 - bug 5916428
Rem    sravada     11/01/06 - bug 5590091
Rem    sravada     08/16/06 - lrg 2493460
Rem    mannamal    07/20/06 - fix lrg 2247076 (rdf upgrade) 
Rem    sdas        06/14/06 - RDF upgrade to 11 
Rem    ningan      04/11/06 - drop NDM related classes files 
Rem    sravada     03/03/06 - Created
Rem


declare
begin
  begin
    execute immediate 'revoke execute on ORDMD_SAM_LIBS from public';
    exception when others then null;
  end;
  begin
    execute immediate 'revoke execute on SDO_GEOR_LIZARDTECH_LIB from public';
    exception when others then null;
  end;
  begin
    execute immediate 'revoke execute on ORDMD_GEOR_LIBS from public';
    exception when others then null;
  end;
  begin
    execute immediate 'revoke execute on ORDMD_GEORX_LIBS from public';
    exception when others then null;
  end;
  begin
    execute immediate 'revoke execute on ORDMD_TP_LIBS from public';
    exception when others then null;
  end;
  begin
    execute immediate 'revoke execute on ORDMD_AG_LIBS from public';
    exception when others then null;
  end;
  begin
    execute immediate 'revoke execute on ORDMD_LRS_LIBS from public';
    exception when others then null;
  end;
  begin
    execute immediate 'revoke execute on ORDMD_PRIDX_LIBS from public';
    exception when others then null;
  end;
  begin
    execute immediate 'revoke execute on ORDMD_UTL_LIBS from public';
    exception when others then null;
  end;
  begin
    execute immediate 'revoke execute on ORDMD_RTREE_LIBS from public';
    exception when others then null;
  end;
  begin
    execute immediate 'revoke execute on ORDMD_CS_LIBS from public';
    exception when others then null;
  end;
  begin
    execute immediate 'revoke execute on ORDMD_MIG_LIBS from public';
    exception when others then null;
  end;
  begin
    execute immediate 'revoke execute on ORDMD_UDT_LIBS from public';
    exception when others then null;
  end;
  begin
    execute immediate 'revoke execute on ORDMD_MBR_LIBS from public';
    exception when others then null;
  end;
  begin
    execute immediate 'revoke execute on ORDMD_WD_LIBS from public';
    exception when others then null;
  end;
  begin
    execute immediate 'revoke execute on ORDMD_REL_LIBS from public';
    exception when others then null;
  end;
  begin
    execute immediate 'revoke execute on ORDMD_IDX_LIBS from public';
    exception when others then null;
  end;
  begin
    execute immediate 'revoke execute on ORDMD_TNPC_LIBS from public';
    exception when others then null;
  end;

  begin
    execute immediate 'drop trigger SDO_DROP_USER_BEFORE ';
      exception
       when others then return;
   end;
  begin
    execute immediate 'drop trigger SDO_NETWORK_CONS_DROP_TRIG ';
      exception
       when others then return;
   end;
  begin
    execute immediate 'drop trigger SDO_NETWORK_UD_DROP_TRIG ';
      exception
       when others then return;
   end;
  begin
    execute immediate 'drop type mdsys.SDO_NODE_I ';
      exception
       when others then return;
   end;
  begin
    execute immediate 'drop type mdsys.SDO_NETWORK_I';
      exception
       when others then return;
   end;
end;
/




-- drop the temporary index_method if it exists
declare
begin
   begin
      execute immediate
              ' drop type sdo_index_method_temp force ';
      exception
       when others then return;
   end;
                                                                                
end;
/

declare
begin
   begin
      execute immediate ' drop type SDO_CART_TEXT force ';
      execute immediate ' drop public synonym SDO_CART_TEXT force ';
      exception
       when others then return;
   end;

end;
/


                                                                                

create type sdo_index_method_temp as object
 ( scan_ctx raw(4) ,
     STATIC function ODCIGetInterfaces(ifclist OUT sys.ODCIObjectList)
         return number );
/

create or replace type body sdo_index_method_temp
is
  STATIC function ODCIGetInterfaces(ifclist OUT sys.ODCIObjectList)
         return number is
   begin
       ifclist := sys.ODCIObjectList(sys.ODCIObject('SYS','ODCIINDEX2'));
       return ODCIConst.Success;
   end ODCIGetInterfaces;
end;
/


declare
begin
   begin
      execute immediate
  ' alter  type sdo_index_method_10i compile specification reuse settings ';
      exception when others then NULL;
   end;
   begin
      execute immediate
  ' alter  type sdo_index_method_9i compile specification reuse settings ';
      exception when others then NULL;
   end;
   begin
      execute immediate
  ' alter  type sdo_index_method compile specification reuse settings ';
      exception when others then NULL;
   end;
   begin
      execute immediate
  ' alter  type RTREE_INDEX_METHOD compile specification reuse settings ';
      exception when others then NULL;
   end;

end;
/


REM reload these headers to fix the issue for upgrade from XE to SE/EE1
REM in the sdo upgrade/dowgrade this will have errors for sdo_3gl
REM since SDO_INDEX_METHOD_10I is dropped in the downgrade just before this
REM but in real upgrade case, the SDO_INDEX_METHOD_10I will exist

declare 
begin
   begin
     execute immediate 
    'CREATE OR REPLACE TYPE SDO_GEOMETRY_ARRAY
     TIMESTAMP ''2008-11-18:10:54:01''
     OID  ''5BF8B4041C603EAEE040578CB2051995''  
     AS VARRAY(10485760) OF SDO_GEOMETRY ';
   exception when others then NULL;
  end; 
  begin
    execute immediate 
    ' create or replace public synonym SDO_GEOMETRY_ARRAY for
         MDSYS.SDO_GEOMETRY_ARRAY';
  end;
end;
/

Rem ===========================================
Rem setup component script filname variable
Rem ===========================================
COLUMN :script_name_sdo NEW_VALUE comp_file_sdo NOPRINT
Variable script_name_sdo varchar2(50)


DECLARE
  p_version SYS.registry$.PRV_VERSION%type;
Begin
  /* this has to work for Locator and SDO */
 begin
   SELECT substr(prv_version,1,4) into p_version 
     FROM sys.registry$ where cid = 'CATPROC';
   if (p_version = '9.2.' or p_version is NULL) then
    :script_name_sdo := dbms_registry.nothing_script; 
   else
    :script_name_sdo := '@prvt3glh.plb';
   end if;

   EXCEPTION 
      WHEN OTHERS THEN
     :script_name_sdo := dbms_registry.nothing_script;
  end;
End;
/
select :script_name_sdo from dual;
@&comp_file_sdo

show errors;

DECLARE
  p_version SYS.registry$.PRV_VERSION%type;
Begin
  /* this has to work for Locator and SDO */
 begin
   SELECT substr(prv_version,1,4) into p_version
     FROM sys.registry$ where cid = 'CATPROC';
   if (p_version = '9.2.') then
    :script_name_sdo := dbms_registry.nothing_script;
   else
    :script_name_sdo := '@sdoutlh.sql';
   end if;

   EXCEPTION
      WHEN OTHERS THEN
     :script_name_sdo := dbms_registry.nothing_script;
  end;
End;
/
select :script_name_sdo from dual;
@&comp_file_sdo

show errors;


declare
begin
   begin
      execute immediate
   ' alter operator LOCATOR_WITHIN_DISTANCE compile ';
      exception when others then NULL;
   end;

   begin
      execute immediate
   ' alter operator RTREE_FILTER compile ';
      exception when others then NULL;
   end;

   begin
      execute immediate
  ' alter operator RTREE_NN compile ';
      exception when others then NULL;
   end;

   begin
      execute immediate
   ' alter operator SDO_FILTER compile ';
      exception when others then NULL;
   end;

   begin
      execute immediate
  ' alter operator SDO_INT2_FILTER compile ';
      exception when others then NULL;
   end;

   begin
      execute immediate
   ' alter operator SDO_INT2_RELATE compile ';
      exception when others then NULL;
   end;

   begin
      execute immediate
   '  alter operator SDO_INT_FILTER compile ';
      exception when others then NULL;
   end;

   begin
      execute immediate
  ' alter operator SDO_INT_RELATE compile ';
      exception when others then NULL;
   end;

   begin
      execute immediate
  ' alter operator SDO_NN compile ';
      exception when others then NULL;
   end;

   begin
      execute immediate
   ' alter operator SDO_NN_DISTANCE compile ';
      exception when others then NULL;
   end;

   begin
      execute immediate
  ' alter operator SDO_RELATE compile ';
      exception when others then NULL;
   end;

   begin
      execute immediate
  ' alter operator SDO_RTREE_FILTER compile ';
      exception when others then NULL;
   end;

   begin
      execute immediate
  ' alter operator SDO_RTREE_RELATE compile ';
      exception when others then NULL;
   end;

   begin
      execute immediate
   ' alter operator SDO_WITHIN_DISTANCE compile ';
      exception when others then NULL;
   end;


end;
/


alter indextype spatial_index
using sdo_index_method_temp
WITH LOCAL range PARTITION;

declare
begin
   begin
      execute immediate
    ' drop function sdo_dummy_function ';
      exception when others then NULL;
   end;
end;
/

create function sdo_dummy_function (geom1 IN mdsys.sdo_geometry, 
                             geom2 IN mdsys.sdo_geometry, param IN varchar2)
return varchar2 is
begin
  return 'DUMMY';
end;
/
grant execute on sdo_dummy_function to public with grant option;

declare
begin
   begin
      execute immediate
    ' drop operator sdo_dummy ';
      exception when others then NULL;
   end;
end;
/
                                                                                

declare
begin
   begin
      execute immediate
  'create  operator
   sdo_dummy binding (mdsys.sdo_geometry, mdsys.sdo_geometry, varchar2)
  return varchar2 using sdo_dummy_function ';
      exception when others then NULL;
   end;
end;
/

grant execute on sdo_dummy to public with grant option;


-- add dummy operator to spatial_index indextype
declare
begin
   begin
      execute immediate
  'alter indextype spatial_index
   add sdo_dummy(mdsys.sdo_geometry, mdsys.sdo_geometry, varchar2) ';
      exception when others then NULL;
   end;
end;
/


-- drop all the operators associated with the indextype
declare
begin
   begin
      execute immediate
 ' alter indextype spatial_index 
drop sdo_nn_distance(number) ' ;
         exception when others then NULL;
   end;

   begin
      execute immediate
  'alter indextype spatial_index drop
  sdo_nn(mdsys.sdo_geometry, mdsys.sdo_geometry, varchar2) ';
         exception when others then NULL;
   end;

   begin
      execute immediate
  ' alter indextype spatial_index drop
   sdo_relate(mdsys.sdo_geometry, mdsys.sdo_geometry, varchar2) ';
         exception when others then NULL;
   end;

   begin
      execute immediate
  ' alter indextype spatial_index drop
    sdo_anyinteract(mdsys.sdo_geometry, mdsys.sdo_geometry) ';
         exception when others then NULL;
   end;

   begin
      execute immediate
   ' alter indextype spatial_index drop
     sdo_anyinteract(mdsys.sdo_topo_geometry, mdsys.sdo_topo_geometry) ';
         exception when others then NULL;
   end;

   begin
      execute immediate
   ' alter indextype spatial_index drop
     sdo_anyinteract(mdsys.sdo_topo_geometry, mdsys.sdo_geometry) ';
         exception when others then NULL;
   end;

   begin
      execute immediate
   ' alter indextype spatial_index drop
     sdo_contains(mdsys.sdo_geometry, mdsys.sdo_geometry) ';
         exception when others then NULL;
   end;

   begin
      execute immediate
   ' alter indextype spatial_index drop
     sdo_inside(mdsys.sdo_geometry, mdsys.sdo_geometry) ';
         exception when others then NULL;
   end;

   begin
      execute immediate
   ' alter indextype spatial_index drop
     sdo_touch(mdsys.sdo_geometry, mdsys.sdo_geometry) ';
         exception when others then NULL;
   end;

   begin
      execute immediate
   ' alter indextype spatial_index drop
     sdo_equal(mdsys.sdo_geometry, mdsys.sdo_geometry) ';
         exception when others then NULL;
   end;

   begin
      execute immediate
   ' alter indextype spatial_index drop
     sdo_covers(mdsys.sdo_geometry, mdsys.sdo_geometry) ';
         exception when others then NULL;
   end;

   begin
      execute immediate
   ' alter indextype spatial_index drop
     sdo_coveredby(mdsys.sdo_geometry, mdsys.sdo_geometry) ';
         exception when others then NULL;
   end;

   begin
      execute immediate
   ' alter indextype spatial_index drop
     sdo_overlaps(mdsys.sdo_geometry, mdsys.sdo_geometry) ';
         exception when others then NULL;
   end;

   begin
      execute immediate
    ' alter indextype spatial_index drop
      sdo_on(mdsys.sdo_geometry, mdsys.sdo_geometry) ';
         exception when others then NULL;
   end;

   begin
      execute immediate
    ' alter indextype spatial_index drop
     sdo_overlapbdydisjoint(mdsys.sdo_geometry, mdsys.sdo_geometry) '; 
         exception when others then NULL;
   end;

   begin
      execute immediate
  ' alter indextype spatial_index drop
   sdo_overlapbdyintersect(mdsys.sdo_geometry, mdsys.sdo_geometry) ';
         exception when others then NULL;
   end;

   begin
      execute immediate
  ' alter indextype spatial_index drop
    sdo_filter(mdsys.sdo_geometry, mdsys.sdo_geometry, varchar2) ';
         exception when others then NULL;
   end;

   begin
      execute immediate
   ' alter indextype spatial_index drop
    sdo_rtree_relate(mdsys.sdo_geometry, mdsys.sdo_geometry, varchar2) ';
         exception when others then NULL;
   end;

   begin
      execute immediate
   ' alter indextype spatial_index drop
    sdo_rtree_filter(mdsys.sdo_geometry, mdsys.sdo_geometry, varchar2) ';
         exception when others then NULL;
   end;

   begin
      execute immediate
  ' alter indextype spatial_index drop
  sdo_int_relate(mdsys.sdo_geometry, mdsys.sdo_geometry, varchar2, varchar2, 
               varchar2, varchar2,number,number, number) ';
         exception when others then NULL;
   end;

   begin
      execute immediate
   ' alter indextype spatial_index drop
   sdo_int_filter(mdsys.sdo_geometry, mdsys.sdo_geometry, varchar2, varchar2, 
               varchar2, varchar2,number,number, number) ';
         exception when others then NULL;
   end;

   begin
      execute immediate
  ' alter indextype spatial_index drop
   sdo_within_distance(mdsys.sdo_geometry, mdsys.sdo_geometry, varchar2) ';
         exception when others then NULL;
   end;

   begin
      execute immediate
  ' alter indextype spatial_index drop
  locator_within_distance(mdsys.sdo_geometry, mdsys.sdo_geometry, varchar2)' ;
         exception when others then NULL;
   end;

   begin
      execute immediate
  ' alter indextype spatial_index drop
  sdo_int2_relate(mdsys.sdo_geometry, mdsys.sdo_geometry, varchar2) ';
         exception when others then NULL;
   end;

   begin
      execute immediate
  ' alter indextype spatial_index drop
  sdo_int2_filter(mdsys.sdo_geometry, mdsys.sdo_geometry, varchar2)' ;
         exception when others then NULL;
   end;

end;
/


declare
begin
   begin
      execute immediate
  ' drop operator sdo_nn force ';
      exception when others then NULL;
   end;

   begin
      execute immediate
  ' drop operator sdo_nn_distance force ';
      exception when others then NULL;
   end;

   begin
      execute immediate
   ' drop operator sdo_filter force ';
      exception when others then NULL;
   end;

   begin
      execute immediate
  ' drop operator sdo_int2_filter force  ';
      exception when others then NULL;
   end;

   begin
      execute immediate
  ' drop operator sdo_int_filter force ';
      exception when others then NULL;
   end;

   begin
      execute immediate
  ' drop operator sdo_relate force ';
      exception when others then NULL;
   end;

   begin
      execute immediate
   ' drop operator sdo_int2_relate force ';
      exception when others then NULL;
   end;

   begin
      execute immediate
   ' drop operator sdo_int_relate force ';
      exception when others then NULL;
   end;

   begin
      execute immediate
  ' drop operator sdo_rtree_filter force ';
      exception when others then NULL;
   end;

   begin
      execute immediate
   ' drop operator sdo_rtree_relate force ';
      exception when others then NULL;
   end;

   begin
      execute immediate
  ' drop operator sdo_within_distance force ';
      exception when others then NULL;
   end;

   begin
      execute immediate
  ' drop operator locator_within_distance force ';
      exception when others then NULL;
   end;

   begin
      execute immediate
  ' drop operator sdo_anyinteract force ';
      exception when others then NULL;
   end;

   begin
      execute immediate
   ' drop operator sdo_contains force ';
      exception when others then NULL;
   end;

   begin
      execute immediate
   ' drop operator sdo_inside force ';
      exception when others then NULL;
   end;

   begin
      execute immediate
   ' drop operator sdo_touch force ';
      exception when others then NULL;
   end;

   begin
      execute immediate
  ' drop operator sdo_equal force ';
      exception when others then NULL;
   end;

   begin
      execute immediate
  ' drop operator sdo_covers force ';
      exception when others then NULL;
   end;

   begin
      execute immediate
   ' drop operator sdo_on force ';
      exception when others then NULL;
   end;

   begin
      execute immediate
    ' drop operator sdo_coveredby force ';
      exception when others then NULL;
   end;

   begin
      execute immediate
  ' drop operator sdo_overlapbdydisjoint force ';
      exception when others then NULL;
   end;

   begin
      execute immediate
  ' drop operator sdo_overlapbdyintersect force ';
      exception when others then NULL;
   end;

   begin
      execute immediate
  ' drop operator sdo_overlaps force ';
      exception when others then NULL;
   end;


end;
/

declare
begin
   begin
      execute immediate
  ' drop type body SDO_INDEX_METHOD_9I ';
      exception when others then NULL;
   end;
   begin
      execute immediate
  ' drop type SDO_INDEX_METHOD_9I ';
      exception when others then NULL;
   end;
end;
/

declare
begin
   begin
      execute immediate
  ' drop type SDO_INDEX_METHOD ';
      exception when others then NULL;
   end;
end;
/

declare
begin
   begin
      execute immediate
  ' drop type SDO_INDEX_METHOD_10I ';
      exception when others then NULL;
   end;
end;
/

drop package prvt_idx;
drop package sdo_3gl;


REM drop the ST_Types only if they do not have the right TOID
COLUMN :script_name NEW_VALUE comp_file NOPRINT
Variable script_name varchar2(50)
Variable sdo_cnt  number;

begin
  select count(*) into :sdo_cnt from all_types where owner='MDSYS' and
  type_name = 'ST_GEOMETRY' and type_oid = '4BA3494AB3EEE5B6E040578CB2057856';
  if (:sdo_cnt = 0) then
    :script_name := '?/md/admin/sdomsttp.sql';
  else
    :script_name := '?/rdbms/admin/nothing.sql';
  end if;
end;
/

select :script_name from dual;
@&comp_file


declare
begin
   begin
      execute immediate
  ' drop type GEOCODE_RESULT ';
      exception when others then NULL;
   end;
   begin
      execute immediate
  ' drop type ROADREC force ';
      exception when others then NULL;
   end;
   begin
      execute immediate
  ' drop  trigger  SDO_CACHED_MAP_DROP_USER  force ';
      exception when others then NULL;
   end;
end;
/

declare
begin
   begin
      execute immediate
   ' drop package GEOCODER_HTTP ';
      exception when others then NULL;
   end;
   begin
      execute immediate
   ' drop package SDO_CMT_CBK ';
      exception when others then NULL;
   end;
   begin
      execute immediate
   ' drop package PRVT_CMT_CBK ';
      exception when others then NULL;
   end;
end;
/

Rem =======================================================================
Rem Drop prvtccbk and sdoccbk related objects
Rem =======================================================================

begin
  execute immediate 'drop trigger sdo_cmt_cbk_trig';
exception
  when others then return;
end;
/

begin
  execute immediate 'drop table sdo_cmt_cbk_fn_table';
exception
  when others then return;
end;
/

begin
  execute immediate 'drop table sdo_cmt_cbk_dml_table';
exception
  when others then return;
end;
/

begin
  execute immediate 'drop table sdo_cmt_cbk_rtree_tab';
exception
  when others then return;
end;
/

begin
  execute immediate 'delete from mdsys.sdo_geom_metadata_table where ' ||
    ' sdo_owner = ''MDSYS'' and sdo_table_name = ''SDO_CMT_CBK_RTREE_TAB'' '||
    ' and sdo_column_name = ''GEOM'' ';
exception
  when others then return;
end;
/

begin
  execute immediate 'drop sequence sdo_cmt_cbk_txn_order';
exception
  when others then return;
end;
/

declare
begin
   begin
      execute immediate
  ' drop view DBA_SDO_INDEX_METADATA ';
      execute immediate
  ' drop view DBA_SDO_GEOM_METADATA ';
      execute immediate
  ' drop view DBA_SDO_INDEX_INFO ';
      exception when others then NULL;
   end;

   begin
      execute immediate
  ' drop public synonym  DBA_SDO_INDEX_METADATA ';
      execute immediate
  ' drop public synonym DBA_SDO_INDEX_INFO ';
      execute immediate
  ' drop public synonym DBA_SDO_GEOM_METADATA ';
      exception when others then NULL;
   end;
end;
/

declare
begin
   begin
      execute immediate
     ' drop package mdexec ';
      exception when others then NULL;
   end;

   begin
      execute immediate
     ' drop package mdexex ';
      exception when others then NULL;
   end;

   begin
      execute immediate
     ' drop package mdtrig ';
      exception when others then NULL;
   end;

   begin
      execute immediate
     ' drop package mdverify ';
      exception when others then NULL;
   end;

   begin
      execute immediate
     ' drop package md_ddl ';
      exception when others then NULL;
   end;

   begin
      execute immediate
     ' drop package md_dml ';
      exception when others then NULL;
   end;

   begin
      execute immediate
     ' drop package md_part ';
      exception when others then NULL;
   end;

   begin
      execute immediate
     ' drop package serv_part ';
      exception when others then NULL;
   end;

   begin
      execute immediate
     ' drop package mdgen ';
      exception when others then NULL;
   end;

   begin
      execute immediate
     ' drop package MDLEXR ';
      exception when others then NULL;
   end;

   begin
      execute immediate
     ' drop package mddict ';
      exception when others then NULL;
   end;

   begin
      execute immediate
     ' drop package mdlib ';
      exception when others then NULL;
   end;

   begin
      execute immediate
     ' drop package mddict ';
      exception when others then NULL;
   end;


   begin
      execute immediate
     ' drop package mdbootstrap ';
      exception when others then NULL;
   end;

end;
/


declare
begin
   begin
      execute immediate
       ' drop table SDO_DATUMS ';
      exception when others then NULL;
   end;

   begin
      execute immediate
       ' drop table SDO_ELLIPSOIDS ';
      exception when others then NULL;
   end;

   begin
      execute immediate
       ' drop table SDO_PROJECTIONS ';
      exception when others then NULL;
   end;

   begin
      execute immediate
       ' drop trigger SDO_NETWORK_LOCKS_DROP_TRIG  ';
      exception when others then NULL;
   end;

end;
/



declare
begin
   begin
     execute immediate ' PURGE TABLESPACE SYSTEM USER MDSYS  ';
     execute immediate ' PURGE TABLESPACE SYSAUX USER MDSYS  ';
           exception when others then NULL;
   end;
end;
/

declare
stmt varchar2(2000);
begin
 
 begin
 stmt := ' create table mdsys.cs_srs_orig as select * from mdsys.cs_Srs';
 execute immediate stmt;
 stmt := 'drop table mdsys.cs_srs';
 execute immediate stmt;
 exception
  when others then
    return;
 end;
end;
/

declare
begin
   begin
     execute immediate ' PURGE TABLESPACE SYSTEM USER MDSYS  ';
     execute immediate ' PURGE TABLESPACE SYSAUX USER MDSYS  ';
           exception when others then NULL;
   end;
   begin
     execute immediate ' DROP VIEW DBA_SDO_LRS_METADATA ';
           exception when others then NULL;
   end;
   begin
     execute immediate ' DROP public synonym DBA_SDO_LRS_METADATA ';
           exception when others then NULL;
   end;
   begin
     execute immediate ' DROP TYPE SDO_RID_ARRAY FORCE ';
           exception when others then NULL;
   end;
end;
/


declare
begin
  begin
     execute immediate ' drop  procedure MDSYS.validate_sdo ';
           exception when others then NULL;
   end;
end;
/

declare
begin
  begin
     execute immediate ' drop  public synonym HHIDLROWS ';
           exception when others then NULL;
   end;
   begin
     execute immediate ' drop public synonym SDO_COMPARE  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym SDO_LATLONTOCODE  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym SDO_DATETODIM  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym SDO_BVALUETODIM  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym SDO_HVALUETODIM  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym SDO_ENCODE  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym SDO_DECODE  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym SDO_TO_LAT  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym SDO_TO_LON  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym SDO_TO_DATE  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym SDO_TO_BVALUE  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym SDO_TO_HVALUE  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHNDIM  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHLENGTH  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHLINEOR  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHBYTELEN  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHPRECISION  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHLEVELS  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHENCODE  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHDECODE  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHCELLBNDRY  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHCELLSIZE  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHSUBSTR  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHCOLLAPSE  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHCOMPOSE  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHNEIGHBOUR  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHCOMMONCODE  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHMATCH  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHDISTANCE  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHORDER  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHGROUP  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHJLDATE  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHCLDATE  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHIDPART  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHIDLPART  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHIDROWS  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHIDLROWS  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHNCOMPARE  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHCOMPARE  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHAND  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHOR  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHXOR  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHBITS  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHGETCID  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHSTBIT  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHGTBIT  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHSTYPE  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHGTYPE  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHSBIT  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHCBIT  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHGBIT  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHINCRLEV  ';
           exception when others then NULL;
   end;

   begin
     execute immediate ' drop public synonym HHLINEPS  ';
           exception when others then NULL;
   end;

end;
/

declare
begin
  begin
     execute immediate ' drop package VALUE$CACHE ';
      exception when others then NULL;
   end;
  begin
     execute immediate ' drop function RDF_MATCH ';
      exception when others then NULL;
   end;
end;
/

drop type body SDO_TOPO_GEOMETRY;

declare
begin
 begin
  execute immediate 
   ' alter type sdo_topo_geometry drop 
    Constructor Function SDO_Topo_Geometry( topology varchar2,
                                    TG_Type number,
                                    TG_layer_id NUMBER,
                                    Topo_ids SDO_TOPO_OBJECT_ARRAY)
     return SELF as RESULT DETERMINISTIC CASCADE ';

  execute immediate 
   ' alter type sdo_topo_geometry drop 
   Constructor Function SDO_Topo_Geometry( topology varchar2,
                                    TG_Type number,
                                    TG_layer_id NUMBER,
                                    add_Topo_ids SDO_TOPO_OBJECT_ARRAY,
                                    delete_Topo_ids SDO_TOPO_OBJECT_ARRAY)
     return SELF as RESULT DETERMINISTIC CASCADE ';

  execute immediate 
   ' alter type sdo_topo_geometry drop 
   Constructor Function SDO_Topo_Geometry( topology varchar2,
                                    Table_Name  varchar2,
                                    Column_Name varchar2,
                                    TG_Type number,
                                    Topo_ids SDO_TOPO_OBJECT_ARRAY)
     return SELF as RESULT DETERMINISTIC CASCADE';

  execute immediate 
   'alter type sdo_topo_geometry drop 
   Constructor Function SDO_Topo_Geometry( topology varchar2,
                                    Table_Name  varchar2,
                                    Column_Name varchar2,
                                    TG_Type number,
                                    add_Topo_ids SDO_TOPO_OBJECT_ARRAY,
                                    delete_Topo_ids SDO_TOPO_OBJECT_ARRAY)
     return SELF as RESULT DETERMINISTIC CASCADE';

  execute immediate 
  ' alter type sdo_topo_geometry drop 
    Constructor Function SDO_Topo_Geometry(TG_ID number,  topology varchar2,
                                    TG_Type number,
                                    TG_layer_id NUMBER,
                                    Topo_ids SDO_TOPO_OBJECT_ARRAY)
     return SELF as RESULT DETERMINISTIC CASCADE';

  execute immediate 
   ' alter type sdo_topo_geometry drop 
   Constructor Function SDO_Topo_Geometry( TG_ID number, topology varchar2,
                                    TG_Type number,
                                    TG_layer_id NUMBER,
                                    add_Topo_ids SDO_TOPO_OBJECT_ARRAY,
                                    delete_Topo_ids SDO_TOPO_OBJECT_ARRAY)
     return SELF as RESULT DETERMINISTIC CASCADE';

  execute immediate 
   ' alter type sdo_topo_geometry drop 
   Constructor Function SDO_Topo_Geometry(TG_ID number,  topology varchar2,
                                    Table_Name  varchar2,
                                    Column_Name varchar2,
                                    TG_Type number,
                                    Topo_ids SDO_TOPO_OBJECT_ARRAY)
     return SELF as RESULT DETERMINISTIC CASCADE';

  execute immediate 
   'alter type sdo_topo_geometry drop 
   Constructor Function SDO_Topo_Geometry(TG_ID number,  topology varchar2,
                                    Table_Name  varchar2,
                                    Column_Name varchar2,
                                    TG_Type number,
                                    add_Topo_ids SDO_TOPO_OBJECT_ARRAY,
                                    delete_Topo_ids SDO_TOPO_OBJECT_ARRAY)
     return SELF as RESULT DETERMINISTIC CASCADE';


  execute immediate 
   ' alter type sdo_topo_geometry drop
   member  Function GET_TOPO_ELEMENTS 
     return SDO_TOPO_OBJECT_ARRAY  DETERMINISTIC CASCADE';

  exception when others then  null;
 end;
end;
/

/* set 3D SDO_ROOT_MBR: gtype=3008 etype=1007 */ 
declare
begin
 update sdo_index_metadata_table a 
   set a.sdo_root_mbr = 
         sdo_geometry(3008, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1007, 3), 
                      a.sdo_root_mbr.SDO_ORDINATES)
   where a.sdo_root_mbr.sdo_gtype = 3003;
end;
/

/* add the JAVA calls in sdomigj.sql */
alter session set current_schema=SYS;
COLUMN mig_java NEW_VALUE migjava_file NOPRINT;
SELECT dbms_registry.script('JAVAVM', '@sdomigj.sql') AS mig_java FROM DUAL;

alter session set current_schema=MDSYS;
@&migjava_file

alter session set current_schema=MDSYS;