Edit D:\app\Administrator\product\11.2.0\dbhome_1\mgw\admin\mgwu920.sql
Rem Rem $Header: mgwu920.sql 01-jun-2004.14:10:09 chall Exp $ Rem Rem mgwu920.sql Rem Rem Copyright (c) 2002, 2003, Oracle Corporation. All rights reserved. Rem Rem NAME Rem mgwu920.sql - MGW component upgrade script (RDBMS 9.2.0 to 10.1.0) Rem Rem DESCRIPTION Rem Upgrade script for MGW from RDBMS 9.2.0.x to 10.1.0. Rem Rem NOTES 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 9.2.0 database in which the MGW component had been loaded. Rem Rem Messaging Gateway agent must be shut down before running this script. Rem Rem TOID Note: Starting with RDBMS 10R1, we will assign a fixed OID for Rem every new MGW type created, rather than having one automatically Rem assigned by the database. Note however, for any 9.x to 10.1 upgrade, Rem we will wind up with a mixture, fixed ids for any new types but old Rem generated ids for existing types. It is something that we (and the Rem user) has to deal with. OJMS and likely other components face the Rem same issue if they did not use the OID clause from the start. Rem Rem MODIFIED (MM/DD/YY) Rem chall 06/01/04 - workaround REPLACE TYPE for evolved type (9.2 to Rem 10.2 downgrade/upgrade) Rem chall 05/14/04 - add 10gR2 migration hooks Rem chall 12/15/03 - add OID clause to object types (bug 3316719) Rem chall 07/29/03 - new fields for mgw$_gateway (bug 2999689) Rem chall 06/04/03 - remove notify pipes (bug 2984956) Rem chall 01/17/03 - set mgw$_schedules.latency to NULL (bug 2754884) Rem chall 11/05/02 - chall_bug-2606429 Rem chall 10/29/02 - Created Rem Rem ========================================================================== Rem ======================================================================== Rem New Admin Types Rem ======================================================================== CREATE OR REPLACE TYPE sys.mgw_tibrv_properties OID '00000000000000000000000000021083' AS OBJECT ( service VARCHAR2(128), daemon VARCHAR2(128), network VARCHAR2(256), cm_name VARCHAR2(256), cm_ledger VARCHAR2(256), STATIC FUNCTION construct RETURN sys.mgw_tibrv_properties, STATIC FUNCTION alter_construct RETURN sys.mgw_tibrv_properties ); / grant execute on mgw_tibrv_properties to mgw_administrator_role; Rem ======================================================================== Rem New Types for TIB/RV Message Type Rem ======================================================================== create or replace type sys.mgw_number_array_t oid '00000000000000000000000000021089' as varray(1024) of NUMBER; / grant execute on sys.mgw_number_array_t to public with grant option; create or replace type sys.mgw_tibrv_field_t oid '0000000000000000000000000002108a' as object ( field_name VARCHAR2(256), field_id INTEGER, field_type INTEGER, number_value NUMBER, number_array_value sys.mgw_number_array_t, text_value VARCHAR2(4000), raw_value RAW(2000), date_value DATE, clob_value CLOB, blob_value BLOB ); / grant execute on sys.mgw_tibrv_field_t to public with grant option; create or replace type sys.mgw_tibrv_ifield_t oid '0000000000000000000000000002108b' as object ( field_name VARCHAR2(256), field_id INTEGER, field_type INTEGER, number_value NUMBER, number_array_value sys.mgw_number_array_t, text_value VARCHAR2(4000), raw_value RAW(2000), date_value DATE, clob_index INTEGER, blob_index INTEGER ); / grant execute on sys.mgw_tibrv_ifield_t to public with grant option; create or replace type sys.mgw_tibrv_ifields_t oid '0000000000000000000000000002108c' AS varray(1024) OF sys.mgw_tibrv_ifield_t; / grant execute on sys.mgw_tibrv_ifields_t to public with grant option; create or replace type sys.mgw_tibrv_msg_t oid '0000000000000000000000000002108d' as object ( send_subject VARCHAR2(256), reply_subject VARCHAR2(256), cm_time_limit NUMBER, cm_sender_name VARCHAR2(256), cm_sequence_num NUMBER, fields sys.mgw_tibrv_ifields_t, clob_data1 CLOB, clob_data2 CLOB, clob_data3 CLOB, blob_data1 BLOB, blob_data2 BLOB, blob_data3 BLOB, STATIC FUNCTION construct RETURN sys.mgw_tibrv_msg_t, MEMBER PROCEDURE add_bool ( name IN VARCHAR2, id IN INTEGER, value IN INTEGER ), MEMBER PROCEDURE add_f32 ( name IN VARCHAR2, id IN INTEGER, value IN FLOAT ), MEMBER PROCEDURE add_f64 ( name IN VARCHAR2, id IN INTEGER, value IN NUMBER ), MEMBER PROCEDURE add_i8 ( name IN VARCHAR2, id IN INTEGER, value IN INTEGER ), MEMBER PROCEDURE add_i16 ( name IN VARCHAR2, id IN INTEGER, value IN INTEGER ), MEMBER PROCEDURE add_i32 ( name IN VARCHAR2, id IN INTEGER, value IN INTEGER ), MEMBER PROCEDURE add_i64 ( name IN VARCHAR2, id IN INTEGER, value IN NUMBER ), MEMBER PROCEDURE add_ipaddr32 ( name IN VARCHAR2, id IN INTEGER, value IN VARCHAR2 ), MEMBER PROCEDURE add_ipport16 ( name IN VARCHAR2, id IN INTEGER, value IN INTEGER ), MEMBER PROCEDURE add_datetime ( name IN VARCHAR2, id IN INTEGER, value IN DATE ), MEMBER PROCEDURE add_f32array ( name IN VARCHAR2, id IN INTEGER, value IN sys.mgw_number_array_t ), MEMBER PROCEDURE add_f64array ( name IN VARCHAR2, id IN INTEGER, value IN sys.mgw_number_array_t ), MEMBER PROCEDURE add_i8array ( name IN VARCHAR2, id IN INTEGER, value IN sys.mgw_number_array_t ), MEMBER PROCEDURE add_i16array ( name IN VARCHAR2, id IN INTEGER, value IN sys.mgw_number_array_t ), MEMBER PROCEDURE add_i32array ( name IN VARCHAR2, id IN INTEGER, value IN sys.mgw_number_array_t ), MEMBER PROCEDURE add_i64array ( name IN VARCHAR2, id IN INTEGER, value IN sys.mgw_number_array_t ), MEMBER PROCEDURE add_string ( name IN VARCHAR2, id IN INTEGER, value IN VARCHAR2 ), MEMBER PROCEDURE add_string ( name IN VARCHAR2, id IN INTEGER, value IN CLOB ), MEMBER PROCEDURE add_opaque ( name IN VARCHAR2, id IN INTEGER, value IN RAW ), MEMBER PROCEDURE add_opaque ( name IN VARCHAR2, id IN INTEGER, value IN BLOB ), MEMBER PROCEDURE add_xml ( name IN VARCHAR2, id IN INTEGER, value IN RAW ), MEMBER PROCEDURE add_xml ( name IN VARCHAR2, id IN INTEGER, value IN BLOB ), MEMBER PROCEDURE set_send_subject ( value IN VARCHAR2 ), MEMBER PROCEDURE set_reply_subject ( value IN VARCHAR2 ), MEMBER PROCEDURE set_cm_time_limit ( value IN NUMBER ), MEMBER PROCEDURE set_cm_sender_name ( value IN VARCHAR2 ), MEMBER PROCEDURE set_cm_sequence_num ( value IN NUMBER ), MEMBER FUNCTION get_send_subject RETURN VARCHAR2, MEMBER FUNCTION get_reply_subject RETURN VARCHAR2, MEMBER FUNCTION get_cm_time_limit RETURN NUMBER, MEMBER FUNCTION get_cm_sender_name RETURN VARCHAR2, MEMBER FUNCTION get_cm_sequence_num RETURN NUMBER, MEMBER FUNCTION get_field_count RETURN INTEGER, MEMBER FUNCTION get_field ( idx IN INTEGER ) RETURN sys.mgw_tibrv_field_t, MEMBER FUNCTION get_field_by_name ( name IN VARCHAR2 ) RETURN sys.mgw_tibrv_field_t, MEMBER FUNCTION get_field_by_id ( id IN INTEGER ) RETURN sys.mgw_tibrv_field_t, MEMBER FUNCTION find_field_name ( name IN VARCHAR2, start_idx IN INTEGER ) RETURN INTEGER, MEMBER FUNCTION find_field_id ( id IN INTEGER, start_idx IN INTEGER ) RETURN INTEGER, MEMBER PROCEDURE add_ifield ( p_field IN sys.mgw_tibrv_ifield_t ), MEMBER PROCEDURE add_num_field ( p_name IN VARCHAR2, p_id IN INTEGER, p_type IN INTEGER, p_value IN NUMBER ), MEMBER PROCEDURE add_numarray_field ( p_name IN VARCHAR2, p_id IN INTEGER, p_type IN INTEGER, p_value IN sys.mgw_number_array_t ), MEMBER PROCEDURE add_text_field ( p_name IN VARCHAR2, p_id IN INTEGER, p_type IN INTEGER, p_value IN VARCHAR2 ), MEMBER PROCEDURE add_raw_field ( p_name IN VARCHAR2, p_id IN INTEGER, p_type IN INTEGER, p_value IN RAW ), MEMBER PROCEDURE add_date_field ( p_name IN VARCHAR2, p_id IN INTEGER, p_type IN INTEGER, p_value IN DATE ), MEMBER PROCEDURE add_clob_field ( p_name IN VARCHAR2, p_id IN INTEGER, p_type IN INTEGER, p_value IN CLOB ), MEMBER PROCEDURE add_blob_field ( p_name IN VARCHAR2, p_id IN INTEGER, p_type IN INTEGER, p_value IN BLOB ) ); / grant execute on sys.mgw_tibrv_msg_t to public with grant option; 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: For 10.1, new attributes were added to the four MGW internal types, Rem mgwi_msglink, mgwi_foreignqueue, mgw_subscriber, and mgwi_schedule and Rem a CREATE OR REPLACE used to replace the type rather than using ALTER TYPE. Rem Even worse, an attribute was even added in the middle of the existing Rem attributes for mgwi_msglink. Rem Rem For 10.2 (and future releases), ALTER TYPE is used to evolve MGW types. Rem To get around the evolve/replace issue for a 10.2 to 9.2 downgrade, a Rem couple changes are needed for the mgwu920 and mgwe920 scripts shipped Rem with the 10.2 release. First, a DROP TYPE is added before the Rem CREATE OR REPLACE TYPE since the REPLACE fails for an evolved type. Rem Second, necessary privileges on the types must be re-granted after the Rem type is re-created. 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 -- drop type mgwi_msglink; CREATE OR REPLACE TYPE mgwi_msglink OID '00000000000000000000000000021090' AS OBJECT ( m_linkname VARCHAR2(30), m_linktype INTEGER, m_mqseriesProp sys.mgw_mqseries_properties, m_tibrvProp sys.mgw_tibrv_properties, m_options sys.mgw_properties, m_comment VARCHAR2(2000), m_guid RAW(16), m_flags INTEGER, STATIC FUNCTION entry_exists ( p_linkname IN VARCHAR2 ) RETURN INTEGER, STATIC FUNCTION entry_guid ( p_linkname IN VARCHAR2 ) RETURN RAW, STATIC FUNCTION construct RETURN mgwi_msglink, STATIC FUNCTION construct ( p_linkname IN VARCHAR2, p_properties IN sys.mgw_mqseries_properties, p_options IN sys.mgw_properties DEFAULT NULL, p_comment IN VARCHAR2 DEFAULT NULL ) RETURN mgwi_msglink, STATIC FUNCTION construct ( p_linkname IN VARCHAR2, p_properties IN sys.mgw_tibrv_properties, p_options IN sys.mgw_properties DEFAULT NULL, p_comment IN VARCHAR2 DEFAULT NULL ) RETURN mgwi_msglink, STATIC FUNCTION load_entry ( p_linkname IN VARCHAR2 ) RETURN mgwi_msglink, STATIC FUNCTION load_guid ( p_guid IN RAW ) RETURN mgwi_msglink, STATIC FUNCTION load_entry_decrypt_text ( p_linkname IN VARCHAR2, p_key IN RAW ) RETURN mgwi_msglink, MEMBER PROCEDURE insertData, MEMBER PROCEDURE deleteData, MEMBER PROCEDURE updateData, MEMBER PROCEDURE update_flags ( p_flags IN INTEGER ), MEMBER PROCEDURE normalizeData, MEMBER PROCEDURE decryptData ( p_keyText IN RAW, p_keyRaw IN RAW ), MEMBER PROCEDURE assignGuid ( p_guid IN RAW DEFAULT SYS_GUID ), MEMBER PROCEDURE loadMqseries, MEMBER PROCEDURE insertMqseries, MEMBER PROCEDURE deleteMqseries, MEMBER PROCEDURE updateMqseries, MEMBER PROCEDURE decryptMqseries ( p_keyText IN VARCHAR2, p_keyRaw IN RAW ), MEMBER PROCEDURE loadTibrv, MEMBER PROCEDURE insertTibrv, MEMBER PROCEDURE deleteTibrv, MEMBER PROCEDURE updateTibrv, MEMBER PROCEDURE decryptTibrv ( p_keyText IN VARCHAR2, p_keyRaw IN RAW ) ); / grant execute on mgwi_msglink to MGW_AGENT_ROLE; -- mgwi_foreignqueue -- drop type mgwi_foreignqueue; CREATE OR REPLACE TYPE mgwi_foreignqueue OID '00000000000000000000000000021091' AS OBJECT ( m_name VARCHAR2(30), m_link VARCHAR2(30), m_provider_queue VARCHAR2(128), m_domain INTEGER, m_options sys.mgw_properties, m_comment VARCHAR2(2000), m_guid RAW(16), m_flags INTEGER, STATIC FUNCTION entry_exists( p_qname IN VARCHAR2, p_linkname IN VARCHAR2 ) RETURN INTEGER, STATIC FUNCTION entry_guid ( p_qname IN VARCHAR2, p_linkname IN VARCHAR2 ) RETURN RAW, STATIC FUNCTION construct RETURN mgwi_foreignqueue, STATIC FUNCTION construct ( p_name IN VARCHAR2, p_link IN VARCHAR2, p_provider_queue IN VARCHAR2 DEFAULT NULL, p_domain IN INTEGER DEFAULT NULL, p_options IN sys.mgw_properties DEFAULT NULL, p_comment IN VARCHAR2 DEFAULT NULL ) RETURN mgwi_foreignqueue, STATIC FUNCTION load_entry ( p_name IN VARCHAR2, p_link IN VARCHAR2 ) RETURN mgwi_foreignqueue, STATIC FUNCTION load_guid ( p_guid IN RAW ) RETURN mgwi_foreignqueue, MEMBER PROCEDURE insertData, MEMBER PROCEDURE deleteData, MEMBER PROCEDURE update_flags ( p_flags IN INTEGER ), MEMBER PROCEDURE normalizeData, MEMBER PROCEDURE assignGuid ( p_guid IN RAW DEFAULT SYS_GUID ) ); / grant execute on mgwi_foreignqueue to MGW_AGENT_ROLE; -- mgwi_subscriber -- drop type mgwi_subscriber; CREATE OR REPLACE TYPE mgwi_subscriber OID '00000000000000000000000000021092' AS OBJECT ( m_id VARCHAR2(30), m_propType INTEGER, m_source VARCHAR2(61), m_destination VARCHAR2(61), m_rule VARCHAR2(4000), m_transformation VARCHAR2(61), m_exceptionQueue VARCHAR2(61), m_guid RAW(16), m_configState INTEGER, m_flags INTEGER, m_options sys.mgw_properties, STATIC FUNCTION entry_exists ( p_id IN VARCHAR2 ) RETURN INTEGER, STATIC FUNCTION entry_guid ( p_id IN VARCHAR2 ) RETURN RAW, STATIC FUNCTION construct RETURN mgwi_subscriber, STATIC FUNCTION construct ( p_id IN VARCHAR2, p_propType IN INTEGER, p_source IN VARCHAR2, p_destination IN VARCHAR2, p_rule IN VARCHAR2 DEFAULT NULL, p_transformation IN VARCHAR2 DEFAULT NULL, p_exceptionQueue IN VARCHAR2 DEFAULT NULL, p_options IN sys.mgw_properties DEFAULT NULL ) RETURN mgwi_subscriber, STATIC FUNCTION load_entry ( p_id IN VARCHAR2 ) RETURN mgwi_subscriber, STATIC FUNCTION load_guid ( p_guid IN RAW ) RETURN mgwi_subscriber, STATIC PROCEDURE delete_guid ( p_guid IN RAW ), MEMBER PROCEDURE insertData, MEMBER PROCEDURE deleteData, MEMBER PROCEDURE updateData, MEMBER PROCEDURE update_configState ( p_state IN BINARY_INTEGER ), MEMBER PROCEDURE update_flags ( p_flags IN INTEGER ), MEMBER PROCEDURE normalizeData, MEMBER PROCEDURE assignGuid ( p_guid IN RAW DEFAULT SYS_GUID ), MEMBER FUNCTION isOutbound RETURN INTEGER ); / grant execute on mgwi_subscriber to MGW_AGENT_ROLE; -- mgwi_schedule -- drop type mgwi_schedule; CREATE OR REPLACE TYPE mgwi_schedule OID '00000000000000000000000000021093' AS OBJECT ( m_id VARCHAR2(30), m_propType INTEGER, m_source VARCHAR2(61), m_destination VARCHAR2(61), m_startTime DATE, m_duration NUMBER, m_nextTime VARCHAR2(200), m_latency NUMBER, m_state INTEGER, m_guid RAW(16), m_flags INTEGER, STATIC FUNCTION entry_exists ( p_id IN VARCHAR2 ) RETURN INTEGER, STATIC FUNCTION entry_guid ( p_id IN VARCHAR2 ) RETURN RAW, STATIC FUNCTION construct RETURN mgwi_schedule, STATIC FUNCTION construct ( p_id IN VARCHAR2, p_propType IN INTEGER, p_source IN VARCHAR2, p_destination IN VARCHAR2, p_startTime IN DATE, p_duration IN NUMBER, p_nextTime IN VARCHAR2, p_latency IN NUMBER, p_state IN INTEGER ) RETURN mgwi_schedule, STATIC FUNCTION load_entry ( p_id IN VARCHAR2 ) RETURN mgwi_schedule, STATIC FUNCTION load_guid ( p_guid IN RAW ) RETURN mgwi_schedule, MEMBER PROCEDURE insertData, MEMBER PROCEDURE deleteData, MEMBER PROCEDURE updateData, MEMBER PROCEDURE updateState, MEMBER PROCEDURE update_flags ( p_flags IN INTEGER ), MEMBER PROCEDURE normalizeData, MEMBER PROCEDURE assignGuid ( p_guid IN RAW DEFAULT SYS_GUID ) ); / grant execute on mgwi_schedule to MGW_AGENT_ROLE; Rem ======================================================================== Rem Table modifications Rem ======================================================================== ALTER TABLE mgw$_gateway ADD ( max_threads integer default 1, flags integer default 0 not null, job_flags integer default 0 not null, agent_instance number, agent_start_time timestamp, last_instance number, last_start_time timestamp, last_agent_exitcode integer ) / ALTER TABLE mgw$_links ADD ( flags integer default 0 not null ) / ALTER TABLE mgw$_foreign_queues ADD ( flags integer default 0 not null ) / ALTER TABLE mgw$_subscribers ADD ( flags integer default 0 not null, options sys.mgw_properties ) / ALTER TABLE mgw$_schedules ADD ( flags integer default 0 not null ) / -- schedule latency first used by MGW agent starting 10.1.0. Set value for -- existing schedules to NULL so agent's default polling interval is used. update mgw$_schedules set latency = null; commit; Rem ======================================================================== Rem New Tables Rem ======================================================================== CREATE TABLE mgw$_tibrv_links ( link_name VARCHAR2(30) NOT NULL, service VARCHAR2(128), daemon VARCHAR2(128), network VARCHAR2(256), cm_name VARCHAR2(256), cm_ledger VARCHAR2(256), CONSTRAINT mgw$_tibrv_links_fk1 FOREIGN KEY (link_name) REFERENCES mgw$_links(link_name) ON DELETE CASCADE ); grant select, update on mgw$_tibrv_links to MGW_AGENT_ROLE; Rem ======================================================================== Rem Other Tasks Rem ======================================================================== -- grant privileges needed for OJMS LOB workaround grant UPDATE ANY TABLE to MGW_AGENT_ROLE; -- remove notification pipes used by MGW prior to 10i release -- note: dbms_pipe.remove_pipe returns 0 if pipe does not exist. It throws -- exception ORA-23322 if insufficient privileges, which should not happen. declare v_int integer; begin v_int := dbms_pipe.remove_pipe('MGW_NOTIFY_PIPE'); v_int := dbms_pipe.remove_pipe('MGW_RESPONSE_PIPE'); end; / Rem ======================================================================== Rem Chain to component's next "u" script Rem ======================================================================== Rem Perform the 10.1.0 to 10.2.0 upgrade actions @@mgwu101 Rem === End file: mgwu920.sql ================================================
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de