REM
REM NAME
REM    deinssdo.sql
REM
REM DESCRIPTION
REM
REM	This file removes the packages, types and operators which
REM     are specific to SDO.
REM NOTES
REM  This script must be run by MDSYS.
REM 
REM MODIFIED    (DD-MON-YY)   DESCRIPTION
REM  sravada     29-MAY-98     Fixed for 10g
REM  gvincent    29-MAY-98     File created
REM
REM
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;


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 = ''FUNCTION'' and owner = ''MDSYS'' ';

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

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

commit;
 -- TRIGGER
   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;

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

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

    begin
       EXECUTE immediate ' drop OPERATOR mdsys.'||obj_name||' force ';
     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;

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

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

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

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

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

    begin
       EXECUTE immediate ' drop INDEXTYPE mdsys.'||obj_name||' force ';
     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'' 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 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;

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

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

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

  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 user MDSYS cascade;