Oracle DBMS_MGWADM
Version 18.3.0.1

General Information
Library Note Morgan's Library Page Header
Coming to OpenWorld 2018? Be sure to visit the TidalScale booth in Moscone South and learn how to solve performance problems and lower costs with Software Defined Servers. Before you visit the booth, or if you can't make it this year, check out TidalScale at www.tidalscale.com. Be sure to click on the Solutions link and look through the Oracle resources.
Purpose Oracle Messaging Gateway administration package
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
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
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

Related Topics
Built-in Functions
Built-in Packages
DBMS_MGWMSG
What's New In 12cR2
What's New In 18cR3

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved