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