Rem
Rem $Header: mgwu101.sql 30-mar-2006.11:19:27 chall Exp $
Rem
Rem mgwu101.sql
Rem
Rem Copyright (c) 2004, 2006, Oracle. All rights reserved.  
Rem
Rem  NAME
Rem    mgwu101.sql - MGW component upgrade script (from 10.1.0)
Rem
Rem  DESCRIPTION
Rem    This script must be run AS SYSDBA.
Rem
Rem    This script is intended to be run during component upgrade process;
Rem    typically from mgwdbmig.sql. It should only be called when upgrading
Rem    a 10.1.0 database in which the MGW component is currently loaded.
Rem
Rem    Messaging Gateway agent must be shut down before running this script.
Rem
Rem  NOTES
Rem
Rem    TOID Note: Starting with RDBMS 10R1, a fixed OID is assigned for 
Rem    every new MGW type, rather than having one automatically assigned by
Rem    the database. Note however, for any 9.x to NNN upgrade, we will wind
Rem    up with a mixture, fixed ids for any new types but old generated ids 
Rem    for existing types. It is something that we (and the user) has to deal
Rem    with. OJMS and likely other components face the same issue if they '
Rem    did not use the OID clause from the start.
Rem
Rem  MODIFIED   (MM/DD/YY)
Rem  chall       03/30/06 - chain to mgwu102.sql for 10.1.0 to 11.0 upgrade 
Rem  chall       09/17/04 - no need to create new tables since mgwtable.sql 
Rem                         called from mgwdbmig.sql (as of 10.2.0)
Rem  chall       05/19/04 - MSMQ support 
Rem  chall       05/14/04 - chall_mgw_mighook_10r2
Rem  chall       05/14/04 - Created
Rem
Rem ==========================================================================

Rem ========================================================================
Rem New Admin Types
Rem ========================================================================

--
-- mgw_msmq_properties
--
-- Basic properties for link to Microsoft MSMQ messaging system.
--
CREATE OR REPLACE TYPE sys.mgw_msmq_properties 
OID '0000000000000000000000000002108e'
AS OBJECT
(
  transactional  INTEGER,
  username       VARCHAR2(64),
  password       VARCHAR2(64),

  STATIC FUNCTION construct
  RETURN sys.mgw_msmq_properties, 

  STATIC FUNCTION alter_construct
  RETURN sys.mgw_msmq_properties
);
/

grant execute on mgw_msmq_properties to mgw_administrator_role;

Rem ========================================================================
Rem Internal types modified.
Rem
rem CAUTION: Once a type has evolved by adding a new attribute, widening an
rem attribute, and so on, a CREATE OR REPLACE TYPE or ALTER TYPE REPLACE
rem _cannot_ be used to replace the type. An attempt to do so fails with:
rem   ORA-22308: operation not allowed on an evolved type
rem The error is not displayed if database is opened as STARTUP MIGRATE and
rem it _appears_ the type is successfully replaced, but in fact, it remains
rem unchanged. Just adding/dropping type methods does not evolve the type.
rem
rem NOTE: There are four MGW internal types, mgwi_msglink, mgwi_foreignqueue, 
rem mgw_subscriber, and mgwi_schedule, that are used as in-memory objects.
rem For 9.2 to 10.1 migration, CREATE OR REPLACE is/was used for all four 
rem types during upgrade and downgrade. For 10.1 to 10.2 migration, ALTER TYPE
rem is used for two types, mgwi_msglink and mgwi_foreignqueue, to modify the
rem types rather than replacing the entire type. To workaround the 
rem evolve/replace issue, the mgwu920 and mgwe920 scripts used for 10.2 
rem release must be modified to DROP TYPE before the CREATE OR REPLACE TYPE,
rem and privileges on the types re-granted.
rem
rem From 10.2 onward, MGW should _always_ use ALTER TYPE for any type 
rem modifications and never replace the entire type.
Rem ========================================================================

-- mgwi_msglink
-- Add new attribute and new methods for MSMQ support.
--
alter type sys.mgwi_msglink
  ADD attribute m_msmqProp sys.mgw_msmq_properties
cascade;

alter type sys.mgwi_msglink
  ADD STATIC FUNCTION construct (
        p_linkname   IN VARCHAR2,
        p_properties IN sys.mgw_msmq_properties,
        p_options    IN sys.mgw_properties DEFAULT NULL,
        p_comment    IN VARCHAR2 DEFAULT NULL )
      RETURN mgwi_msglink,
  ADD MEMBER PROCEDURE loadMsmq,
  ADD MEMBER PROCEDURE insertMsmq,
  ADD MEMBER PROCEDURE deleteMsmq,
  ADD MEMBER PROCEDURE updateMsmq,
  ADD MEMBER PROCEDURE decryptMsmq(p_keyText IN VARCHAR2, p_keyRaw IN RAW)
cascade;

-- mgwi_foreignqueue
-- Increase size of m_provider_queue from 128 to 2000.
--
alter type sys.mgwi_foreignqueue
  MODIFY attribute m_provider_queue VARCHAR2(2000)
cascade;

-- mgw_property
-- Increase size of name from 100 to 500.
-- Increase size of value from 1000 to 4000.
--
alter type sys.mgw_property
  modify attribute name varchar2(500),
  modify attribute value varchar2(4000)
cascade;

-- mgw_properties
-- Increase array limit from 100 to 2000.
--
alter type sys.mgw_properties
  modify limit 2000
cascade;

Rem ========================================================================
Rem Table modifications
Rem
Rem Note: Starting with 10.2.0, mgwdbmig.sql calls mgwtable.sql so we no
Rem longer need to create new tables in mgwuNNN scripts. Only need to 
Rem focus on changes to existing tables.
Rem ========================================================================

-- mgw$_foreign_queues
-- Increase size of provider_queue from 128 to 2000.
--
alter table sys.mgw$_foreign_queues
  modify (provider_queue varchar2(2000));

Rem ========================================================================
Rem Other Tasks
Rem ========================================================================

Rem None for now.

Rem ========================================================================
Rem Chain to component's next "u" script
Rem ========================================================================

Rem Perform the 10.2.0 to 11.1.0 upgrade actions.
@@mgwu102

Rem === End file: mgwu101.sql ================================================