--------------- start of sdo_deinst.sql ---------------------------
set echo on
col comp_name format a25
col version format a25
col status format a15
select comp_name, version, status
from dba_registry
where comp_name='Spatial';

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;


col comp_name format a25
col version format a25
col status format a15
select comp_name, version, status
from dba_registry
where comp_name='Spatial';

-- Spatial still VALID?

-- Reload Locator:

alter session set current_schema=MDSYS;
start ?/md/admin/catmdloc.sql

col comp_name format a25
col version format a25
col status format a15
select comp_name, version, status
from dba_registry
where comp_name='Spatial';

-- List invalids

select owner, object_name, object_type
from dba_objects
where status='INVALID'
and owner='MDSYS';

-- Mark SDO as Removed

EXECUTE dbms_registry.removed('SDO');
alter session set current_schema=SYS;
set echo off
------------- End of sdo_deinst.sql ------------