Rem
Rem imupins.sql
Rem
Rem Copyright (c) 2005, 2008, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      imupins.sql - This script installs Oracle Multimedia during an upgrade
Rem
Rem    DESCRIPTION
Rem      This script is invoked during upgrade to clean up old ORD and VIR 
Rem      components and to install Oracle Multimedia.  It is invoked rather 
Rem      than the Oracle Multimedia upgrade script when Oracle Multimedia is 
Rem      not present in the source database and when a component that depends 
Rem      on Oracle Multimedia is being upgraded.  For example, in 8i and 9i, 
Rem      Spatial had no dependency on Oracle Multimedia.  In 10g, Spatial 
Rem      started depending on Oracle Multimedia.  If an 8i or 9i database with
Rem      Spatial and without Oracle Multimedia is upgraded, this script is 
Rem      invoked to install Oracle Multimedia before Spatial is upgraded.
Rem      
Rem    NOTES
Rem      You must connect as SYSDBA prior to running this script
Rem

Rem =============================================================
Rem Create ORDSYS and ORDPLUGINS in case they have been deleted
Rem Create ORDDATA schema
Rem =============================================================

DECLARE
    user_name_conflicts_ex exception;
    pragma exception_init (user_name_conflicts_ex, -1920);
BEGIN 
  BEGIN
    EXECUTE IMMEDIATE 
          'CREATE USER ordsys IDENTIFIED BY ordsys ' ||
          'ACCOUNT LOCK PASSWORD EXPIRE ' ||
          'DEFAULT TABLESPACE SYSAUX';
  EXCEPTION WHEN user_name_conflicts_ex THEN NULL;
  END;

  BEGIN
    EXECUTE IMMEDIATE 
          'CREATE USER ordplugins IDENTIFIED BY ordplugins ' ||
          'ACCOUNT LOCK PASSWORD EXPIRE ' ||
          'DEFAULT TABLESPACE SYSAUX';
  EXCEPTION WHEN user_name_conflicts_ex THEN NULL;
  END;

  BEGIN
    EXECUTE IMMEDIATE 
          'CREATE USER orddata IDENTIFIED BY orddata ' ||
          'ACCOUNT LOCK PASSWORD EXPIRE ' ||
          'DEFAULT TABLESPACE SYSAUX';
  EXCEPTION WHEN user_name_conflicts_ex THEN NULL;
  END;
END;
/

Rem ===========================================
Rem Drop 817, 901 and 920 old interMedia cartridge tables, packages etc
Rem ===========================================

alter session set current_schema="ORDSYS";

Rem Remove DBA_CARTRIDGES tables if they are there
begin
  EXECUTE IMMEDIATE 'drop view DBA_CARTRIDGES';
exception when others then
  null;
end;
/
begin
  EXECUTE IMMEDIATE 'drop view DBA_CARTRIDGE_COMPONENTS';
exception when others then
  null;
end;
/
begin
  EXECUTE IMMEDIATE 'drop public synonym DBA_CARTRIDGES';
exception when others then
  null;
end;
/
begin
  EXECUTE IMMEDIATE 'drop public synonym DBA_CARTRIDGE_COMPONENTS';
exception when others then
  null;
end;
/

Rem Remove ORDSYS.PVTCARTRIDGE if it is there
begin
  EXECUTE IMMEDIATE 'drop package ordsys.pvtcartridge';
exception when others then
  null;
end;
/

Rem
Rem Remove Visual Information Retrieval if it was there
Rem
          
BEGIN
IF (dbms_registry.is_loaded('ORDVIR') IS NOT NULL)
THEN
  begin
    EXECUTE IMMEDIATE 'drop package ORDVIR_PKG';
  exception when others then
    null;
  end;
  begin
    EXECUTE IMMEDIATE 'drop package ORDVIREXCEPTIONS';
  exception when others then
    null;
  end;
  begin
    EXECUTE IMMEDIATE 'drop synonym VIR';
  exception when others then
    null;
  end;
  begin
    EXECUTE IMMEDIATE 'drop type body ORDVir';
  exception when others then
    null;
  end;
  begin
   EXECUTE IMMEDIATE 'drop type ORDVir';
  exception when others then
    null;
  end;
  begin
    EXECUTE IMMEDIATE 'drop type body ORDVirB';
  exception when others then
    null;
  end;
  begin
   EXECUTE IMMEDIATE 'drop type ORDVirB';
  exception when others then
    null;
  end;
  begin
    EXECUTE IMMEDIATE 'drop type body ORDVirF';
  exception when others then
    null;
  end;
  begin
   EXECUTE IMMEDIATE 'drop type ORDVirF';
  exception when others then
    null;
  end;
  begin
    EXECUTE IMMEDIATE 'drop type body ORDVIRIDXMethods';
  exception when others then
    null;
  end;
  begin
    EXECUTE IMMEDIATE 'drop type ORDVIRIDXMethods';
  exception when others then
    null;
  end;
END IF;
end;
/

Rem Remove VIR components while connected as SYS
alter session set current_schema="SYS";

--  
-- Drop user indexes of type ORDVIRIDX
--
DECLARE
  cursor viridx_curs is select index_name, owner from all_indexes 
    where index_type = 'DOMAIN' and ityp_name = 'ORDVIRIDX';
  index_name VARCHAR2(30);
  index_owner VARCHAR2(30);
  idxcurs integer;

BEGIN
IF (dbms_registry.is_loaded('ORDVIR') IS NOT NULL)
THEN
    idxcurs := dbms_sql.open_cursor;
    open viridx_curs;
    loop
        begin
          fetch viridx_curs into index_name, index_owner;
          exit when viridx_curs%NOTFOUND;

          -- Note: these drop statements are executed immediately upon parse
          -- thus the class name needs to be specified, but no execute is
          --  required
          --
          -- drop the domain index
          dbms_sql.parse(idxcurs, 'drop index ' || index_owner || '.' ||
                         index_name || ' force',
                         dbms_sql.native);
          -- drop associated table
          dbms_sql.parse(idxcurs, 'drop table ' || index_owner || '.' ||
                         index_name || '_FT$',
                         dbms_sql.native);
          exception when others then
            exit;
        end;
    end loop;
    close viridx_curs;
    dbms_sql.close_cursor(idxcurs);
END IF;
END;
/

Rem Remove remaining VIR components as ORDSYS
alter session set current_schema="ORDSYS";

BEGIN
IF (dbms_registry.is_loaded('ORDVIR') IS NOT NULL)
THEN
  dbms_registry.removing('ORDVIR');
  begin
    EXECUTE IMMEDIATE 'drop indextype ordsys.ordviridx';
  exception when others then
    null;
  end;
  begin
    EXECUTE IMMEDIATE 'drop operator virscore';
  exception when others then
    null;
  end;
  begin
    EXECUTE IMMEDIATE 'drop operator virsimilar';
  exception when others then
    null;
  end;
  begin
    EXECUTE IMMEDIATE 'drop function virscorefunc';
  exception when others then
    null;
  end;
  begin
    EXECUTE IMMEDIATE 'drop function virsimilarfunc'; 
  exception when others then
    null;
  end;
  begin
    EXECUTE IMMEDIATE 'drop type ORDVIRIDXStats';
  exception when others then
    null;
  end;
  begin
    EXECUTE IMMEDIATE 'drop type body ordvirattr_varray';
  exception when others then
    null;
  end;
  begin
    EXECUTE IMMEDIATE 'drop type ordvirattr_varray';
  exception when others then
    null;
  end;
  begin
    EXECUTE IMMEDIATE 'drop type body ordvirscr_varray';
  exception when others then
    null;
  end;
  begin
    EXECUTE IMMEDIATE 'drop type ordvirscr_varray';
  exception when others then
    null;
  end;
  begin
    EXECUTE IMMEDIATE 'drop type body ordvirrowid_table';
  exception when others then
    null;
  end;
  begin
    EXECUTE IMMEDIATE 'drop type ordvirrowid_table';
  exception when others then
    null;
  end;
  dbms_registry.removed('ORDVIR');
END IF;    
END;
/


Rem ===========================================
Rem Install Oracle Multimedia related objects
Rem ===========================================
@@iminst.sql

alter session set current_schema="SYS";