Rem
Rem $Header: sdo/admin/sdoeoper.sql /main/10 2009/03/20 14:46:28 yhu Exp $
Rem
Rem sdoeoper.sql
Rem
Rem Copyright (c) 2004, 2009, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      sdoeoper.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    yhu         03/20/09 - downgrade for sdo_statistics
Rem    sravada     05/06/08 - lrg 3324628
Rem    sravada     03/30/07  - 
Rem    bgouslin    01/07/07  - New version to fix Windoze format issue caused
Rem                            by some ADE bug
Rem    sravada     02/22/06 -  move out stuff other than indextype stuff
Rem    mhorhamm    11/22/05 - Rename 2 public synonyms 
Rem    sravada     06/13/05 - 
Rem    mhorhamm    09/23/04 - Change sequence of dropped types 
Rem    sravada     08/30/04 - sravada_fix_install_issues_3
Rem    sravada     08/30/04 - Created
Rem


declare
begin
   begin
      execute immediate 
            ' drop type sdo_index_method_temp 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
    'DISASSOCIATE STATISTICS FROM INDEXTYPES spatial_index FORCE' ;
  exception when others then NULL;
 end;
 begin 
  execute immediate
    'DISASSOCIATE STATISTICS FROM PACKAGES sdo_3gl FORCE';
  exception when others then NULL;
 end;
 begin
  execute immediate
    'DISASSOCIATE STATISTICS FROM PACKAGES prvt_idx FORCE';
  exception when others then NULL;
 end; 
end;
/


declare
begin
 begin
   execute immediate
     'alter type sdo_statistics compile specification reuse settings';
   exception when others then NULL;
 end;
 begin
   execute immediate
     'DROP TYPE sdo_statistics';
   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 return;
   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 return;
   end;
end;
/

create  operator
  sdo_dummy binding (mdsys.sdo_geometry, mdsys.sdo_geometry, varchar2)
return varchar2
  using sdo_dummy_function;
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 return;
   end;
end;
/

declare
begin
   begin
      execute immediate 
       ' alter indextype spatial_index 
         drop sdo_nn_distance(number) ';
      exception
       when others then return;
   end;
end;
/

alter indextype spatial_index drop
sdo_nn(mdsys.sdo_geometry, mdsys.sdo_geometry, varchar2);
alter indextype spatial_index drop
sdo_nn(mdsys.st_geometry, mdsys.sdo_geometry, varchar2);
alter indextype spatial_index drop
sdo_nn(mdsys.st_geometry, mdsys.st_geometry, varchar2);
alter indextype spatial_index drop
sdo_nn(mdsys.sdo_geometry, mdsys.st_geometry, varchar2);
alter indextype spatial_index drop
sdo_nn(mdsys.sdo_geometry, mdsys.sdo_geometry);
alter indextype spatial_index drop
sdo_nn(mdsys.st_geometry, mdsys.sdo_geometry);
alter indextype spatial_index drop
sdo_nn(mdsys.st_geometry, mdsys.st_geometry);
alter indextype spatial_index drop
sdo_nn(mdsys.sdo_geometry, mdsys.st_geometry);

alter indextype spatial_index drop
sdo_relate(mdsys.sdo_geometry, mdsys.sdo_geometry, varchar2);
alter indextype spatial_index drop
sdo_relate(mdsys.st_geometry, mdsys.sdo_geometry, varchar2);
alter indextype spatial_index drop
sdo_relate(mdsys.st_geometry, mdsys.st_geometry, varchar2);
alter indextype spatial_index drop
sdo_relate(mdsys.sdo_geometry, mdsys.st_geometry, varchar2);

alter indextype spatial_index drop
sdo_anyinteract(mdsys.sdo_geometry, mdsys.sdo_geometry);
alter indextype spatial_index drop
sdo_anyinteract(mdsys.st_geometry, mdsys.sdo_geometry);
alter indextype spatial_index drop
sdo_anyinteract(mdsys.st_geometry, mdsys.st_geometry);
alter indextype spatial_index drop
sdo_anyinteract(mdsys.sdo_geometry, mdsys.st_geometry);

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

alter indextype spatial_index drop
sdo_contains(mdsys.sdo_geometry, mdsys.sdo_geometry);
alter indextype spatial_index drop
sdo_contains(mdsys.st_geometry, mdsys.sdo_geometry);
alter indextype spatial_index drop
sdo_contains(mdsys.st_geometry, mdsys.st_geometry);
alter indextype spatial_index drop
sdo_contains(mdsys.sdo_geometry, mdsys.st_geometry);

declare
begin
   begin
    execute immediate
' alter indextype spatial_index drop
sdo_contains(mdsys.sdo_topo_geometry, mdsys.sdo_geometry) ';
    execute immediate
  ' alter indextype spatial_index drop
sdo_contains(mdsys.sdo_topo_geometry, mdsys.sdo_topo_geometry) ';
    execute immediate
  ' alter indextype spatial_index drop
sdo_contains(mdsys.sdo_topo_geometry, mdsys.sdo_topo_object_array) ' ;
      exception
       when others then return;
  end;
end;
/


alter indextype spatial_index drop
sdo_inside(mdsys.sdo_geometry, mdsys.sdo_geometry);
alter indextype spatial_index drop
sdo_inside(mdsys.st_geometry, mdsys.sdo_geometry);
alter indextype spatial_index drop
sdo_inside(mdsys.st_geometry, mdsys.st_geometry);
alter indextype spatial_index drop
sdo_inside(mdsys.sdo_geometry, mdsys.st_geometry);

declare
begin
   begin
    execute immediate 
  ' alter indextype spatial_index drop
sdo_inside(mdsys.sdo_topo_geometry, mdsys.sdo_geometry) ';
    execute immediate 
  ' alter indextype spatial_index drop
sdo_inside(mdsys.sdo_topo_geometry, mdsys.sdo_topo_geometry) ';
    execute immediate 
  ' alter indextype spatial_index drop
sdo_inside(mdsys.sdo_topo_geometry, mdsys.sdo_topo_object_array) ';
      exception
       when others then return;
  end;
end;
/


alter indextype spatial_index drop
sdo_touch(mdsys.sdo_geometry, mdsys.sdo_geometry);
alter indextype spatial_index drop
sdo_touch(mdsys.st_geometry, mdsys.sdo_geometry);
alter indextype spatial_index drop
sdo_touch(mdsys.st_geometry, mdsys.st_geometry);
alter indextype spatial_index drop
sdo_touch(mdsys.sdo_geometry, mdsys.st_geometry);

declare
begin
   begin
    execute immediate 
  ' alter indextype spatial_index drop
sdo_touch(mdsys.sdo_topo_geometry, mdsys.sdo_geometry) ';
    execute immediate 
 ' alter indextype spatial_index drop
sdo_touch(mdsys.sdo_topo_geometry, mdsys.sdo_topo_geometry) ';
    execute immediate 
  ' alter indextype spatial_index drop
sdo_touch(mdsys.sdo_topo_geometry, mdsys.sdo_topo_object_array) ';
      exception
       when others then return;
  end;
end;
/


alter indextype spatial_index drop
sdo_equal(mdsys.sdo_geometry, mdsys.sdo_geometry);
alter indextype spatial_index drop
sdo_equal(mdsys.st_geometry, mdsys.sdo_geometry);
alter indextype spatial_index drop
sdo_equal(mdsys.st_geometry, mdsys.st_geometry);
alter indextype spatial_index drop
sdo_equal(mdsys.sdo_geometry, mdsys.st_geometry);

declare
begin
   begin
    execute immediate 
  ' alter indextype spatial_index drop
sdo_equal(mdsys.sdo_topo_geometry, mdsys.sdo_geometry) ';
    execute immediate 
  ' alter indextype spatial_index drop
sdo_equal(mdsys.sdo_topo_geometry, mdsys.sdo_topo_geometry) ';
    execute immediate 
  ' alter indextype spatial_index drop
sdo_equal(mdsys.sdo_topo_geometry, mdsys.sdo_topo_object_array) ';
      exception
       when others then return;
  end;
end;
/


alter indextype spatial_index drop
sdo_covers(mdsys.sdo_geometry, mdsys.sdo_geometry);
alter indextype spatial_index drop
sdo_covers(mdsys.st_geometry, mdsys.sdo_geometry);
alter indextype spatial_index drop
sdo_covers(mdsys.st_geometry, mdsys.st_geometry);
alter indextype spatial_index drop
sdo_covers(mdsys.sdo_geometry, mdsys.st_geometry);

declare
begin
   begin
    execute immediate 
  ' alter indextype spatial_index drop
sdo_covers(mdsys.sdo_topo_geometry, mdsys.sdo_geometry) ';
    execute immediate 
  ' alter indextype spatial_index drop
sdo_covers(mdsys.sdo_topo_geometry, mdsys.sdo_topo_geometry) ';
    execute immediate 
  ' alter indextype spatial_index drop
sdo_covers(mdsys.sdo_topo_geometry, mdsys.sdo_topo_object_array) ';
      exception
       when others then return;
  end;
end;
/


alter indextype spatial_index drop
sdo_coveredby(mdsys.sdo_geometry, mdsys.sdo_geometry);
alter indextype spatial_index drop
sdo_coveredby(mdsys.st_geometry, mdsys.sdo_geometry);
alter indextype spatial_index drop
sdo_coveredby(mdsys.st_geometry, mdsys.st_geometry);
alter indextype spatial_index drop
sdo_coveredby(mdsys.sdo_geometry, mdsys.st_geometry);

declare
begin
   begin
    execute immediate 
  ' alter indextype spatial_index drop
sdo_coveredby(mdsys.sdo_topo_geometry, mdsys.sdo_geometry) ';
    execute immediate 
  ' alter indextype spatial_index drop
sdo_coveredby(mdsys.sdo_topo_geometry, mdsys.sdo_topo_geometry) ';
    execute immediate 
  ' alter indextype spatial_index drop
sdo_coveredby(mdsys.sdo_topo_geometry, mdsys.sdo_topo_object_array) ';
      exception
       when others then return;
  end;
end;
/


alter indextype spatial_index drop
sdo_overlaps(mdsys.sdo_geometry, mdsys.sdo_geometry);
alter indextype spatial_index drop
sdo_overlaps(mdsys.st_geometry, mdsys.sdo_geometry);
alter indextype spatial_index drop
sdo_overlaps(mdsys.st_geometry, mdsys.st_geometry);
alter indextype spatial_index drop
sdo_overlaps(mdsys.sdo_geometry, mdsys.st_geometry);

declare
begin
   begin
    execute immediate 
  ' alter indextype spatial_index drop
sdo_overlaps(mdsys.sdo_topo_geometry, mdsys.sdo_geometry) ';
    execute immediate 
  ' alter indextype spatial_index drop
sdo_overlaps(mdsys.sdo_topo_geometry, mdsys.sdo_topo_geometry) ';
    execute immediate 
  ' alter indextype spatial_index drop
sdo_overlaps(mdsys.sdo_topo_geometry, mdsys.sdo_topo_object_array) ';
      exception
       when others then return;
  end;
end;
/


alter indextype spatial_index drop
sdo_on(mdsys.sdo_geometry, mdsys.sdo_geometry);
alter indextype spatial_index drop
sdo_on(mdsys.st_geometry, mdsys.sdo_geometry);
alter indextype spatial_index drop
sdo_on(mdsys.st_geometry, mdsys.st_geometry);
alter indextype spatial_index drop
sdo_on(mdsys.sdo_geometry, mdsys.st_geometry);

declare
begin
   begin
    execute immediate 
   ' alter indextype spatial_index drop
sdo_on(mdsys.sdo_topo_geometry, mdsys.sdo_geometry) ';
    execute immediate 
  ' alter indextype spatial_index drop
sdo_on(mdsys.sdo_topo_geometry, mdsys.sdo_topo_geometry) ';
    execute immediate 
  ' alter indextype spatial_index drop
sdo_on(mdsys.sdo_topo_geometry, mdsys.sdo_topo_object_array) ';
      exception
       when others then return;
  end;
end;
/


alter indextype spatial_index drop
sdo_overlapbdydisjoint(mdsys.sdo_geometry, mdsys.sdo_geometry);
alter indextype spatial_index drop
sdo_overlapbdydisjoint(mdsys.st_geometry, mdsys.sdo_geometry);
alter indextype spatial_index drop
sdo_overlapbdydisjoint(mdsys.st_geometry, mdsys.st_geometry);
alter indextype spatial_index drop
sdo_overlapbdydisjoint(mdsys.sdo_geometry, mdsys.st_geometry);

declare
begin
   begin
    execute immediate 
  ' alter indextype spatial_index drop
sdo_overlapbdydisjoint(mdsys.sdo_topo_geometry, mdsys.sdo_geometry) ';
    execute immediate 
  ' alter indextype spatial_index drop
sdo_overlapbdydisjoint(mdsys.sdo_topo_geometry, mdsys.sdo_topo_geometry) ';
    execute immediate 
  ' alter indextype spatial_index drop
sdo_overlapbdydisjoint(mdsys.sdo_topo_geometry, mdsys.sdo_topo_object_array) ';
      exception
       when others then return;
  end;
end;
/


alter indextype spatial_index drop
sdo_overlapbdyintersect(mdsys.sdo_geometry, mdsys.sdo_geometry);
alter indextype spatial_index drop
sdo_overlapbdyintersect(mdsys.st_geometry, mdsys.sdo_geometry);
alter indextype spatial_index drop
sdo_overlapbdyintersect(mdsys.st_geometry, mdsys.st_geometry);
alter indextype spatial_index drop
sdo_overlapbdyintersect(mdsys.sdo_geometry, mdsys.st_geometry);

declare
begin
   begin
    execute immediate 
  ' alter indextype spatial_index drop
sdo_overlapbdyintersect(mdsys.sdo_topo_geometry, mdsys.sdo_geometry) ';
    execute immediate 
  ' alter indextype spatial_index drop
sdo_overlapbdyintersect(mdsys.sdo_topo_geometry, mdsys.sdo_topo_geometry) ';
    execute immediate 
  ' alter indextype spatial_index drop
sdo_overlapbdyintersect(mdsys.sdo_topo_geometry, mdsys.sdo_topo_object_array) ';
      exception
       when others then return;
  end;
end;
/


alter indextype spatial_index drop
sdo_filter(mdsys.sdo_geometry, mdsys.sdo_geometry, varchar2);
alter indextype spatial_index drop
sdo_filter(mdsys.sdo_geometry, mdsys.sdo_geometry);
alter indextype spatial_index drop
sdo_filter(mdsys.st_geometry, mdsys.sdo_geometry);
alter indextype spatial_index drop
sdo_filter(mdsys.st_geometry, mdsys.st_geometry);
alter indextype spatial_index drop
sdo_filter(mdsys.sdo_geometry, mdsys.st_geometry);

declare
begin
   begin
    execute immediate 
   ' alter indextype spatial_index drop
sdo_filter(mdsys.sdo_topo_geometry, mdsys.sdo_topo_geometry) ';
    execute immediate 
  ' alter indextype spatial_index drop
sdo_filter(mdsys.sdo_topo_geometry, mdsys.sdo_geometry) ';
    execute immediate 
  ' alter indextype spatial_index drop
sdo_filter(mdsys.sdo_topo_geometry, mdsys.sdo_topo_object_array) ';
      exception
       when others then return;
  end;
end;
/


alter indextype spatial_index drop
sdo_rtree_relate(mdsys.sdo_geometry, mdsys.sdo_geometry, varchar2);
alter indextype spatial_index drop
sdo_rtree_filter(mdsys.sdo_geometry, mdsys.sdo_geometry, varchar2);

alter indextype spatial_index drop
sdo_int_relate(mdsys.sdo_geometry, mdsys.sdo_geometry, varchar2, varchar2, 
               varchar2, varchar2,number,number, number);
alter indextype spatial_index drop
sdo_int_filter(mdsys.sdo_geometry, mdsys.sdo_geometry, varchar2, varchar2, 
               varchar2, varchar2,number,number, number);
alter indextype spatial_index drop
sdo_within_distance(mdsys.sdo_geometry, mdsys.sdo_geometry, varchar2);
alter indextype spatial_index drop
sdo_within_distance(mdsys.st_geometry, mdsys.sdo_geometry, varchar2);
alter indextype spatial_index drop
sdo_within_distance(mdsys.st_geometry, mdsys.st_geometry, varchar2);
alter indextype spatial_index drop
sdo_within_distance(mdsys.sdo_geometry, mdsys.st_geometry, varchar2);
alter indextype spatial_index drop
locator_within_distance(mdsys.sdo_geometry, mdsys.sdo_geometry, varchar2);
alter indextype spatial_index drop
sdo_int2_relate(mdsys.sdo_geometry, mdsys.sdo_geometry, varchar2);
alter indextype spatial_index drop
sdo_int2_filter(mdsys.sdo_geometry, mdsys.sdo_geometry, varchar2) ;



drop operator sdo_nn force;
drop operator sdo_nn_distance force;
drop operator sdo_filter force;
drop operator sdo_int2_filter force;
drop operator sdo_int_filter force;
drop operator sdo_relate force;
drop operator sdo_int2_relate force;
drop operator sdo_int_relate force;
drop operator sdo_rtree_filter force;
drop operator sdo_rtree_relate force;
drop operator sdo_within_distance force;
drop operator locator_within_distance force;
drop operator sdo_anyinteract force;
drop operator sdo_contains force;
drop operator sdo_inside force;
drop operator sdo_touch force;
drop operator sdo_equal force;
drop operator sdo_covers force;
drop operator sdo_on force;
drop operator sdo_coveredby force;
drop operator sdo_overlapbdydisjoint force;
drop operator sdo_overlapbdyintersect force;
drop operator sdo_overlaps force;

drop type SDO_INDEX_METHOD_10I;