Oracle | DBMS

Oracle DBMS_MGWADM Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai is now availble. If you haven't you downloaded already: Why?

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 ReferencePackages and Types Documentation

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;

      /

      *

      ORA-32818: AQ queue UW_MGW.SQ does not exist

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);

      

      PL/SQL procedure successfully completed.

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);

      

      PL/SQL procedure successfully completed.

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');

      

      PL/SQL procedure successfully completed.

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);

      

      PL/SQL procedure successfully completed.

REMOVE_MSGSYSTEM_LINK

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

exec dbms_mgwadm.remove_msgsystem_link('UW_MSG_LINK');

      

      PL/SQL procedure successfully completed.

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);

      

      PL/SQL procedure successfully completed.

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);

      

      PL/SQL procedure successfully completed.

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');

      

      PL/SQL procedure successfully completed.

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);

      

      PL/SQL procedure successfully completed.

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

exec dbms_mgwadm.startup('UW_MGW_AGENT');

      

      PL/SQL procedure successfully completed.

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