Rem
Rem $Header: sdo/admin/semremov.sql /st_sdo_11.2.0/3 2011/04/29 12:19:52 sdas Exp $
Rem
Rem semremov.sql
Rem
Rem Copyright (c) 2009, 2011, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      semremov.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    sdas        03/24/11 - drop RDF_PARAM$ view and _TBL table
Rem    matperry    12/03/10 - update for refactored semgeo packages
Rem    sdas        11/22/10 - XbranchMerge sdas_ng_11.2.0.2.0_11.2.0.3.0 from
Rem                           st_sdo_11.2.0
Rem    sdas        11/22/10 - XbranchMerge sdas_ng_11.2.0.2.0 from
Rem    matperry    08/25/10 - remove SEM_GRAPHS type
Rem    vkolovsk    07/29/10 - XbranchMerge
Rem                           vkolovsk_rdf_ols_set_triple_label_11.2.0.2.0 from
Rem                           st_sdo_11.2.0
Rem    vkolovsk    06/10/10 - remove sem_inf_internal
Rem    vkolovsk    05/04/10 - add session-specific application context
Rem    matperry    04/02/10 - update for optional RDFGEO component
Rem    vkolovsk    11/03/09 - moved sdosemsam type creation and removal to
Rem                           sdordfty and semremov
Rem    matperry    05/13/09 - change behavior to drop force
Rem    matperry    04/23/09 - copied from seme102
Rem    matperry    04/23/09 - Created
Rem

-------------------------------------------------------------------------------------------
-- Each SERVER component has a "remov" script, mycremov.sql where 'MYC' is the component --
-- abbreviation. The removal script drops all of the component's objects and schemas     --
-- from the database.                                                                    --
--                                                                                       --
-- In 11g, there is a script, catcmprm.sql, that takes a single parameter, the component --
-- ID of the component to be removed. If the component exists in the database and has no --
-- dependent components in the database, then the relevant component removal script is   --
-- invoked. The catcmprm.sql script will use the dependencies set by the                 --
-- dbms_registry.set_required_comps procedure and the component removal script will only --
-- be called if there are no existing dependencies. If your removal script is going to   --
-- drop the entire schema you do not need to call dbms_registry interfaces because the   --
-- entry in the component registry is deleted when the component schema is dropped.      --
-------------------------------------------------------------------------------------------

ALTER SESSION SET CURRENT_SCHEMA = MDSYS;

-- make sure we don't fail --
whenever sqlerror continue;

-- PL/SQL block to raise errors
declare
  network_exists     NUMBER;
begin

  -- first try to remove any semantic data using drop_sem_network cascade --
  select count(*) into network_exists
  from all_objects
  where object_name = 'RDF_VALUE$' and owner = 'MDSYS';

  if (network_exists > 0) then
    execute immediate
      'begin
         sem_apis.drop_sem_network(true);
       end;';
  end if;

  -- see if drop_network was successful --
  select count(*) into network_exists
  from all_objects
  where object_name = 'RDF_VALUE$' and owner = 'MDSYS';

  if (network_exists > 0) then
    raise_application_error(-20000, 
      'Cannot remove component: a semantic network exists');
  end if;

end;
/
show errors;

-- PL/SQL blocks to do work

-- first uninstall OLS / VPD / CTX / WM optional components --
COLUMN :script_name1 NEW_VALUE comp_file1 NOPRINT
VARIABLE script_name1 VARCHAR2(30)

COLUMN :script_name2 NEW_VALUE comp_file2 NOPRINT
VARIABLE script_name2 VARCHAR2(30)

COLUMN :script_name3 NEW_VALUE comp_file3 NOPRINT
VARIABLE script_name3 VARCHAR2(30)

declare
  network_exists  number := 0;
  refcount        number := 0;
begin

  select count(*) into network_exists
  from all_objects
  where object_name = 'RDF_VALUE$' and owner = 'MDSYS';

  if (network_exists > 0) then
    :script_name1 := dbms_registry.nothing_script;
    :script_name2 := dbms_registry.nothing_script;
    :script_name3 := dbms_registry.nothing_script;
  else

    refcount := 0;
    begin
      execute immediate
        'select count(*) from mdsys.rdf_parameter
	 where namespace = ''COMPONENT'' and attribute = ''RDFCTX'''
      into refcount;
      exception when others then null;
    end;
    if (refcount > 0) then 
      :script_name1 := '@sdordfctx_rm.sql';
    else
      :script_name1 := dbms_registry.nothing_script;
    end if; 

    refcount := 0;
    begin
      execute immediate
        'select count(*) from mdsys.rdf_parameter
	 where namespace = ''COMPONENT'' and attribute = ''RDFOWM'''
      into refcount;
      exception when others then null;
    end;
    if (refcount > 0) then 
      :script_name2 := '@sdordfwm_rm.sql';
    else
      :script_name2 := dbms_registry.nothing_script;
    end if;

    refcount := 0;
    begin
      execute immediate
        'select count(*) from mdsys.rdf_parameter
	 where namespace = ''COMPONENT'' and attribute in (''RDFVPD'', ''RDFOLS'')'
      into refcount;
      exception when others then null;
    end;
    if (refcount > 0) then 
      :script_name3 := '@sdordfsa_rm.sql';
    else
      :script_name3 := dbms_registry.nothing_script;
    end if; 

  end if;

end;
/
show errors;

SELECT :script_name1 FROM DUAL;
@&comp_file1
SELECT :script_name2 FROM DUAL;
@&comp_file2
SELECT :script_name3 FROM DUAL;
@&comp_file3

-- make sure schema is set to MDSYS --
ALTER SESSION SET CURRENT_SCHEMA = MDSYS;

-- get rid of standard semantics components --
COLUMN :jscript NEW_VALUE comp_file4 NOPRINT
VARIABLE jscript VARCHAR2(50)

declare
  stmt           VARCHAR2(1000);
  network_exists NUMBER;
begin

  -- only continue if no network exists
  select count(*) into network_exists
  from all_objects
  where object_name = 'RDF_VALUE$' and owner = 'MDSYS';

  if (network_exists = 0) then
    -- no network ... ok to remove 

    -- get java removal script
    :jscript := '@semremovj.sql';

    -- VPD / OLS / WM / CTX Types and Tables and packages --
    begin
      stmt := 'drop package mdsys.sem_rdfsa_ir'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop procedure mdsys.RDF$GRANT_OLS_PRIVS'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop type mdsys.rdfsa_resource force'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop type mdsys.rdfsa_labelgen force'; execute immediate stmt;
      exception when others then null;
    end;

    -- drop RDF_PARAMETER table, RDF_PARAM$ view and _TBL table
    begin
      stmt := 'drop table mdsys.rdf_parameter'; execute immediate stmt;
      stmt := 'drop view mdsys.rdf_param$'; execute immediate stmt;
      stmt := 'drop table mdsys.rdf_param$_tbl'; execute immediate stmt;
      exception when others then null;
    end;

    -- delete RDF exp/imp related rows and drop SDO_RDF_EXO_IMP package --
    begin
      stmt := '
        delete from SYS.EXPPKGACT$ 
         where package=''SDO_RDF_EXP_IMP'' and schema=''SYS''';
      execute immediate stmt;

      stmt := '
        delete from SYS.impcalloutreg$ 
         where package=''SDO_RDF_EXP_IMP'' and schema=''SYS''
           and tag=''SEMANTIC''';
      execute immediate stmt;

      stmt := 'drop package SYS.SDO_RDF_EXP_IMP';
      execute immediate stmt;

      exception when others then null;
    end;

    -- Standard Semantics types, tables and packages --
    begin
       stmt := 'drop type sdo_sem_stats force'; execute immediate stmt;
       exception when others then null;
     end;

    begin
       stmt := 'drop indextype sem_indextype force';  execute immediate stmt;
       exception when others then null;
     end;

    begin
       stmt := 'drop public synonym sem_path'; execute immediate stmt;
       exception when others then null;
     end;

    begin
       stmt := 'drop operator sem_path force'; execute immediate stmt;
       exception when others then null;
     end;

    begin
       stmt := 'drop public synonym sem_distance'; execute immediate stmt;
       exception when others then null;
     end;

    begin
       stmt := 'drop operator sem_distance force'; execute immediate stmt;
       exception when others then null;
     end;

    begin
       stmt := 'drop public synonym sem_related'; execute immediate stmt;
       exception when others then null;
     end;

    begin
       stmt := 'drop operator sem_related force'; execute immediate stmt;
       exception when others then null;
     end;

    begin
       stmt := 'drop package sdo_sem_operator'; execute immediate stmt;
       exception when others then null;
     end;

    begin
       stmt := 'drop type sem_sameas_list force';  execute immediate stmt;
       exception when others then null;
    end;

    begin
       stmt := 'drop type sem_sameas_rec force';  execute immediate stmt;
       exception when others then null;
     end;

    begin
       stmt := 'drop type sem_indextype_im force';  execute immediate stmt;
       exception when others then null;
     end;

    begin
       stmt := 'drop type sdo_bt_handle force'; execute immediate stmt;
       exception when others then null;
     end;

    begin
       stmt := 'drop package sdo_sem_bt'; execute immediate stmt;
       exception when others then null;
     end;

    begin
       stmt := 'drop library mdsys.ORDMD_SEM_LIBS'; execute immediate stmt;
       exception when others then null;
    end;

    begin
       stmt := 'drop package rdf_apis_oper_internal'; execute immediate stmt;
       exception when others then null;
     end;

    begin
       stmt := 'drop package sdo_sem_inference'; execute immediate stmt;
       exception when others then null;
    end;

    begin
       stmt := 'drop package sdo_sem_inf_internal'; execute immediate stmt;
       exception when others then null;
    end;

    begin
       stmt := 'drop package sdo_sem_infh'; execute immediate stmt;
       exception when others then null;
    end;

    begin
       stmt := 'drop package sdo_sem_infi'; execute immediate stmt;
       exception when others then null;
    end;

    begin
       stmt := 'drop package sdo_sem_utl'; execute immediate stmt;
       exception when others then null;
    end;

    begin
       stmt := 'drop package sem_upgrade_to_11'; execute immediate stmt;
       exception when others then null;
    end;

    begin
       stmt := 'drop package sdo_sem_downgrade_utl'; execute immediate stmt;
       exception when others then null;
    end;

    begin
       stmt := 'drop package sdo_rdf_mig'; execute immediate stmt;
       exception when others then null;
    end;

    begin
       stmt := 'drop package sdo_sem_validate'; execute immediate stmt;
       exception when others then null;
    end;

    begin
       stmt := 'drop package sdo_sem_downgrade'; execute immediate stmt;
       exception when others then null;
    end;

    begin
       stmt := 'drop package sdo_sem_perf'; execute immediate stmt;
       exception when others then null;
    end;

    begin
       stmt := 'drop package sdo_sem_cli'; execute immediate stmt;
       exception when others then null;
    end;

    begin
       stmt := 'drop public synonym SEM_INF_HIST'; execute immediate stmt;
       exception when others then null;
    end;

    begin
       stmt := 'drop public synonym SEM_PERF'; execute immediate stmt;
       exception when others then null;
    end;

    begin
       stmt := 'drop view mdsys.sem_inf_hist'; execute immediate stmt;
       exception when others then null;
    end;

    begin
       stmt := 'drop table mdsys.rdf_hist$'; execute immediate stmt;
       exception when others then null;
    end;

    begin
       stmt := 'drop sequence mdsys.rdf_hist_id_sq'; execute immediate stmt;
       exception when others then null;
    end;

    begin
       stmt := 'drop package mdsys.sdo_sem_log'; execute immediate stmt;
       exception when others then null;
    end;

    begin
       stmt := 'drop package mdsys.sdo_sem_magic'; execute immediate stmt;
       exception when others then null;
    end;

    begin
       stmt := 'drop type mdsys.sem_vars_table force'; execute immediate stmt;
       exception when others then null;
    end;

    begin
       stmt := 'drop type mdsys.sem_pred_array force'; execute immediate stmt;
       exception when others then null;
    end;

    begin
       stmt := 'drop type mdsys.sem_predicate force'; execute immediate stmt;
       exception when others then null;
    end;

    begin
       stmt := 'drop type mdsys.sem_rule force'; execute immediate stmt;
       exception when others then null;
    end;

    begin
       stmt := 'drop type mdsys.sem_node force'; execute immediate stmt;
       exception when others then null;
    end;

    begin
       stmt := 'drop type mdsys.sem_nodes_array force'; execute immediate stmt;
       exception when others then null;
    end;

    begin
       stmt := 'drop type mdsys.sem_rule_node force'; execute immediate stmt;
       exception when others then null;
    end;

    begin
       stmt := 'drop type mdsys.sem_goal_node force'; execute immediate stmt;
       exception when others then null;
    end;

    begin
       stmt := 'drop type mdsys.sem_rules_table force'; execute immediate stmt;
       exception when others then null;
    end;

    begin
      stmt := 'drop type mdsys.sem_graph force'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop type mdsys.sem_terms_table'; execute immediate stmt;
       exception when others then null;
    end;

    begin
      stmt := 'drop type mdsys.sem_term'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop type mdsys.sem_term'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop trigger mdsys.sdo_sem_drop_trigger'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop trigger mdsys.sdo_sem_alter_trigger'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop public synonym SEM_APIS'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop package mdsys.sdo_rdf'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop package mdsys.rdf_apis_user'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop package mdsys.rdf_apis'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop package mdsys.sdo_rdf_inference'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop package mdsys.rdf_apis_internal'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop package mdsys.sdo_rdf_internal'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop package mdsys.sdo_sem_const'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop package mdsys.sem_hash_internal'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop public synonym SEM_VARCHARARRAY force'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop type mdsys.RDF_VARCHARARRAY force'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop public synonym SEM_LONGVARCHARARRAY force'; execute immediate stmt; 
      exception when others then null;
    end;

    begin
      stmt := 'drop type mdsys.RDF_LONGVARCHARARRAY force'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop public synonym SEM_MATCH'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop public synonym RDF_MATCH'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop function mdsys.RDF_MATCH'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop type mdsys.RDF_MATCH_IMPL_T force'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop public synonym SEM_ALIASES'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop type mdsys.RDF_ALIASES force'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop public synonym SEM_ALIAS'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop type mdsys.RDF_ALIAS force'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop type mdsys.SDO_RDF_TRIPLE force'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop type mdsys.SDO_RDF_TRIPLE_S force'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop procedure mdsys.LOAD_PREDEFINED_RULEBASES'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop context SDO_SEM_CTX'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop package mdsys.SDO_SEM_CTX'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop context SDO_SEM_CTX_SESSION'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop package mdsys.SDO_SEM_CTX_SESSION'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop public synonym SEM_MODELS'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop type mdsys.RDF_MODELS force'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop public synonym SEM_GRAPHS'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop type mdsys.RDF_GRAPHS force'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop public synonym SEM_RULEBASES'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop type mdsys.RDF_RULEBASES force'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop type sem_sameas_rec'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop type sem_sameas_list'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop public synonym SDO_RDF_RULEBASES'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop public synonym SDO_RDF_MODELS'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop public synonym SDO_RDF_GRAPHS'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop public synonym SDO_RDF_ALIAS'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop public synonym SDO_RDF_ALIASES'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop public synonym SDO_RDF_INFERENCE'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop public synonym SDO_RDF_MATCH'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop public synonym SDO_RDF_INTERNAL'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop public synonym SDO_RDF'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop public synonym SDO_RDF_TRIPLE'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop public synonym SDO_RDF_TRIPLE_S'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop public synonym SDO_SEM_INFERENCE'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop public synonym SDO_SEM_VALIDATE'; execute immediate stmt;
      exception when others then null;
    end;

    begin
      stmt := 'drop public synonym SDO_SEM'; execute immediate stmt;
      exception when others then null;
    end;
  -- else network exists ... so do nothing
  else
   :jscript := dbms_registry.nothing_script;
  end if;

end;
/

ALTER SESSION SET CURRENT_SCHEMA = SYS;

-- get rid of java packages
SELECT :jscript FROM DUAL;
@&comp_file4