Edit D:\app\Administrator\product\11.2.0\dbhome_1\ord\im\admin\imu101.sql
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
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de