Rem
Rem $Header: sdo/admin/sdoewfcs.sql /main/8 2009/02/14 11:21:44 sravada Exp $
Rem
Rem sdoewfcs.sql
Rem
Rem Copyright (c) 2006, 2009, Oracle and/or its affiliates.
Rem All rights reserved. 
Rem
Rem    NAME
Rem      sdoewfcs.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    sravada     02/11/09 - change to SYS.DBMS_ASSERT
Rem    rchatter    05/02/07 - fix lrg 2922480
Rem    rchatter    03/28/06 - Created
Rem


-- to be invoked from sdoe102.sql ( for downgrade )
alter session set current_schema=MDSYS;

drop table MDSYS.WFS_FeatureTypeTags$;
drop table MDSYS.WFS_FeatureInstanceMetadata$;
drop table MDSYS.WFS_CapabilitiesInfo$;
drop table MDSYS.WFS_FeatureTypeAttrs$;
drop table MDSYS.WFS_FeatureTypeComplexTags$;
drop table MDSYS.WFS_FeatureTypeSimpleTagAttrs$;
drop table MDSYS.WFS_FtTypeComplexTagAttrs$;
drop table MDSYS.WFS_FeatureTypeXMLCOLINFO$;
drop table MDSYS.WFS_FeatureTypeNestedSDOs$;
drop table mdsys.WFS_featureTableMDUpdated$ ;
drop table mdsys.WFS_featureTableUpdated$ ;
drop table mdsys.wfs_trg_table_temp$ ;
-- drop all the metadata related to doc based feature types 
ALTER SESSION SET CURRENT_SCHEMA = SYS;
declare
CURSOR C1 IS SELECT t.featureTypeId FROM mdsys.WFS_FeatureType$ t where t.iSDOCBASED  = 'Y';
CURSOR C2 IS SELECT t.PACKAGENAME FROM mdsys.WFS_PROC_MAP$ t ;
CURSOR C3 IS select table_name from  all_tables where owner = 'MDSYS' and table_name like 'WFS_FT_%_CL%$' ;
l_featureTypeId number;
l_packageName VARCHAR2(30);
dmlStr varchar2(32700);
l_tabName varchar2(30);
begin
  OPEN C3;
        LOOP
        FETCH C3 INTO l_tabName;
        EXIT WHEN C3%NOTFOUND;

	begin
	dmlStr := 'begin   delete from mdsys.sdo_geom_metadata_table where sdo_owner = ''MDSYS'' and sdo_table_name = '||SYS.DBMS_ASSERT.enquote_literal(l_tabName)||'; exception when others then null; end;';
        execute immediate dmlStr;
        exception when others then null; end;
	begin
                dmlStr := ' drop table mdsys.'|| l_tabName;
        execute immediate dmlStr;
        exception when others then null; end;
	end loop;
  CLOSE C3;


  OPEN C1;
        LOOP
        FETCH C1 INTO l_featureTypeId;
        EXIT WHEN C1%NOTFOUND;
        begin
        dmlStr := 'begin   delete from mdsys.sdo_geom_metadata_table where sdo_owner = ''MDSYS'' and sdo_table_name = ''WFS_FT_'|| l_featureTypeId ||'$''; exception when others then null; end;';
        execute immediate dmlStr;
        exception when others then null; end;

        begin
                dmlStr := ' drop table mdsys.wfs_ft_'|| l_featureTypeId ||'$';
        execute immediate dmlStr;
        exception when others then null; end;

        begin
                dmlStr := ' drop sequence mdsys.ft_'|| l_featureTypeId ||'_sq$';
        execute immediate dmlStr;
        exception when others then null; end;
        begin
                dmlStr := 'drop role wfs_typerole_'|| l_featureTypeId;
        execute immediate dmlStr;
        exception when others then null; end;
        begin
        dmlStr := ' drop package mdsys.wfs_FT_'||l_featureTypeId||'_proc';
        execute immediate dmlStr;
        exception when others then null; end;
        END LOOP;
  CLOSE C1;
  commit;
  OPEN C2;
        LOOP
        FETCH C2 INTO l_packageName;
        EXIT WHEN C2%NOTFOUND;
        begin
        dmlStr := ' drop package '||l_packageName;
        execute immediate dmlStr;
        exception when others then null; end;
        END LOOP;
  CLOSE C2;

end;
/
ALTER SESSION SET CURRENT_SCHEMA = MDSYS;

drop table MDSYS.WFS_PROC_MAP$;
drop table MDSYS.WFS_FeatureType$;
drop table mdsys.WFS_FTXSDINFO$;
drop view mdsys.RowTokenMap$;
drop view mdsys.TokenSessionMap$;
drop table mdsys.RowTokenMap_t$;
drop table mdsys.TokenSessionMap_t$;
drop view mdsys.CurrentSessionTokenMap$;
drop table mdsys.CurrentSessionTokenMap_t$;
drop sequence mdsys.ft_sq$;
drop sequence mdsys.xmlinfo_sq$;
drop sequence mdsys.wfs_proc_sq$;
drop sequence mdsys.token_sq$;

alter session set current_schema=SYS;
-- drop role spatial_wfs_admin;
-- drop role wfs_usr_role;
drop user spatial_wfs_admin_usr;

alter session set current_schema=MDSYS;
drop table MDSYS.CSW_rtXSDInfo$;
drop table mdsys.CSW_RECORDVIEWMAP$;
drop table mdsys.CSW_RECORDDOMAINUPDATED$;
drop table mdsys.CSW_RECORDTABLEUPDATED$;
drop table mdsys.CSW_RECORDTABLEMDUPDATED$;
drop table mdsys.CSW_HarvestMetadata$;
drop table mdsys.CSW_CapabilitiesInfo$;
drop table mdsys.CSW_DomainInfo$;
drop table MDSYS.CSW_TRG_TABLE_TEMP$;
drop table MDSYS.CSW_PLUGIN_MAP$;

ALTER SESSION SET CURRENT_SCHEMA = SYS;
-- drop all the record type related metadata before dropping MDSYS.CSW_Record_Types$; 
declare
CURSOR C1 IS SELECT t.recordTypeId FROM mdsys.CSW_Record_Types$ t;
CURSOR C2 IS select table_name from all_tables where owner = 'MDSYS' and table_name like 'CSW_RT_%_CL%$' ;
l_recordTypeId number;
l_tabName varchar2(30);
dmlStr varchar2(4000);
begin
  OPEN C2;  
        LOOP
        FETCH C2 INTO l_tabName;
        EXIT WHEN C2%NOTFOUND;
        begin
        dmlStr := 'begin   delete from mdsys.sdo_geom_metadata_table where sdo_owner = ''MDSYS'' and sdo_table_name = '||SYS.DBMS_ASSERT.enquote_literal(l_tabName)||'; exception when others then null; end;';
        execute immediate dmlStr;
        exception when others then null; end;
        begin
                dmlStr := ' drop table mdsys.'|| l_tabName;
        execute immediate dmlStr;
        exception when others then null; end;
	end loop;
  CLOSE C2;

  OPEN C1;
        LOOP
        FETCH C1 INTO l_recordTypeId;
        EXIT WHEN C1%NOTFOUND;
        begin
        dmlStr := 'begin  delete from mdsys.sdo_geom_metadata_table where sdo_owner = ''MDSYS'' and sdo_table_name = ''CSW_RT_'|| l_recordTypeId ||'$''; exception when others then null; end;';
        execute immediate dmlStr;
        exception when others then null; end;
        begin
                dmlStr := 'drop table mdsys.csw_rt_'|| l_recordTypeId ||'$';
        execute immediate dmlStr;
        exception when others then null; end;
        begin
        dmlStr := ' drop role csw_typerole_'|| l_recordTypeId ;
        execute immediate dmlStr;
        exception when others then null; end;
        END LOOP;
  CLOSE C1;

end; 
/



ALTER SESSION SET CURRENT_SCHEMA = MDSYS;
drop table MDSYS.CSW_Record_Types$;
drop sequence mdsys.rt_sq$;
drop type mdsys.sdo_geom_path_info_list;
drop type mdsys.sdo_geom_path_info_elem;
drop type mdsys.sdo_geom_path_info;
drop type mdsys.sdo_geom_path_elem;
drop type mdsys.WfscomplexType;
drop type mdsys.RowPointerList;
drop type mdsys.StringListList;
drop type mdsys.StringList;
drop type mdsys.SDONUMLIST;

alter session set current_schema=SYS;
-- drop role spatial_csw_admin;
-- drop role csw_usr_role;
drop user spatial_csw_admin_usr;

alter session set current_schema=MDSYS;
drop package MDSYS.SDO_WS_PROCESS;
drop package MDSYS.SDO_WFS_PROCESS;
drop package MDSYS.SDO_WFS_PROCESS_UTIL;
drop package MDSYS.SDO_WFS_LOCK;
drop package MDSYS.SDO_WFS_LOCK_UTIL;
drop package MDSYS.SDO_WFS_LOCK_GEN;
drop package MDSYS.SDO_CSW_PROCESS;
alter session set current_schema=MDSYS;