Rem
Rem $Header: sdo/admin/loce102.sql /main/5 2009/09/03 13:08:27 sravada Exp $
Rem
Rem loce102.sql
Rem
Rem Copyright (c) 2007, 2009, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      loce102.sql - Locator downgrade 
Rem
Rem    DESCRIPTION
Rem      This is called from interMedia downgrade, if SDO is not
Rem       present, else it is called from sdoe102.sql
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    sravada     08/20/09 - bug 8740141
Rem    sravada     02/14/08 - 
Rem    mhorhamm    11/28/07 - Truncate sdo_cs_srs
Rem    sravada     05/11/07 - Created
Rem


ALTER SESSION SET CURRENT_SCHEMA = MDSYS;

-- downgrade Locator to 11.1.0.6 only if SDO is not installed
-- if SDO is installed sdoe111.sql is called from SDO downgrade

COLUMN :script_name NEW_VALUE comp_file NOPRINT
Variable script_name varchar2(50)
Variable sdo_cnt  number;

declare
  sdo_status VARCHAR2(20) := NULL;
begin
  -- Check whether SDO is installed.
  select count(*) into :sdo_cnt from all_objects where owner='MDSYS' and
  object_name = 'SDO_GEOMETRY';
  sdo_status := dbms_registry.status('SDO');
  if (:sdo_cnt = 0) then
    :script_name := '?/rdbms/admin/nothing.sql';
  elsif (sdo_status is NULL or sdo_status = 'OPTION OFF') then
    :script_name := '@loce111.sql';
  else
    :script_name := '?/rdbms/admin/nothing.sql';
  end if;
end;
/

select :script_name from dual;
@&comp_file


-- For EPSG

alter table mdsys.sdo_units_of_measure modify(unit_of_meas_name varchar2(80));

declare
begin
  execute immediate
  ' drop procedure reset_inprog_index';
  exception when others then null;
end;
/

declare
begin
   execute immediate
    ' DROP package sdoCurPkg';
   exception when others then NULL;
end;
/

declare
begin
  begin
    execute immediate
     'alter table mdsys.sdo_units_of_measure drop constraint uom_unique1';
    exception when others then null;
  end;
  begin
    execute immediate
      'alter table mdsys.sdo_units_of_measure drop constraint uom_type_constr';
    exception when others then null;
  end;
  begin
    execute immediate
 'alter table mdsys.sdo_units_of_measure drop constraint only_std_target_uoms';
    exception when others then null;
  end;
  begin
    execute immediate
 'alter table mdsys.sdo_units_of_measure drop constraint FACTOR_B_MUST_BE_POS';
    exception when others then null;
  end;
  begin
    execute immediate
 'alter table mdsys.sdo_units_of_measure drop constraint FACTOR_C_MUST_BE_POS';
    exception when others then null;
  end;

end;
/


drop trigger mdsys.SDO_UNITS_OF_MEASURE_TRIGGER;
drop trigger mdsys.SDO_coord_op_param_val_TRIGGER;
drop trigger mdsys.SDO_coord_op_param_val_TRIGG2;

drop trigger mdsys.cs_srs_trigger;
drop trigger mdsys.sdo_crs_insert_trigger;
drop trigger mdsys.sdo_crs_delete_trigger;
select count(*) from mdsys.sdo_cs_srs;
truncate table mdsys.sdo_cs_srs;

-- ST_ANNOTATION TEXT
drop type body ST_ANNOTATION_TEXT;
drop type body ST_ANNOTATIONTEXTELEMENT_ARRAY;
drop type body ST_ANNOTATIONTEXTELEMENT;
drop trigger SDO_ANNOT_TRIG_INS1;
drop public synonym ALL_ANNOTATION_TEXT_METADATA;
drop public synonym USER_ANNOTATION_TEXT_METADATA;
drop view MDSYS.USER_ANNOTATION_TEXT_METADATA;
drop view MDSYS.ALL_ANNOTATION_TEXT_METADATA;


declare
begin
   begin
       EXECUTE IMMEDIATE
  'ALTER TYPE ST_GEOMETRY DROP MEMBER FUNCTION ST_SymDifference(g2 ST_Geometry)
              RETURN ST_Geometry DETERMINISTIC CASCADE';
    EXECUTE IMMEDIATE
  'ALTER TYPE ST_GEOMETRY DROP  MEMBER FUNCTION ST_Touches(g2 ST_Geometry) RETURN Integer DETERMINISTIC CASCADE';
    EXECUTE IMMEDIATE
  'ALTER TYPE ST_GEOMETRY DROP MEMBER FUNCTION ST_Crosses(g2 ST_Geometry) RETURN Integer DETERMINISTIC CASCADE ';
    EXECUTE IMMEDIATE
  'ALTER TYPE ST_GEOMETRY DROP MEMBER FUNCTION ST_GetTolerance RETURN NUMBER DETERMINISTIC CASCADE ';
 exception when others then null;
 end;
end;
/

declare
begin
  begin
    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 ' ||
  ' Constructor Function SDO_Topo_Geometry(TG_ID number, topology varchar2,
                                    TG_Type number,
                                    TG_layer_id NUMBER,
                                    Topo_ids SDO_TGL_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_TGL_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_TGL_OBJECT_ARRAY,
                                    delete_Topo_ids SDO_TGL_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_TGL_OBJECT_ARRAY,
                                    delete_Topo_ids SDO_TGL_OBJECT_ARRAY)
     return SELF as RESULT DETERMINISTIC CASCADE ';

    execute immediate
  ' alter type sdo_topo_geometry drop ' ||
  ' MAP MEMBER FUNCTION to_string  RETURN VARCHAR2 CASCADE ';

    exception when others then
    return;
 end;
end;
/
SHOW errors;

-- Remove MATCHVECTOR from sdo_geo_addr
begin
   begin
     execute immediate 'alter type sdo_geo_addr drop attribute(matchVector) cascade';
   exception
       WHEN others then NULL;
   end;

   begin
    execute immediate ' alter type sdo_geo_addr drop Constructor
  FUNCTION SDO_GEO_ADDR( id                  NUMBER,
 addresslines        SDO_KEYWORDARRAY, placeName           VARCHAR2,
 streetName          VARCHAR2, intersectStreet     VARCHAR2,
 secUnit             VARCHAR2, settlement          VARCHAR2,
 municipality        VARCHAR2, region              VARCHAR2,
 country             VARCHAR2, postalCode          VARCHAR2,
 postalAddonCode     VARCHAR2, fullPostalCode      VARCHAR2,
 poBox               VARCHAR2, houseNumber         VARCHAR2,
 baseName            VARCHAR2, streetType          VARCHAR2,
 streetTypeBefore    VARCHAR2,
 streetTypeAttached  VARCHAR2, streetPrefix        VARCHAR2,
 streetSuffix        VARCHAR2, side                VARCHAR2,
 percent             NUMBER, edgeId              NUMBER,
 errorMessage        VARCHAR2, matchcode           NUMBER,
 matchmode VARCHAR2, longitude  NUMBER, latitude   NUMBER)
  RETURN SELF as result cascade ';
 exception
       WHEN others then NULL;
   end;


end;
/



ALTER SESSION SET CURRENT_SCHEMA = SYS;