Oracle | DBMS

Oracle DBMS_MGWADM Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Oracle Messaging Gateway administration package.

This package and the messaging gateway is not installed by default using DBCA.

AUTHID DEFINER

Constants

Name	Data Type	Value
Agent Log Levels
BASIC_LOGGING	BINARY_INTEGER	0
TRACE_LITE_LOGGING	BINARY_INTEGER	1
TRACE_HIGH_LOGGING	BINARY_INTEGER	2
TRACE_DEBUG_LOGGING	BINARY_INTEGER	3
TRACE_DEBUG_HIGH_LOGGING	BINARY_INTEGER	4
Clean Up Actions
CLEAN_STARTUP_STATE	BINARY_INTEGER	1
CLEAN_LOG_QUEUES	BINARY_INTEGER	2
RESET_SUB_MISSING_LOG_REC	BINARY_INTEGER	3
RESET_SUB_MISSING_MESSAGE	BINARY_INTEGER	4
Default Agent
DEFAULT_AGENT	VARCHAR2(13)	'DEFAULT_AGENT'
Force Parameter
NO_FORCE	BINARY_INTEGER	0
FORCE	BINARY_INTEGER	1
Gateway Propagation Types
OUTBOUND_PROPAGATION	BINARY_INTEGER	1
INBOUND_PROPAGATION	BINARY_INTEGER	2
JDBC Connection Types
JDBC_OCI	VARCHAR2(10)	'oci'
JDBC_THIN	VARCHAR2(10)	'thin'
JMS Constants
JMS_QUEUE_CONNECTION	BINARY_INTEGER	8
JMS_TOPIC_CONNECTION	BINARY_INTEGER	9
JMS_CONNECTION	BINARY_INTEGER	10
Link/Queue Transactional Nature
TRANSACTIONAL	BINARY_INTEGER	1
NON_TRANSACTIONAL	BINARY_INTEGER	2
MQSeries Interface Types
MQSERIES_BASE_JAVA_INTERFACE1;	BINARY_INTEGER	1
Preserve Value
NO_CHANGE	VARCHAR2(16)	'<<!#NOcHAngE#!>>'
Queue Domain Types
DOMAIN_QUEUE	BINARY_INTEGER	1
DOMAIN_TOPIC	BINARY_INTEGER	2
Reserved Property Name
MGWPROP_PREFIX	VARCHAR2(10)	'MGWPROP$_'
MGWPROP_REMOVE	VARCHAR2(20)	'MGWPROP$_REMOVE'
MGWPROP_REMOVE_ALL	VARCHAR2(20)	'MGWPROP$_REMOVE_ALL'
Shutdown Modes
SHUTDOWN_NORMAL	BINARY_INTEGER	0
SHUTDOWN_IMMEDIATE	BINARY_INTEGER	1
SHUTDOWN_DATABASE	BINARY_INTEGER	2
SHUTDOWN_ROGUE	BINARY_INTEGER	3
Target Types
AGENT_JAVA_PROP	PLS_INTEGER	1
MSGLINK_OPTION	PLS_INTEGER	6
JOB_OPTION	PLS_INTEGER	7

Dependencies

MGWI_ADMIN	MGWI_SUBSCRIBER	MGW_PROPERTIES
MGWI_CRYPTO	MGW_MQSERIES_PROPERTIES	MGW_TIBRV_PROPERTIES
MGWI_MSGLINK	MGW_MSMQ_PROPERTIES

Documented Yes: Packages and Types Reference

Exceptions

Error Code	Reason
ORA-32800	ERRN_INTERNAL_ERROR
ORA-32801	ERRN_INVALID_PARAMETER
ORA-32802	ERRN_VALUE_MUST_BE
ORA-32803	ERRN_CANNOT_ALTER_PARAMETER
ORA-32804	ERRN_INVALID_NAME_FORMAT
ORA-32805	ERRN_IDENTIFIER_TOO_LONG
ORA-32806	ERRN_VALUE_TOO_LONG
ORA-32807	ERRN_MSGLINK_EXISTS
ORA-32808	ERRN_MSGLINK_NOT_EXIST
ORA-32809	ERRN_FQUEUE_REGISTERED
ORA-32810	ERRN_FQUEUE_NOT_REGISTERED
ORA-32811	ERRN_SUBSCRIBER_EXISTS
ORA-32812	ERRN_SUBSCRIBER_NOT_EXIST
ORA-32813	ERRN_SCHEDULE_EXISTS
ORA-32814	ERRN_SCHEDULE_NOT_EXIST
ORA-32815	ERRN_LINK_REFBY_FQUEUE
ORA-32816	ERRN_FQUEUE_REFBY_SUB_SCHED
ORA-32817	ERRN_LINK_LOGQ_NOT_SET
ORA-32818	ERRN_AQ_QUEUE_NOT_EXIST
ORA-32819	ERRN_AQ_NORMAL_QUEUE
ORA-32820	ERRN_SUBS_EXCEPTIONQ_LINK
ORA-32821	ERRN_SUBS_EXCEPTIONQ_PAYLOAD
ORA-32822	ERRN_SUBS_REMOVE_PENDING
ORA-32823	ERRN_SUBSCRIBER_SET_EXISTS
ORA-32824	ERRN_SCHEDULE_SET_EXISTS
ORA-32825	ERRN_GATEWAY_NOT_STARTED
ORA-32826	ERRN_GATEWAY_ALREADY_STARTED
ORA-32827	ERRN_SHUTDOWN_GATEWAY
ORA-32828	ERRN_RUNNING_GATEWAY
ORA-32829	ERRN_STARTING_NO_SHUTDOWN
ORA-32830	ERRN_GATEWAY_RESULT
ORA-32831	ERRN_ADMIN_LOCK_TIMEOUT
ORA-32832	ERRN_ADMIN_LOCK_FAILED
ORA-32833	ERRN_ADMIN_UNLOCK_FAILED
ORA-32834	ERRN_AGENT_USER_NOT_SET
ORA-32835	ERRN_DB_USER_NOT_EXIST
ORA-32836	ERRN_DB_USER_NEEDS_ROLE
ORA-32837	ERRN_INVALID_CONFIG_STATE
ORA-32838	ERRN_MAX_PROP_EXCEEDED
ORA-32839	ERRN_RESERVED_PROP_NAME
ORA-32840	ERRN_NULL_PROP_NAME
ORA-32841	ERRN_INVALID_PROP_VALUE
ORA-32842	ERRN_CANNOT_ALTER_PROP
ORA-32843	ERRN_OUT_OF_RANGE
ORA-32844	ERRN_OUT_OF_VALUES
ORA-32845	ERRN_AGENT_ALREADY_RUNNING
ORA-32846	ERRN_CANNOT_START_BAD_STATUS
ORA-32847	ERRN_PLATFORM_NOT_SUPPORTED
ORA-32848	ERRN_NO_JMS_QUEUE_DOMAIN
ORA-32849	ERRN_LINKED_PARAMETERS
ORA-32850	ERRN_AGENT_NOT_EXIST
ORA-32851	ERRN_AGENT_EXISTS
ORA-32852	ERRN_AGENT_REFBY_LINK
ORA-32853	ERRN_PROPJOB_NOT_EXIST
ORA-32854	ERRN_PROPJOB_EXISTS
ORA-32855	ERRN_PROPJOB_REMOVE_PENDING
ORA-32856	ERRN_PROPJOB_OP_NOT_SUPPORTED
ORA-32857	ERRN_PROPJOB_SET_EXISTS

First Available 9.2

Security Model Owned by SYS with EXECUTE granted to the MGW_ADMINISTRATOR_ROLE role

Source {ORACLE_HOME}/mgw/admin/mgwadm.sql
Install from $ORACLE_HOME/mgw/admin/catmgw.sql

Subprograms

ADD_SUBSCRIBER	DISABLE_JOB	RESET_JOB
ALTER_AGENT	DISABLE_PROPAGATION_SCHEDULE	RESET_SUBSCRIBER
ALTER_JOB	ENABLE_JOB	SCHEDULE_PROPAGATION
ALTER_MSGSYSTEM_LINK	ENABLE_PROPAGATION_SCHEDULE	SET_LOG_EVENT
ALTER_PROPAGATION_SCHEDULE	REGISTER_FOREIGN_QUEUE	SET_LOG_LEVEL
ALTER_SUBSCRIBER	REMOVE_AGENT	SET_OPTION
CLEANUP_GATEWAY	REMOVE_JOB	SHUTDOWN
CREATE_AGENT	REMOVE_MSGSYSTEM_LINK	STARTUP
CREATE_JOB	REMOVE_OPTION	UNREGISTER_FOREIGN_QUEUE
CREATE_MSGSYSTEM_LINK	REMOVE_SUBSCRIBER	UNSCHEDULE_PROPAGATION
DB_CONNECT_INFO

ADD_SUBSCRIBER

Adds a subscriber used to consume messages from a source queue for propagation to a destination. dbms_mgwadm.add_subscriber( subscriber_id IN VARCHAR2, propagation_type IN BINARY_INTEGER, queue_name IN VARCHAR2, destination IN VARCHAR2, rule IN VARCHAR2 DEFAULT NULL, transformation IN VARCHAR2 DEFAULT NULL, exception_queue IN VARCHAR2 DEFAULT NULL, options IN sys.mgw_properties DEFAULT NULL);

BEGIN

        dbms_mgwadm.add_subscriber(subscriber_id       => 'uwclass',

                                   propagation_type => dbms_mgwadm.outbound_propagation,

                                   queue_name       => 'uw_mgw.sq',

                                   destination      => 'uw_mgw@uw_remote',

                                   transformation   => 'uw_mgw.test_trans'),

                                   exception_queue   => 'uw_mgw.eq');

      END;

      /

ALTER_AGENT

Alter Messaging Gateway agent parameters

The password parameter is plain text so be very careful with this procedure if required

Overload 1 dbms_mgwadm.alter_agent( agent_name IN VARCHAR2, username IN VARCHAR2 DEFAULT DBMS_MGWADM.NO_CHANGE, password IN VARCHAR2 DEFAULT DBMS_MGWADM.NO_CHANGE, database IN VARCHAR2 DEFAULT DBMS_MGWADM.NO_CHANGE, conntype IN VARCHAR2 DEFAULT DBMS_MGWADM.NO_CHANGE, max_memory IN PLS_INTEGER DEFAULT NULL, max_threads IN PLS_INTEGER DEFAULT NULL, service IN VARCHAR2 DEFAULT DBMS_MGWADM.NO_CHANGE, initfile IN VARCHAR2 DEFAULT DBMS_MGWADM.NO_CHANGE, comment IN VARCHAR2 DEFAULT DBMS_MGWADM.NO_CHANGE);

TBD

Overload 2 dbms_mgwadm.alter_agent( max_connections IN BINARY_INTEGER DEFAULT NULL, -- deprecated parameter max_memory IN BINARY_INTEGER DEFAULT NULL, max_threads IN BINARY_INTEGER DEFAULT NULL, service IN VARCHAR2 DEFAULT DBMS_MGWADM.NO_CHANGE);

exec dbms_mgwadm.alter_agent(NULL, 512, 128, 'UW_MGW_AGENT');

ALTER_JOB

Alters the properties of a propagation job dbms_mgwadm.alter_job( job_name IN VARCHAR2, rule IN VARCHAR2 DEFAULT dbms_mgwadm.NO_CHANGE, transformation IN VARCHAR2 DEFAULT dbms_mgwadm.NO_CHANGE, exception_queue IN VARCHAR2 DEFAULT dbms_mgwadm.NO_CHANGE, poll_interval IN PLS_INTEGER DEFAULT 0, options IN sys.mgw_properties DEFAULT NULL, comments IN VARCHAR2 DEFAULT dbms_mgwadm.no_change);

TBD

ALTER_MSGSYSTEM_LINK

Alters properties of MQSeries messaging system link

Overload 1 dbms_mgwadm.alter_msgsystem_link( linkname IN VARCHAR2, properties IN sys.mgw_mqseries_properties, options IN sys.mgw_properties DEFAULT NULL, comment IN VARCHAR2 DEFAULT dbms_mgwadm.no_change);

TBD

Alters properties of TIB/Rendezvous messaging system link

Overload 2 dbms_mgwadm.alter_msgsystem_link( linkname IN VARCHAR2, properties IN sys.mgw_tibrv_properties, options IN sys.mgw_properties DEFAULT NULL, comment IN VARCHAR2 DEFAULT dbms_mgwadm.NO_CHANGE);

TBD

Alters properties of Microsoft MSMQ messaging system link

Overload 3 dbms_mgwadm.alter_msgsystem_link( linkname IN VARCHAR2, properties IN sys.mgw_msmq_properties, options IN sys.mgw_properties DEFAULT NULL, comment IN VARCHAR2 DEFAULT dbms_mgwadm.NO_CHANGE);

TBD

ALTER_PROPAGATION_SCHEDULE

Alters parameters of a propagation schedule dbms_mgwadm.alter_propagation_schedule( schedule_id IN VARCHAR2, duration IN NUMBER DEFAULT NULL, next_time IN VARCHAR2 DEFAULT NULL, latency IN NUMBER DEFAULT NULL);

TBD

ALTER_SUBSCRIBER

Alters the parameters of a subscriber used to consume messages from a source queue for propagation to a destination dbms_mgwadm.alter_subscriber( subscriber_id IN VARCHAR2, rule IN VARCHAR2 DEFAULT dbms_mgwadm.NO_CHANGE, transformation IN VARCHAR2 DEFAULT dbms_mgwadm.NO_CHANGE, exception_queue IN VARCHAR2 DEFAULT dbms_mgwadm.NO_CHANGE, options IN sys.mgw_properties DEFAULT NULL);

TBD

CLEANUP_GATEWAY

Clean Up Gateway
Overload 1 dbms_mgwadm.cleanup_gateway( action IN BINARY_INTEGER, sarg IN VARCHAR2 DEFAULT NULL);

exec dbms_mgwadm.cleanup_gateway(dbms_mgwadm.clean_startup_state);

Overload 2 dbms_mgwadm.cleanup_gateway( agent_name IN VARCHAR2, action IN BINARY_INTEGER, sarg IN VARCHAR2 DEFAULT NULL);

exec dbms_mgwadm.cleanup_gateway('UW_MGW_AGENT', dbms_mgwadm.clean_startup_state);

CREATE_AGENT

Create Gateway Named Agent dbms_mgwadm.create_agent( agent_name IN VARCHAR2, username IN VARCHAR2 DEFAULT NULL, password IN VARCHAR2 DEFAULT NULL, database IN VARCHAR2 DEFAULT NULL, conntype IN VARCHAR2 DEFAULT DBMS_MGWADM.JDBC_OCI, max_memory IN PLS_INTEGER DEFAULT 64, max_threads IN PLS_INTEGER DEFAULT 1, service IN VARCHAR2 DEFAULT NULL, initfile IN VARCHAR2 DEFAULT NULL, comment IN VARCHAR2 DEFAULT NULL);

TBD

CREATE_JOB

Creates a propagation job dbms_mgwadm.create_job( job_name IN VARCHAR2, propagation_type IN PLS_INTEGER, source IN VARCHAR2, destination IN VARCHAR2, rule IN VARCHAR2 DEFAULT NULL, transformation IN VARCHAR2 DEFAULT NULL, exception_queue IN VARCHAR2 DEFAULT NULL, poll_interval IN PLS_INTEGER DEFAULT NULL, options IN sys.mgw_properties DEFAULT NULL, enabled IN BOOLEAN DEFAULT TRUE, comments IN VARCHAR2 DEFAULT NULL);

TBD

CREATE_MSGSYSTEM_LINK

Creates a link for a MQSeries messaging system

Overload 1 dbms_mgwadm.create_msgsystem_link( linkname IN VARCHAR2, properties IN sys.mgw_mqseries_properties, options IN sys.mgw_properties DEFAULT NULL, comment IN VARCHAR2 DEFAULT NULL);

TBD

Creates a link for a MQSeries messaging system

Overload 2 dbms_mgwadm.create_msgsystem_link( linkname IN VARCHAR2, agent_name IN VARCHAR2, properties IN sys.mgw_mqseries_properties, options IN sys.mgw_properties DEFAULT NULL, comment IN VARCHAR2 DEFAULT NULL);

TBD

Creates a link for a TIB/Rendezvous messaging system

Overload 3 dbms_mgwadm.create_msgsystem_link( linkname IN VARCHAR2, properties IN sys.mgw_tibrv_properties, options IN sys.mgw_properties DEFAULT NULL, comment IN VARCHAR2 DEFAULT NULL);

TBD

Creates a link for a TIB/Rendezvous messaging system

Overload 4 dbms_mgwadm.create_msgsystem_link( linkname IN VARCHAR2, agent_name IN VARCHAR2, properties IN sys.mgw_tibrv_properties, options IN sys.mgw_properties DEFAULT NULL, comment IN VARCHAR2 DEFAULT NULL);

TBD

Creates a link for a Microsoft MSMQ messaging system

Overload 5 dbms_mgwadm.create_msgsystem_link( linkname IN VARCHAR2, properties IN sys.mgw_msmq_properties, options IN sys.mgw_properties DEFAULT NULL, comment IN VARCHAR2 DEFAULT NULL);

TBD

Creates a link for a Microsoft MSMQ messaging system

Overload 6 dbms_mgwadm.create_msgsystem_link( linkname IN VARCHAR2, agent_name IN VARCHAR2, properties IN sys.mgw_msmq_properties, options IN sys.mgw_properties DEFAULT NULL, comment IN VARCHAR2 DEFAULT NULL);

TBD

DB_CONNECT_INFO

Configure database connection information for the Message Gateway agent dbms_mgwadm.db_connect_info( username IN VARCHAR2, password IN VARCHAR2, database IN VARCHAR2 DEFAULT NULL);

Deprecated: Instead use ALTER_AGENT

DISABLE_JOB

Disables a propagation job dbms_mgwadm.disable_job(job_name IN VARCHAR2);

TBD

DISABLE_PROPAGATION_SCHEDULE

Disables a propagation schedule dbms_mgwadm.disable_propagation_schedule(schedule_id IN VARCHAR2);

TBD

ENABLE_JOB

Enables a propagation job dbms_mgwadm.enable_job(job_name IN VARCHAR2);

TBD

ENABLE_PROPAGATION_SCHEDULE<

Enables a propagation schedule dbms_mgwadm.enable_propagation_schedule(schedule_id IN VARCHAR2);

TBD

REGISTER_FOREIGN_QUEUE

Registers a non-Oracle queue entity in Messaging Gateway dbms_mgwadm.register_foreign_queue( name IN VARCHAR2, linkname IN VARCHAR2, provider_queue IN VARCHAR2 DEFAULT NULL, domain IN INTEGER DEFAULT NULL, options IN sys.mgw_properties DEFAULT NULL, comment IN VARCHAR2 DEFAULT NULL);

TBD

REMOVE_AGENT

Drop Gateway Named Agent dbms_mgwadm.remove_agent(agent_name IN VARCHAR2);

exec dbms_mgwadm.remove_agent('UW_MGW_AGENT');

REMOVE_JOB

Removes a properties job dbms_mgwadm.remove_job( job_name IN VARCHAR2, force IN BINARY_INTEGER DEFAULT dbms_mgwadm.no_force);

exec dbms_mgwadm.remove_job('UW_MSG_JOB', dbms_mgwadm.force);

REMOVE_MSGSYSTEM_LINK

Removes a messaging system link dbms_mgwadm.remove_msgsystem_link(linkname IN VARCHAR2);

execdbms_mgwadm.remove_msgsystem_link('UW_MSG_LINK');

REMOVE_OPTION

Removes a Messaging Gateway configuration option dbms_mgwadm.remove_option( target_type IN PLS_INTEGER, target_name IN VARCHAR2, option_name IN VARCHAR2);

TBD

REMOVE_SUBSCRIBER

Removes a subscriber used to consume messages from a source queue for propagation to a destination dbms_mgwadm.remove_subscriber( subscriber_id IN VARCHAR2, force IN BINARY_INTEGER DEFAULT dbms_mgwadm.no_force);

TBD

RESET_JOB

Resets the propagation error state of a propagation job dbms_mgwadm.reset_job(job_name IN VARCHAR2);

TBD

RESET_SUBSCRIBER

Resets the propagation error state for a subscriber dbms_mgwadm.reset_subscriber(subscriber_id IN VARCHAR2);

TBD

SCHEDULE_PROPAGATION

Schedules propagation of messages from a source to destination dbms_mgwadm.schedule_propagation( schedule_id IN VARCHAR2, propagation_type IN BINARY_INTEGER, source IN VARCHAR2, destination IN VARCHAR2, start_time IN DATE DEFAULT SYSDATE, duration IN NUMBER DEFAULT NULL, next_time IN VARCHAR2 DEFAULT NULL, latency IN NUMBER DEFAULT NULL);

TBD

SET_LOG_EVENT

Set Log Event
Overload 1 dbms_mgwadm.set_log_event( log_event IN INTEGER, component IN INTEGER);

TBD

Overload 2 dbms_mgwadm.set_log_event( agent_name IN VARCHAR2, log_event IN INTEGER, component IN INTEGER);

TBD

SET_LOG_LEVEL

Set Log Level
Overload 1 dbms_mgwadm.set_log_level(log_level IN BINARY_INTEGER);

exec dbms_mgwadm.set_log_level(dbms_mgwadm.basic_logging);

Overload 2 dbms_mgwadm.set_log_level( agent_name IN VARCHAR2, log_level IN BINARY_INTEGER);

exec dbms_mgwadm.set_log_level('UW_MGW_AGENT', dbms_mgwadm.trace_debug_logging);

SET_OPTION

Sets a Messaging Gateway configuration option dbms_mgwadm.set_option( target_type IN PLS_INTEGER, target_name IN VARCHAR2, option_name IN VARCHAR2, option_value IN VARCHAR2, encrypted IN BOOLEAN DEFAULT FALSE);

TBD

SHUTDOWN

Shutdown the Messaging Gateway
Overload 1 dbms_mgwadm.shutdown(sdmode IN BINARY_INTEGER DEFAULT dbms_mgwadm.SHUTDOWN_NORMAL);

TBD

Overload 2 dbms_mgwadm.shutdown(agent_name IN VARCHAR2);

exec dbms_mgwadm.shutdown('UW_MGW_AGENT');

STARTUP

Start the Messaging Gateway
Overload 1 dbms_mgwadm.startup( instance IN BINARY_INTEGER DEFAULT 0, force IN BINARY_INTEGER DEFAULT dbms_mgwadm.no_force);

exec dbms_mgwadm.startup(0, dbms_mgwadm.force);

Overload 2 exec dbms_mgwadm.startup(agent_name IN VARCHAR2);

exec dbms_mgwadm.startup('UW_MGW_AGENT');

UNREGISTER_FOREIGN_QUEUE

Removes a non-Oracle queue entity in Messaging Gateway dbms_mgwadm.register_foreign_queue( name IN VARCHAR2, linkname IN VARCHAR2);

TBD

UNSCHEDULE_PROPAGATION

Removes a propagation schedule dbms_mgwadm.unschedule_propagation(schedule_id IN VARCHAR2);

TBD