Oracle DBMS_MGWMDG
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 Provides object types used by the canonical message types to convert message bodies, and methods, constants, and subprograms for working with Messaging Gateway message types.
AUTHID DEFINER
Constants
Name Data Type Value
BOOLEAN Value Representations
BOOLEAN_FALSE BINARY_INTEGER 0
 BOOLEAN_TRUE BINARY_INTEGER 1
Java Numeric Ranges
JAVA_BYTE_MINVAL-- 8-bit BINARY_INTEGER 128
JAVA_BYTE_MAXVAL BINARY_INTEGER 127
JAVA_SHORT_MINVAL-- 16-bit INTEGER 32768
JAVA_SHORT_MAXVAL INTEGER 32767
JAVA_INT_MINVAL-- 32-bit INTEGER 2147483648
JAVA_INT_MAXVAL INTEGER 2147483647
String Comparison
CASE_SENSITIVE BINARY_INTEGER 0
CASE_INSENSITIVE BINARY_INTEGER 1
Valid Values For mgw_name_value_t.type Attribute
TEXT_VALUE BINARY_INTEGER 1
RAW_VALUE BINARY_INTEGER 2
BOOLEAN_VALUE BINARY_INTEGER 3
BYTE_VALUE BINARY_INTEGER 4
SHORT_VALUE BINARY_INTEGER 5
INTEGER_VALUE BINARY_INTEGER 6
LONG_VALUE BINARY_INTEGER 7
FLOAT_VALUE BINARY_INTEGER 8
DOUBLE_VALUE BINARY_INTEGER 9
DATE_VALUE CONSTANT BINARY_INTEGER BINARY_INTEGER 10
Valid Values For mgw_tibrv_field_t.field_type Attribute
TIBRVMSG_UNKNOWN BINARY_INTEGER 0
TIBRVMSG_BOOL BINARY_INTEGER 1
TIBRVMSG_F32 BINARY_INTEGER 2
TIBRVMSG_F64 BINARY_INTEGER 3
TIBRVMSG_I8 BINARY_INTEGER 4
TIBRVMSG_I16 BINARY_INTEGER 5
TIBRVMSG_I32 BINARY_INTEGER 6
TIBRVMSG_I64 BINARY_INTEGER 7
TIBRVMSG_IPADDR32 BINARY_INTEGER 8
TIBRVMSG_IPPORT16 BINARY_INTEGER 9
TIBRVMSG_DATETIME BINARY_INTEGER 10
TIBRVMSG_F32ARRAY BINARY_INTEGER 11
TIBRVMSG_F64ARRAY BINARY_INTEGER 12
TIBRVMSG_I8ARRAY BINARY_INTEGER 13
TIBRVMSG_I16ARRAY BINARY_INTEGER 14
TIBRVMSG_I32ARRAY BINARY_INTEGER 15
TIBRVMSG_I64ARRAY BINARY_INTEGER 16
TIBRVMSG_OPAQUE BINARY_INTEGER 17
TIBRVMSG_STRING BINARY_INTEGER 18
TIBRVMSG_XML BINARY_INTEGER 19
Data Types SQL> desc mgw_name_value_array_t
mgw_name_value_array_t VARRAY(1024) OF MGW_NAME_VALUE_T

Name                       Null?    Type
-------------------------- -------- ---------------
NAME                                VARCHAR2(250)
TYPE                                NUMBER(38)
INTEGER_VALUE                       NUMBER(38)
NUMBER_VALUE                        NUMBER
TEXT_VALUE                          VARCHAR2(4000)
RAW_VALUE                           RAW(2000)
DATE_VALUE                          DATE
Dependencies
ANYDATA MGW_NAME_VALUE_ARRAY_T MGW_TIBRV_MSG_T
DBMS_STREAMS_LCR_INT MGW_NAME_VALUE_T PLITBLM
DBMS_SYS_ERROR MGW_NUMBER_ARRAY_T XMLTYPE
Documented No
First Available 9.2
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/mgw/admin/mgwmsg.sql
Install from $ORACLE_HOME/mgw/admin/catmgw.sql
Subprograms
 
CHECK_RANGE
If the value specified is within the range no exception is raised

Overload 1
dbms_mgwmsg.check_range (
p_value      IN INTEGER,
p_minval     IN INTEGER,
p_maxval     IN INTEGER,
p_errm_value IN VARCHAR2);
SQL> exec dbms_mgwmsg.check_range(42, 1, 100, 'Limit');

PL/SQL procedure successfully completed.

SQL> exec dbms_mgwmsg.check_range(42, 1, 10, 'Limit');
BEGIN dbms_mgwmsg.check_range(42, 1, 10, 'Limit'); END;
*
ERROR at line 1:
ORA-32842: value for property Limit cannot be altered
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_MGWMSG", line 475
ORA-06512: at line 1
Overload 2 dbms_mgwmsg.check_range (
p_numarray   IN sys.mgw_number_array_t,
p_minval     IN INTEGER,
p_maxval     IN INTEGER,
p_errm_value IN VARCHAR2);
TBD
 
IS_SAME
Returns TRUE if two strings are identical based on the selected case sensitivity dbms_mgwmsg.is_same (
p_str1    IN VARCHAR2,
p_str2    IN VARCHAR2,
p_compare IN BINARY_INTEGER DEFAULT CASE_SENSITIVE)
RETURN BOOLEAN;
BEGIN
  IF dbms_mgwmsg.is_same('Dan Morgan', 'Dan MorgAn', dbms_mgwmsg.case_insensitive) THEN
    dbms_output.put_line('Is');
  ELSE
    dbms_output.put_line('Is Not');
  END IF;

  IF dbms_mgwmsg.is_same('Dan Morgan', 'Dan MorgAn', dbms_mgwmsg.case_sensitive) THEN
    dbms_output.put_line('Is');
  ELSE
    dbms_output.put_line('Is Not');
  END IF;
END;
/
 
LCR_TO_XML
Converts a SYS.ANYDATA object encapsulating a row LCR (LCR$_ROW_RECORD) or a DDL LCR (LCR$_DDL_RECORD) to a SYS.XMLTYPE object

Note: An exception is raised if the encapsulated type, p_anydata, is not an LCR as demonstrated
dbms_mgwmsg.lcr_to_xml(p_anydata IN sys.anydata)
RETURN sys.xmltype;
SQL> DECLARE
  2   inVal anydata := sys.anyData.convertDate(SYSDATE);
  3   retVal XMLTYPE;
  4  BEGIN
  5    retVal := dbms_mgwmsg.lcr_to_xml(inVal);
  6  END;
  7  /
DECLARE
*
ERROR at line 1:
ORA-21560: argument ANYLCR is null, invalid, or out of range
ORA-06512: at "SYS.DBMS_STREAMS_LCR_INT", line 19
ORA-06512: at "SYS.DBMS_STREAMS_LCR_INT", line 44
ORA-06512: at "SYS.DBMS_MGWMSG", line 566
ORA-06512: at line 5
 
NVARRAY_ADD
Appends a name-value element to the end of a name-value array dbms_mgwmsg.nvarray_add(
p_array IN OUT sys.mgw_name_value_array_t,
p_value IN     sys.mgw_name_value_t);
DECLARE
 x sys.mgw_name_value_array_t;
 y sys.mgw_name_value_t;
 z VARCHAR2(60) := 'Author';
 retVal VARCHAR2(30);
BEGIN
  y := sys.mgw_name_value_t('Author',1,42,3.14,'Dan Morgan',NULL,SYSDATE);
  dbms_mgwmsg.nvarray_add(x, y);

  retVal := '[' || dbms_mgwmsg.nvarray_get_text(x, z, dbms_mgwmsg.case_insensitive) || ']';
  dbms_output.put_line(retVal);

  retVal := '[' || dbms_mgwmsg.nvarray_find_name(x, z, dbms_mgwmsg.case_insensitive) || ']';
  dbms_output.put_line(retVal);

  retVal := '[' || dbms_mgwmsg.nvarray_find_name_type(x, z, dbms_mgwmsg.case_insensitive) || ']';
  dbms_output.put_line(retVal);

  retVal := '[' || dbms_mgwmsg.nvarray_get_date(x, z, dbms_mgwmsg.case_insensitive) || ']';
  dbms_output.put_line(retVal);

  retVal := '[' || dbms_mgwmsg.nvarray_get_integer(x, z, dbms_mgwmsg.case_insensitive) || ']';
  dbms_output.put_line(retVal);

  dbms_output.put_line(x(1).date_value);
  dbms_output.put_line(x(1).integer_value);
  -- need to determine why the last 2 demos fail to return the value
END;
/
 
NVARRAY_FIND_NAME
Returns a name-value array for the element with the name specified in p_name dbms_mgwmsg.nvarray_find_name (
p_array   IN sys.mgw_name_value_array_t,
p_name    IN VARCHAR2,
p_compare IN BINARY_INTEGER DEFAULT CASE_SENSITIVE )
RETURN BINARY_INTEGER;
 
 
NVARRAY_FIND_NAME_TYPE
Returns a name-value array for an element with the name and value type specified dbms_mgwmsg.nvarray_find_name_type(
p_array   IN sys.mgw_name_value_array_t,
p_name    IN VARCHAR2,
p_type    IN BINARY_INTEGER,
p_compare IN BINARY_INTEGER DEFAULT CASE_SENSITIVE)
RETURN BINARY_INTEGER;
See NVARRAY_ADD Demo above
 
NVARRAY_GET
Returns the name-value element of the name specified by p_name from a name-value array dbms_mgwmsg.nvarray_get(
p_array   IN sys.mgw_name_value_array_t,
p_name    IN VARCHAR2,
p_compare IN BINARY_INTEGER DEFAULT CASE_SENSITIVE)
RETURN sys.mgw_name_value_t;
TBD
 
NVARRAY_GET_BOOLEAN
Returnss the value of the name-value array element specified in p_name and with the BOOLEAN_VALUE value type dbms_mgwmsg.nvarray_get_boolean(
p_array   IN sys.mgw_name_value_array_t,
p_name    IN VARCHAR2,
p_compare IN BINARY_INTEGER DEFAULT CASE_SENSITIVE)
RETURN INTEGER;
TBD
 
NVARRAY_GET_BYTE
Returnss the value of the name-value array element specified in p_name and with the BYTE_VALUE value type dbms_mgwmsg.varray_get_byte(
p_array   IN sys.mgw_name_value_array_t,
p_name    IN VARCHAR2,
p_compare IN BINARY_INTEGER DEFAULT CASE_SENSITIVE)
RETURN INTEGER;
TBD
 
NVARRAY_GET_DATE
Returnss the value of the name-value array element specified in p_name and with the Date_VALUE value type dbms_mgwmsg.nvarray_get_date(
p_array   IN sys.mgw_name_value_array_t,
p_name    IN VARCHAR2,
p_compare IN BINARY_INTEGER DEFAULT CASE_SENSITIVE)
RETURN DATE;
See NVARRAY_ADD Demo above
 
NVARRAY_GET_DOUBLE
Returnss the value of the name-value array element specified in p_name and with the DOUBLE_VALUE value type dbms_mgwmsg.nvarray_get_double(
p_array   IN sys.mgw_name_value_array_t,
p_name    IN VARCHAR2,
p_compare IN BINARY_INTEGER DEFAULT CASE_SENSITIVE)
RETURN NUMBER;
See NVARRAY_ADD Demo above
 
NVARRAY_GET_FLOAT
Returnss the value of the name-value array element specified in p_name and with the FLOAT_VALUE value type dbms_mgwmsg.nvarray_get_float(
p_array   IN sys.mgw_name_value_array_t,
p_name    IN VARCHAR2,
p_compare IN BINARY_INTEGER DEFAULT CASE_SENSITIVE)
RETURN NUMBER;
See NVARRAY_ADD Demo above
 
NVARRAY_GET_INTEGER
Returnss the value of the name-value array element specified in p_name and with the INTEGER_VALUE value type dbms_mgwmsg.nvarray_get_integer(
p_array   IN sys.mgw_name_value_array_t,
p_name    IN VARCHAR2,
p_compare IN BINARY_INTEGER DEFAULT CASE_SENSITIVE)
RETURN INTEGER;
See NVARRAY_ADD Demo above
 
NVARRAY_GET_LONG
Returnss the value of the name-value array element specified in p_name and with the LONG_VALUE value type dbms_mgwmsg.nvarray_get_long(
p_array   IN sys.mgw_name_value_array_t,
p_name    IN VARCHAR2,
p_compare IN BINARY_INTEGER DEFAULT CASE_SENSITIVE)
RETURN NUMBER;
TBD
 
NVARRAY_GET_RAW
Returns the value of the name-value array element specified in p_name and with the RAW_VALUE value type dbms_mgwmsg.nvarray_get_raw(
p_array   IN sys.mgw_name_value_array_t,
p_name    IN VARCHAR2,
p_compare IN BINARY_INTEGER DEFAULT CASE_SENSITIVE)
RETURN RAW;
TBD
 
NVARRAY_GET_SHORT
Returnss the value of the name-value array element specified in p_name and with the SHORT_VALUE value type dbms_mgwmsg.nvarray_get_short(
p_array   IN sys.mgw_name_value_array_t,
p_name    IN VARCHAR2,
p_compare IN BINARY_INTEGER DEFAULT CASE_SENSITIVE)
RETURN INTEGER;
See NVARRAY_ADD Demo above
 
NVARRAY_GET_TEXT
Returnss the value of the name-value array element specified in p_name and with the TEXT_VALUE value type dbms_mgwmsg.nvarray_get_text(
p_array   IN sys.mgw_name_value_array_t,
p_name    IN VARCHAR2,
p_compare IN BINARY_INTEGER DEFAULT CASE_SENSITIVE)
RETURN VARCHAR2;
See NVARRAY_ADD Demo above
 
XML_TO_LCR
Converts XML to a logical change record but keep in mind the restriction encapsulated in the restriction demonstrated in the demo at right dbms_mgwmsg.xml_to_lcr(p_xmldata IN sys.xmltype)
RETURN sys.anydata;
SQL> DECLARE
  2   c CLOB := '<?xml version="1.0" encoding="utf-8"?>
  3              <ShoppingCartData xmlns:xsd="http://www.w3.org/2001/XMLSchema"
  4              xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  5              <Id>ABCDEFG</Id>
  6              <SiteId>1</SiteId>
  7              <ProductId>100</ProductId>
  8              <Quantity>2</Quantity>
  9              </ShoppingCartData>';
 10   x      XMLType;
 11   retVal sys.anyData;
 12 BEGIN
 13   x := xmlType.createXML(c);
 14   retVal := dbms_mgwmsg.xml_to_lcr(x);
 15  END;
 16  /
DECLARE
*
ERROR at line 1:
ORA-19030: Method invalid for non-schema based XML Documents.
ORA-06512: at "SYS.DBMS_STREAMS_LCR_INT", line 9
ORA-06512: at "SYS.DBMS_STREAMS_LCR_INT", line 62
ORA-06512: at "SYS.DBMS_MGWMSG", line 588
ORA-06512: at line 15

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