Rem Copyright (c) 2006, 2008, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      imu102.sql
Rem
Rem    DESCRIPTION
Rem      runs as ORDSYS
Rem
Rem      Internal upgrade script for Oracle Multimedia from 10.2
Rem
Rem    NOTES
Rem

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

--
-- Drop the pragma restriction on ordsource methods by dropping and adding
-- the method back.
--
begin
      -- drop the pragma on ORDDoc getContentLength method.
      execute immediate 'ALTER TYPE ORDSYS.ORDDoc 
        DROP MEMBER FUNCTION getContentLength
            RETURN INTEGER CASCADE';
end;
/

begin
      execute immediate 'ALTER TYPE ORDSYS.ORDDoc 
        ADD MEMBER FUNCTION getContentLength
            RETURN INTEGER CASCADE';
end;
/
      -- drop the pragma on ORDVideo getContentLength method.
begin
      execute immediate 'ALTER TYPE ORDSYS.ORDVideo 
        DROP MEMBER FUNCTION getContentLength( ctx IN OUT RAW) 
            RETURN INTEGER CASCADE';
end;
/

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

begin
      execute immediate 'ALTER TYPE ORDSYS.ORDVideo 
        DROP MEMBER FUNCTION getContentLength
            RETURN INTEGER CASCADE';
end;
/

begin
      execute immediate 'ALTER TYPE ORDSYS.ORDVideo 
        ADD MEMBER FUNCTION getContentLength
            RETURN INTEGER CASCADE';
end;
/
      -- drop the pragma on ORDAudio getContentLength method.
begin
      execute immediate 'ALTER TYPE ORDSYS.ORDAudio 
        DROP MEMBER FUNCTION getContentLength( ctx IN OUT RAW) 
            RETURN INTEGER CASCADE';
end;
/

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

begin
      execute immediate 'ALTER TYPE ORDSYS.ORDAudio 
        DROP MEMBER FUNCTION getContentLength
            RETURN INTEGER CASCADE';
end;
/

begin
      execute immediate 'ALTER TYPE ORDSYS.ORDAudio 
        ADD MEMBER FUNCTION getContentLength
            RETURN INTEGER CASCADE';
end;
/
      -- drop the pragma on ORDSource getContentLength method.
begin
      execute immediate 'ALTER TYPE ORDSYS.ORDSource 
        DROP MEMBER FUNCTION getContentLength( ctx IN OUT RAW) 
            RETURN INTEGER CASCADE';
end;
/

begin
      execute immediate 'ALTER TYPE ORDSYS.ORDSource 
        ADD MEMBER FUNCTION getContentLength( ctx IN OUT RAW) 
            RETURN INTEGER CASCADE';
end;
/
      -- drop the pragma on ORDSource getSourceAddress method.
begin
      execute immediate 'ALTER TYPE ORDSYS.ORDSource 
        DROP MEMBER FUNCTION getSourceAddress( 
                                ctx IN OUT RAW, 
                                userData IN VARCHAR2) 
            RETURN VARCHAR2 CASCADE';
end;
/

begin
      execute immediate 'ALTER TYPE ORDSYS.ORDSource 
        ADD MEMBER FUNCTION getSourceAddress( 
                                ctx IN OUT RAW, 
                                userData IN VARCHAR2) 
            RETURN VARCHAR2 CASCADE';
end;
/

-- Privs needed to load native library, see imprv.sql
-- enable SYS
execute dbms_java.grant_permission('SYS','SYS:oracle.aurora.rdbms.security.PolicyTablePermission','0:java.lang.RuntimePermission#loadLibrary.ordim11',null);

execute dbms_java.grant_permission('SYS','SYS:oracle.aurora.rdbms.security.PolicyTablePermission','0:java.lang.RuntimePermission#loadLibrary.oraordim11',null);

-- grant to ORDSYS
execute dbms_java.grant_permission('ORDSYS', 'SYS:java.lang.RuntimePermission','loadLibrary.ordim11', '');

execute dbms_java.grant_permission('ORDSYS', 'SYS:java.lang.RuntimePermission','loadLibrary.oraordim11', '');

execute dbms_java.grant_permission('ORDSYS', 'SYS:java.util.logging.LoggingPermission','control','');

--revoke from SYS
execute dbms_java.revoke_permission('SYS','SYS:oracle.aurora.rdbms.security.PolicyTablePermission','0:java.lang.RuntimePermission#loadLibrary.ordim11',null);

execute dbms_java.revoke_permission('SYS','SYS:oracle.aurora.rdbms.security.PolicyTablePermission','0:java.lang.RuntimePermission#loadLibrary.oraordim11',null);
-- END privs need for native library


-- 
-- Load new DICOM support
--
grant execute on dbms_lock to ordsys;

-- create new DICOM admin role
create role ORDADMIN;

--
-- NOTE: Removing invocation of ordcrtbl.plb because the DICOM tables
-- are created in ORDData schema and not in ORDSYS schema
-- The only reason to leave the DICOM tables in ORDSYS is for downgrade 
-- purposes
-- But the downgrade will go from 11.2 -> 10.2 and not to 11.1 so the tables 
-- in ORDSYS schema are not needed
--


-- create DICOM object types
@@ordcscsp
grant execute on ordsys.orddatasource to public;
create or replace public synonym orddatasource for ordsys.orddatasource;

@@ordcspec
grant execute on ordsys.orddicom to public;
create or replace public synonym orddicom      for ordsys.orddicom;

-- grant needed for tracking feature usage
grant select on dba_tab_cols to ordsys;
-- create table for tracking feature usage
create table ORDSYS.ORD_USAGE_RECS (
  USERNAME              VARCHAR2(30)  NOT NULL,    
  AUX_COUNT             NUMBER,
  COMPONENT_NAME        VARCHAR2(200) NOT NULL
);

Rem This should be at the end of the file
alter session set current_schema="ORDSYS";

REM Invoke 11.1 script to go from 11.1 to 11.2
@@imu111.sql