Rem
Rem $Header: sdo/admin/deinssdo_only.sql /main/1 2009/03/11 13:28:43 sravada Exp $
Rem
Rem deinssdo_only.sql
Rem
Rem Copyright (c) 2009, Oracle and/or its affiliates.All rights reserved. 
Rem
Rem    NAME
Rem      deinssdo_only.sql - Drop only package bodies and non type related
Rem                          SDO objects to leave Locator in valid state
Rem
Rem    DESCRIPTION
Rem     This only drops the non Locator related packages to leave
Rem      Locator in a valid state 
Rem
Rem    NOTES
Rem      Do not invoke directly
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    sravada     03/11/09 - Created
Rem


declare
stmt varchar2(1000);
obj_name varchar2(64);
type                    cursor_type is REF CURSOR;
query_crs               cursor_type ;
begin
                                                                                
  stmt := ' select SNAME from sys.synonyms ' ||
          ' where creator=''MDSYS'' and SYNTYPE=''PUBLIC'' '||
          ' and TABTYPE<> ''JAVA CLASS'' ';
                                                                            
      OPEN query_crs FOR stmt;
  LOOP
    fetch query_crs into  obj_name;
    EXIT when query_crs%NOTFOUND ;
                                                                                
    begin
       EXECUTE immediate ' drop public synonym '||obj_name;
     EXCEPTION
     WHEN OTHERS THEN
      obj_name := ' ';
    end;
   END LOOP;
  close query_crs;
end;
/
                                                                                
commit;

 -- TRIGGER
declare
stmt varchar2(1000);
obj_name varchar2(64);
type                    cursor_type is REF CURSOR;
query_crs               cursor_type ;
begin
   stmt :=  ' select object_name from all_objects where  ' ||
            ' object_type = ''TRIGGER'' and owner = ''MDSYS'' ';

      OPEN query_crs FOR stmt;
  LOOP
    fetch query_crs into  obj_name;
    EXIT when query_crs%NOTFOUND ;

    begin
       EXECUTE immediate ' drop TRIGGER mdsys.'||obj_name;
     EXCEPTION
     WHEN OTHERS THEN
      obj_name := ' ';
    end;
   END LOOP;
  close query_crs;
commit;

-- JAVA CLASS
   stmt :=  ' select object_name from all_objects where  ' ||
            ' object_type = ''JAVA CLASS'' and owner = ''MDSYS'' ';

      OPEN query_crs FOR stmt;
  LOOP
    fetch query_crs into  obj_name;
    EXIT when query_crs%NOTFOUND ;

    begin
       EXECUTE immediate ' drop JAVA CLASS mdsys."'||obj_name||'" ';
       commit;
     EXCEPTION
     WHEN OTHERS THEN
      obj_name := ' ';
    end;
   END LOOP;
  close query_crs;
commit;



-- PACKAGE
   stmt :=  ' select object_name from all_objects where  ' ||
            ' object_type = ''PACKAGE BODY'' and owner = ''MDSYS'' ';

      OPEN query_crs FOR stmt;
  LOOP
    fetch query_crs into  obj_name;
    EXIT when query_crs%NOTFOUND ;

    begin
       EXECUTE immediate ' drop PACKAGE BODY mdsys.'||obj_name;
     EXCEPTION
     WHEN OTHERS THEN
      obj_name := ' ';
    end;
   END LOOP;
  close query_crs;
commit;

-- PROCEDURE
   stmt :=  ' select object_name from all_objects where  ' ||
            ' object_type = ''PROCEDURE'' and owner = ''MDSYS'' ';

      OPEN query_crs FOR stmt;
  LOOP
    fetch query_crs into  obj_name;
    EXIT when query_crs%NOTFOUND ;

    begin
       EXECUTE immediate ' drop PROCEDURE mdsys.'||obj_name;
     EXCEPTION
     WHEN OTHERS THEN
      obj_name := ' ';
    end;
   END LOOP;
  close query_crs;
commit;

-- SEQUENCE
   stmt :=  ' select object_name from all_objects where  ' ||
            ' object_type = ''SEQUENCE'' and owner = ''MDSYS'' ';

      OPEN query_crs FOR stmt;
  LOOP
    fetch query_crs into  obj_name;
    EXIT when query_crs%NOTFOUND ;

    begin
       EXECUTE immediate ' drop SEQUENCE mdsys.'||obj_name;
     EXCEPTION
     WHEN OTHERS THEN
      obj_name := ' ';
    end;
   END LOOP;
  close query_crs;
commit;

-- SYNONYMS
  stmt := ' select SNAME from sys.synonyms ' ||
          ' where creator=''MDSYS'' and SYNTYPE=''PUBLIC'' ';

   OPEN query_crs FOR stmt;
  LOOP 
  BEGIN
    fetch query_crs into  obj_name;
    EXIT when query_crs%NOTFOUND ;

    begin
      EXECUTE immediate ' drop public synonym '||obj_name;
     EXCEPTION 
     WHEN OTHERS THEN 
      obj_name := ' ';
    end;

  end;
  END LOOP;
  close query_crs;


end;
/



drop type body MDSYS.SDO_TOPO_GEOMETRY;
drop package MDSYS.SDO_TOPO;
drop package MDSYS.SDO_TOPO_MAP;
drop package MDSYS.SDO_GEOR;
drop package MDSYS.SDO_GEOR_INT;
drop package MDSYS.SDO_GEOR_DEF;
drop package MDSYS.SDO_GEOR_AUX;
drop package MDSYS.SDO_GEORX;
drop package MDSYS.SDO_GEOR_ADMIN;
drop package MDSYS.SDO_GEOR_UTL;
drop package MDSYS.SDO_GCDR;
drop package MDSYS.SDO_NET;
drop package MDSYS.MD_NET;
drop type body  MDSYS.SDO_NETWORK_MANAGER_I;
drop type body MDSYS.SDO_NODE_I;
drop type body MDSYS.SDO_LINK_I;
drop type body MDSYS.SDO_PATH_I;
drop type body MDSYS.SDO_NETWORK_I;
drop type body MDSYS.SAMCLUST_IMP_T;
drop package MDSYS.SDO_NET_MEM;
drop package MDSYS.SDO_SAM;
drop function MDSYS.SPCLUSTERS;
drop package MDSYS.PRVT_SAM;
drop package MDSYS.SDO_WFS_PROCESS;
drop package MDSYS.SDO_CSW_PROCESS;
drop package MDSYS.SDO_PC_PKG;
drop package MDSYS.PRVT_PC;
drop package MDSYS.SDO_TIN_PKG;
drop package MDSYS.PRVT_TIN;