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

Rem ======================================
Rem Add new Object methods
Rem ======================================
begin
      execute immediate 'ALTER TYPE ORDSYS.ORDImage 
        ADD MEMBER FUNCTION getDicomMetadata(
                                  SELF IN ORDImage,
                                  optionString  IN VARCHAR2) 
            RETURN XMLType CASCADE';
end;
/

begin
      execute immediate 'ALTER TYPE ORDSYS.ORDImage 
        ADD STATIC FUNCTION getDicomMetadata(
                                   imageBlob      IN BLOB,
                                   optionString IN VARCHAR2) 
            RETURN XMLType CASCADE';
end;
/

begin
      execute immediate 'ALTER TYPE ORDSYS.ORDImage 
        ADD STATIC FUNCTION getDicomMetadata(
                                   imageBfile     IN BFILE,
                                   optionString IN VARCHAR2) 
            RETURN XMLType CASCADE';
end;
/

begin
      execute immediate 'ALTER TYPE ORDSYS.ORDImage ADD 
        MEMBER FUNCTION getMetadata(SELF          IN ORDImage,
                                    metadataType  IN VARCHAR2 DEFAULT ''ALL'') 
        RETURN SYS.XMLSEQUENCETYPE CASCADE';
end;
/

begin
      execute immediate 'ALTER TYPE ORDSYS.ORDImage ADD 
        MEMBER PROCEDURE putMetadata(SELF         IN OUT NOCOPY ORDImage,
                                    xmlData      IN SYS.XMLType,
                                    metadataType IN VARCHAR2 DEFAULT ''XMP'',
                                    encoding     IN VARCHAR2 DEFAULT ''UTF-8'')
        CASCADE';
end;
/

begin
      execute immediate 'ALTER TYPE ORDSYS.ORDImage ADD 
        STATIC FUNCTION getMetadata(imageBlob    IN BLOB,
                                    metadataType IN VARCHAR2 DEFAULT ''ALL'')
        RETURN SYS.XMLSEQUENCETYPE CASCADE';
end;
/

begin
      execute immediate 'ALTER TYPE ORDSYS.ORDImage ADD 
        STATIC FUNCTION getMetadata(imageBfile   IN BFILE,
                                    metadataType IN VARCHAR2 DEFAULT ''ALL'')
        RETURN SYS.XMLSEQUENCETYPE CASCADE';
end;
/

begin
      execute immediate 'ALTER TYPE ORDSYS.ORDImage ADD 
        STATIC PROCEDURE putMetadata(imageBlob    IN BLOB,
                                    dest         IN OUT NOCOPY BLOB,
                                    xmlData      IN SYS.XMLType,
                                    metadataType IN VARCHAR2 DEFAULT ''XMP'',
                                    encoding     IN VARCHAR2 DEFAULT ''UTF-8'')
        CASCADE';
end;
/

begin
      execute immediate 'ALTER TYPE ORDSYS.ORDImage ADD 
        STATIC PROCEDURE putMetadata(imageBfile   IN BFILE,
                                    dest         IN OUT NOCOPY BLOB,
                                    xmlData      IN SYS.XMLType,
                                    metadataType IN VARCHAR2 DEFAULT ''XMP'',
                                    encoding     IN VARCHAR2 DEFAULT ''UTF-8'')
        CASCADE';
end;
/

---
--- Drop migrate methods in ORDImage and procedures in ORDImg_Pkg
--- associated with ORDImgB and ORDImgF since they are deprecated
---
begin    
     execute immediate 'ALTER TYPE ORDSYS.ORDImage DROP 
       MEMBER PROCEDURE migrateFromORDImgB(SELF IN OUT ORDImage, 
                                           old_object IN ORDImgB) CASCADE';
end;
/

begin
      execute immediate 'ALTER TYPE ORDSYS.ORDImage DROP 
       MEMBER PROCEDURE migrateFromORDImgF(SELF IN OUT NOCOPY ORDImage,
                                           old_object ORDImgF) CASCADE';
end;
/


---
--- Drop deprecated OrdAudio methods
---
begin
     execute immediate 'ALTER TYPE ORDSYS.ORDAudio DROP 
       MEMBER FUNCTION getFormat(ctx IN OUT RAW) RETURN VARCHAR2 CASCADE';
end;
/

begin
     execute immediate 'ALTER TYPE ORDSYS.ORDAudio DROP 
       MEMBER FUNCTION getEncoding(ctx IN OUT RAW) RETURN VARCHAR2 CASCADE';
end;
/

begin
     execute immediate 'ALTER TYPE ORDSYS.ORDAudio DROP 
       MEMBER FUNCTION  getNumberOfChannels(ctx IN OUT RAW) RETURN INTEGER 
       CASCADE';
end;
/

begin
     execute immediate 'ALTER TYPE ORDSYS.ORDAudio DROP 
       MEMBER FUNCTION  getSamplingRate(ctx IN OUT RAW) 
       RETURN INTEGER CASCADE';
end;
/

begin
     execute immediate 'ALTER TYPE ORDSYS.ORDAudio DROP 
       MEMBER FUNCTION  getSampleSize(ctx IN OUT RAW) RETURN INTEGER CASCADE';
end;
/

begin
     execute immediate 'ALTER TYPE ORDSYS.ORDAudio DROP 
       MEMBER FUNCTION  getCompressionType(ctx IN OUT RAW) RETURN VARCHAR2 
       CASCADE';
end;
/

begin
     execute immediate 'ALTER TYPE ORDSYS.ORDAudio DROP 
       MEMBER FUNCTION  getAudioDuration(ctx IN OUT RAW) RETURN INTEGER 
       CASCADE';
end;
/

begin
---
--- Drop deprecated OrdVideo methods
---
     execute immediate 'ALTER TYPE ORDSYS.ORDVideo DROP 
       MEMBER FUNCTION getFormat(ctx IN OUT RAW) RETURN VARCHAR2 CASCADE';
end;
/

begin
     execute immediate 'ALTER TYPE ORDSYS.ORDVideo DROP 
       MEMBER PROCEDURE getFrameSize(SELF IN OUT NOCOPY ORDVideo, 
                                ctx IN OUT RAW, 
                                retWidth OUT INTEGER, 
                                retHeight OUT INTEGER) CASCADE';
end;
/

begin
     execute immediate 'ALTER TYPE ORDSYS.ORDVideo DROP 
       MEMBER FUNCTION  getFrameResolution(ctx IN OUT RAW) RETURN INTEGER 
       CASCADE';
end;
/

begin
     execute immediate 'ALTER TYPE ORDSYS.ORDVideo DROP 
       MEMBER FUNCTION  getFrameRate(ctx IN OUT RAW) RETURN INTEGER CASCADE';
end;
/

begin
     execute immediate 'ALTER TYPE ORDSYS.ORDVideo DROP 
       MEMBER FUNCTION  getVideoDuration(ctx IN OUT RAW) RETURN INTEGER 
       CASCADE';
end;
/

begin
     execute immediate 'ALTER TYPE ORDSYS.ORDVideo DROP 
       MEMBER FUNCTION  getNumberOfFrames(ctx IN OUT RAW) RETURN INTEGER 
       CASCADE';
end;
/

begin
     execute immediate 'ALTER TYPE ORDSYS.ORDVideo DROP 
       MEMBER FUNCTION  getCompressionType(ctx IN OUT RAW) RETURN VARCHAR2 
       CASCADE';
end;
/

begin
     execute immediate 'ALTER TYPE ORDSYS.ORDVideo DROP 
       MEMBER FUNCTION  getNumberOfColors(ctx IN OUT RAW) RETURN INTEGER 
       CASCADE';
end;
/

begin
     execute immediate 'ALTER TYPE ORDSYS.ORDVideo DROP 
       MEMBER FUNCTION  getBitRate(ctx IN OUT RAW) RETURN INTEGER CASCADE';

end;
/

Rem
Rem we need ORDSYS.ORD_STR_LIST in ORDImg_Pkg in 11.2
Rem before invoke ordipksp.plb, we need to create that.
Rem In 11.2, ORDSYS.ORD_STR_LIST is created in ordcrtbl.sql
Rem but it loaded after invoking ordipksp.plb in this script
Rem So just did a hack here to create it
create or replace type ORDSYS.ORD_STR_LIST as table  of varchar2(2000 CHAR);
/

Rem 
Rem Invoke ordipksp.plb to reload ORDImg_Pkg package.  This is necessary
Rem before we drop the ORDImgB and ORDImgF types because the package
Rem contains procedures and functions that use the types.  The package
Rem is reloaded in imdbmig.sql after running imu101.sql (or the appropriate
Rem upgrade script). But we need to reload the package before we can 
Rem drop these types, so we are basically reloading it twice - once now
Rem and once in imdbmig.sql.  This approached ensured minimum change to
Rem the way things are done and ensures that no special handling is 
Rem necessary for future releases (if the drop is encapsulated in imu101.sql,
Rem then there is no need to handle upgrade from 10.2 to higher releases
Rem in a special way, which would have been necessary had we put the
Rem drops in imdbmig.sql).

Rem Invoke ordipksp.plb to reload ORDImg_Pkg.
COLUMN :file_name NEW_VALUE pkg_file NOPRINT;
Variable file_name varchar2(50);
begin
   :file_name := '@ordipksp.plb';
end;
/
select :file_name from dual;
@&pkg_file

Rem
Rem Drop deprecated types ORDImgB and ORDImgF
Rem

DROP TYPE BODY ORDSYS.ORDImgB;
DROP TYPE ORDSYS.ORDImgB;

DROP TYPE BODY ORDSYS.ORDImgF;
DROP TYPE ORDSYS.ORDImgF;

Rem
Rem Invoke imxreg.sql to register new XML Schemas if XDB is installed
Rem
COLUMN file_name NEW_VALUE comp_file NOPRINT;
SELECT dbms_registry.script('XDB','@imxreg.sql') AS file_name FROM DUAL;
@&comp_file

Rem
Rem Drop audio and video format plug-in packages
Rem
alter session set current_schema="ORDPLUGINS";

DROP PACKAGE ORDPLUGINS.ORDX_AIFF_AUDIO;
DROP PACKAGE ORDPLUGINS.ORDX_AIFC_AUDIO;
DROP PACKAGE ORDPLUGINS.ORDX_ASF_AUDIO;
DROP PACKAGE ORDPLUGINS.ORDX_AUFF_AUDIO;
DROP PACKAGE ORDPLUGINS.ORDX_MPGA_AUDIO;
DROP PACKAGE ORDPLUGINS.ORDX_MP4_AUDIO;
DROP PACKAGE ORDPLUGINS.ORDX_RMFF_AUDIO;
DROP PACKAGE ORDPLUGINS.ORDX_WAVE_AUDIO;

DROP PACKAGE ORDPLUGINS.ORDX_MOOV_VIDEO;
DROP PACKAGE ORDPLUGINS.ORDX_AVI_VIDEO;
DROP PACKAGE ORDPLUGINS.ORDX_RMFF_VIDEO;
DROP PACKAGE ORDPLUGINS.ORDX_MPEG_VIDEO;
DROP PACKAGE ORDPLUGINS.ORDX_MP4_VIDEO;
DROP PACKAGE ORDPLUGINS.ORDX_ASF_VIDEO;

alter session set current_schema="ORDSYS";

Rem
Rem Add the new NULL_SOURCE Exceptions
Rem

CREATE OR REPLACE PACKAGE ORDSYS.ORDAudioExceptions
AS
  AUDIO_PLUGIN_EXCEPTION      EXCEPTION;
  INVALID_DESCRIPTION         EXCEPTION;
  DESCRIPTION_IS_NOT_SET      EXCEPTION;
  INVALID_MIME_TYPE           EXCEPTION;
  METHOD_NOT_SUPPORTED        EXCEPTION;
  NULL_INPUT_VALUE            EXCEPTION;
  AUDIO_FORMAT_IS_NULL        EXCEPTION;
  AUDIO_ENCODING_IS_NULL      EXCEPTION;
  AUDIO_NUM_CHANNELS_IS_NULL  EXCEPTION;
  AUDIO_SAMPLING_RATE_IS_NULL EXCEPTION;
  AUDIO_SAMPLE_SIZE_IS_NULL   EXCEPTION;
  AUDIO_DURATION_IS_NULL      EXCEPTION;
  LOCAL_DATA_SOURCE_REQUIRED  EXCEPTION;
  NULL_LOCAL_DATA             EXCEPTION;
  NULL_SOURCE                 EXCEPTION;
END;
/

CREATE OR REPLACE PACKAGE ORDSYS.ORDVideoExceptions
AS
  VIDEO_PLUGIN_EXCEPTION   EXCEPTION;
  INVALID_TITLE            EXCEPTION;
  DESCRIPTION_IS_NOT_SET   EXCEPTION;
  INVALID_MIME_TYPE        EXCEPTION;
  METHOD_NOT_SUPPORTED     EXCEPTION;
  NULL_INPUT_VALUE         EXCEPTION;
  VIDEO_FORMAT_IS_NULL     EXCEPTION;
  LOCAL_DATA_SOURCE_REQUIRED     EXCEPTION;
  NULL_LOCAL_DATA          EXCEPTION;
  NULL_SOURCE              EXCEPTION;
END;
/

CREATE OR REPLACE PACKAGE ORDSYS.ORDDocExceptions
AS
  DOC_PLUGIN_EXCEPTION        EXCEPTION;
  INVALID_MIME_TYPE           EXCEPTION;
  INVALID_FORMAT_TYPE         EXCEPTION;
  METHOD_NOT_SUPPORTED        EXCEPTION;
  NULL_INPUT_VALUE            EXCEPTION;
  NULL_LOCAL_DATA             EXCEPTION;
  NULL_SOURCE                 EXCEPTION;
END;
/

Rem
Rem The validate_ordim has been moved to SYS.
Rem Drop from ORDSYS
Rem
drop procedure ordsys.validate_ordim;

Rem The package ORDSYS.PVTCARTRIDGE has been removed
Rem
drop package ordsys.pvtcartridge;

Rem 
Rem Drop the package ORDImgSig_Pkg body.  The VIR
Rem compatible functions have been removed and the
Rem generateSignature function in ORDImgSig has only
Rem a spec and no body (it is a fast callout).
Rem
drop package body ORDSYS.ORDImgSig_Pkg;


Rem 
Rem alter to SYS and revoke unnecessary privileges.
Rem
alter session set current_schema="SYS";

declare
  priv_not_granted_ex exception;
  pragma exception_init(priv_not_granted_ex, -1952);
begin
---
--- remove keyword "any" from the privilegs.
---
  execute immediate 
   'revoke create any operator, create any indextype, create any type, 
       create any table, drop any indextype, drop any operator, drop any type
       from ORDSYS';
exception when priv_not_granted_ex then
  -- ignore ORA-01952 system privileges not granted exception
  null;
end;
/

begin
  execute immediate 
   'grant create operator, create indextype, create type, create table  
     to ORDSYS';

end;
/

declare
  priv_not_granted_ex exception;
  pragma exception_init(priv_not_granted_ex, -1952);
begin
  execute immediate 
   'revoke create any operator, create any indextype, create any type,  
       create any table, drop any indextype, drop any operator, drop any type 
       from ORDPLUGINS';
exception when priv_not_granted_ex then
  -- ignore ORA-01952 system privileges not granted exception
  null;
end;
/

begin
  execute immediate 
   'grant create operator, create indextype, create type, create table  
       to ORDPLUGINS';

end;
/

---
--- revoke connect role and grant the necessary privileges.
--- connect role has following privileges:
--- Alter Session: has to be removed for security reason. 
--- Create Cluster: removed. 
--- Create Database Link: removed. 
--- Create Sequence: removed. 
--- Create Session: kept, see imprv.sql. 
--- Create Synonym: kept, see imprv.sql. 
--- Create Table: kept, see imprv.sql. 
--- Create View: removed from ordplugins and si_infomtn_schema 
---
begin
  execute immediate 
   'revoke connect from ORDSYS';
end;
/

begin
  execute immediate 
   'grant  create view, create any synonym, drop any synonym to ORDSYS';
end;
/

begin
  execute immediate 
   'revoke connect from ORDPLUGINS';
end;
/

begin
  execute immediate 
   'revoke connect from SI_INFORMTN_SCHEMA';
end;
/
-- Revoke priviliges added to functions and types that are part of the
-- the image matching indextype implementation.  Not necessary any longer.
-- Adding exception handling since package bodies and privs might not exist
-- due to drop in 9.2 upgrade so might get errors if upgrading from 9.2.  

begin
  EXECUTE IMMEDIATE
   'revoke execute on ORDSYS.ScoreFunc from public';
  EXCEPTION WHEN others THEN null;
end;
/

begin
  EXECUTE IMMEDIATE
   'revoke execute on ORDSYS.SimilarFunc from public';
  EXCEPTION WHEN others THEN null;
end;
/

begin
  EXECUTE IMMEDIATE
   'revoke execute on ORDSYS.ORDImgIdxMethods from public';
  EXCEPTION WHEN others THEN null;
end;
/

begin
  EXECUTE IMMEDIATE
   'revoke execute on ORDSYS.ORDImageIndexStats from public';
  EXCEPTION WHEN others THEN null;
end;
/

REM Must end with session set to ORDSYS
alter session set current_schema="ORDSYS";

REM Invoke 10.2 script to go from 10.2 to 11
@@imu102.sql