Rem Copyright (c) 2002, 2009, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      imu920.sql
Rem
Rem    DESCRIPTION
Rem      runs as ORDSYS
Rem
Rem      Internal upgrade script for Oracle Multimedia from 9.2.0.
Rem
Rem    NOTES
Rem      

REM Create new Schemas and make sure all privs we need are granted
alter session set current_schema="SYS";
begin
  execute immediate 
      'create user SI_INFORMTN_SCHEMA identified by si_informtn_schema';
  execute immediate 
      'alter user SI_INFORMTN_SCHEMA account lock password expire';
  EXCEPTION WHEN OTHERS THEN
    NULL;
end;
/


-- removed imprv.sql because it is called in imu111.sql
-- The user ORDDATA is created in imu111.sql and grant for ORDDATA is in
-- imprv.sql
alter session set current_schema="ORDSYS";

Rem
Rem drop OrdSourceServer class in ORDPLUGINS schema
Rem (it belongs in ORDSYS schema - bug 3462254)
Rem
declare
 cnt number;
begin
 select count(object_name) into cnt
   from sys.dba_objects,javasnm
   where owner = 'ORDPLUGINS' and
   object_type = 'JAVA CLASS' and
   short(+) = object_name and
   nvl(longname, object_name) = 'oracle/ord/media/source/OrdSourceSvr';
 if cnt > 0 then
   execute immediate 'drop java class ORDPLUGINS."oracle/ord/media/source/OrdSourceSvr"';
 end if;
end;
/


REM Remove obsolete libraries
drop library ordvirlibs;
drop library ordvirlibt;
drop library ordaudlibs;
drop library ordimglibs;

Rem
Rem Remove ORDAnnotations if it was there
Rem
begin
  EXECUTE IMMEDIATE 'drop package ORDSYS.ORDAnnotationsExceptions';
exception when others then
  null;
end;
/
begin
  EXECUTE IMMEDIATE 'drop type ORDSYS.ORDAnnotations';
exception when others then
  null;
end;
/
begin
  EXECUTE IMMEDIATE 'drop type ORDSYS.ORDAnnotationList';
exception when others then
  null;
end;
/
begin
  EXECUTE IMMEDIATE 'drop type ORDSYS.ORDAnnotation';
exception when others then
  null;
end;
/

Rem
Rem Remove DBA_CARTRIDGES tables if they are there
Rem
begin
  EXECUTE IMMEDIATE 'drop view ORDSYS.DBA_CARTRIDGES';
exception when others then
  null;
end;
/
begin
  EXECUTE IMMEDIATE 'drop view ORDSYS.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
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 ORDSYS.ORDVIR_PKG';
  exception when others then
    null;
  end;
  begin
    EXECUTE IMMEDIATE 'drop package ORDSYS.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 ORDSYS.ORDVir';
  exception when others then
    null;
  end;
  begin
   EXECUTE IMMEDIATE 'drop type ORDSYS.ORDVir';
  exception when others then
    null;
  end;
  begin
    EXECUTE IMMEDIATE 'drop type body ORDSYS.ORDVirB';
  exception when others then
    null;
  end;
  begin
   EXECUTE IMMEDIATE 'drop type ORDSYS.ORDVirB';
  exception when others then
    null;
  end;
  begin
    EXECUTE IMMEDIATE 'drop type body ORDSYS.ORDVirF';
  exception when others then
    null;
  end;
  begin
   EXECUTE IMMEDIATE 'drop type ORDSYS.ORDVirF';
  exception when others then
    null;
  end;
  begin
    EXECUTE IMMEDIATE 'drop type body ORDSYS.ORDVIRIDXMethods';
  exception when others then
    null;
  end;
  begin
    EXECUTE IMMEDIATE 'drop type ORDSYS.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 sys.dba_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 := sys.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
          sys.dbms_sql.parse(idxcurs, 'drop index ' || 
                         sys.dbms_assert.enquote_name(index_owner) || '.' ||
                         sys.dbms_assert.enquote_name(index_name) || ' force',
                         sys.dbms_sql.native);
          -- drop associated table
          sys.dbms_sql.parse(idxcurs, 'drop table ' || 
                         sys.dbms_assert.enquote_name(index_owner) || '.' ||
                         sys.dbms_assert.enquote_name(index_name) || '_FT$',
                         sys.dbms_sql.native);
          exception when others then
            exit;
        end;
    end loop;
    close viridx_curs;
    sys.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 ordsys.virscore';
  exception when others then
    null;
  end;
  begin
    EXECUTE IMMEDIATE 'drop operator ordsys.virsimilar';
  exception when others then
    null;
  end;
  begin
    EXECUTE IMMEDIATE 'drop function ordsys.virscorefunc';
  exception when others then
    null;
  end;
  begin
    EXECUTE IMMEDIATE 'drop function ordsys.virsimilarfunc'; 
  exception when others then
    null;
  end;
  begin
    EXECUTE IMMEDIATE 'drop type ordsys.ORDVIRIDXStats';
  exception when others then
    null;
  end;
  begin
    EXECUTE IMMEDIATE 'drop type body ordsys.ordvirattr_varray';
  exception when others then
    null;
  end;
  begin
    EXECUTE IMMEDIATE 'drop type ordsys.ordvirattr_varray';
  exception when others then
    null;
  end;
  begin
    EXECUTE IMMEDIATE 'drop type body ordsys.ordvirscr_varray';
  exception when others then
    null;
  end;
  begin
    EXECUTE IMMEDIATE 'drop type ordsys.ordvirscr_varray';
  exception when others then
    null;
  end;
  begin
    EXECUTE IMMEDIATE 'drop type body ordsys.ordvirrowid_table';
  exception when others then
    null;
  end;
  begin
    EXECUTE IMMEDIATE 'drop type ordsys.ordvirrowid_table';
  exception when others then
    null;
  end;
  dbms_registry.removed('ORDVIR');
END IF;    
END;
/

Rem
Rem  Drop loadlobs to avoid invalid object during upgrade
Rem
begin
  EXECUTE IMMEDIATE 'drop package ordsys.loadlobs';
exception when others then
  null;
end;
/

Rem Recreate libraries
@@ordlib

REM Install new Tables
@@ordistab.plb

REM Install SQL/MM types if not already done
Rem ===========================================
Rem setup component script filname variable
Rem ===========================================
COLUMN :script_name NEW_VALUE comp_file NOPRINT
Variable script_name varchar2(50)

Begin
If (substr(dbms_registry.version('ORDIM'),1,2) = '8.') then
   :script_name := sys.dbms_registry.nothing_script;
else
   :script_name := '@ordisits.sql';
end if;
end;
/

Rem Invoke Still Image script if not already invoked
select :script_name from dual;
@&comp_file

-- Bug 2305344 - adding an OID to ORDImageSignature by doing a
-- create or replace ORDImageSignature type if there are no dependent objects

-- setup component script filename variable

COLUMN :typ_script NEW_VALUE typ_file NOPRINT
Variable typ_script varchar2(50)

COLUMN :obj_dep_count NEW_VALUE o_dep NOPRINT
Variable obj_dep_count NUMBER

alter session set current_schema="SYS";

BEGIN
   select count(*) into :obj_dep_count from sys.dba_dependencies where referenced_name='ORDIMAGESIGNATURE';
END;
/

alter session set current_schema="ORDSYS";

BEGIN
    :typ_script := sys.dbms_registry.nothing_script;

   -- there are user defined objects which 
   -- reference the object ORDIMAGESIGNATURE - if there are none, 
   -- the only objects are the 9 objects owned by ordsys. 
   -- the value of 9 is hardcoded because 9i has 9 objects that are
   -- dependent on ordsys, and these should be ignored while checking to see
   -- whether a user has defined objects using ORDIMAGESIGNATURE.

   IF (:obj_dep_count > 9)  THEN 
      RETURN;
   END IF;
      -- there are no dependent objects, so drop objects.
      BEGIN
        EXECUTE IMMEDIATE 'drop indextype ORDSYS.ORDImageIndex';
          EXCEPTION WHEN others THEN
          NULL;
      END;  
      BEGIN
        EXECUTE IMMEDIATE 'drop operator ORDSYS.IMGScore';
          EXCEPTION WHEN others THEN
          NULL;
      END;  
      BEGIN
        EXECUTE IMMEDIATE 'drop operator ORDSYS.IMGSimilar';
          EXCEPTION WHEN others THEN
          NULL;
      END;  
      BEGIN
        EXECUTE IMMEDIATE 'drop type ORDSYS.ORDImgIdxMethods';
          EXCEPTION WHEN others THEN
          NULL;
      END;  
      BEGIN
        EXECUTE IMMEDIATE 'drop function ORDSYS.SimilarFunc';
          EXCEPTION WHEN others THEN
          NULL;
      END;  
      BEGIN
        EXECUTE IMMEDIATE 'drop function ORDSYS.ScoreFunc';
          EXCEPTION WHEN others THEN
          NULL;
      END;  
      BEGIN
        EXECUTE IMMEDIATE 'drop type ORDSYS.ORDImageIndexStats';
          EXCEPTION WHEN others THEN
          NULL;
      END;  
      BEGIN
        EXECUTE IMMEDIATE 'drop type ORDSYS.ORDImageSignature';
          EXCEPTION WHEN others THEN
          NULL;
      END;
      :typ_script := '@ordimssp.sql';
END;
/

select :typ_script from dual;
@&typ_file

REM make necessary grants
grant execute on ORDSYS.ORDImg_Pkg to public;

-- ordibody will be called by the imtyb and implb scripts.

REM Create public synonyms for the ORD object types
create or replace public synonym ORDAudio for ORDSYS.ORDAudio;
create or replace public synonym ORDImage for ORDSYS.ORDImage;
create or replace public synonym ORDVideo for ORDSYS.ORDVideo;
create or replace public synonym ORDDoc   for ORDSYS.ORDDoc;
create or replace public synonym ORDImageSignature for ORDSYS.ORDImageSignature;

REM Invoke 10.1 script to go from 10.1 to 10.2
@@imu101.sql