PK
CAoa, mimetypeapplication/epub+zipPK CA iTunesMetadata.plistl
Use the Oracle Visual Workbench for developing applications that access WebSphere MQ through the gateway. The Oracle Visual Workbench defines an interface for accessing WebSphere MQ and automatically generates the PL/SQL code (the MIP) for Oracle applications to interface with the gateway. Refer to the Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User's Guide for Microsoft Windows (32-Bit) for more information about Oracle Visual Workbench. This appendix includes the following sections:
Messages sent to a WebSphere MQ queue or retrieved from a WebSphere MQ queue are transferred as untyped data by the MIP procedures. When data profiles are defined in the MIP, the MIP converts message data from Oracle data types to target data types that the receiving application understands. The message data is packed into a buffer of the RAW
data type before being sent to the WebSphere MQ queue. The same conversion process applies when receiving a message. The MIP unpacks the message from the buffer and converts it to specified Oracle data types.
The MIP uses the functions of the UTL_RAW
package to perform the message data conversions. The UTL_RAW
package is a PL/SQL package that contains procedures for converting and packing message data which is sent back and forth through the WebSphere MQ queues using the RAW
data type and PL/SQL data types.
When necessary, you can enhance the message data conversions in the generated MIP with the UTL_RAW
functions. When no data profiles are defined in the MIP, you can create your own data conversion procedures with UTL_RAW
functions, calling these functions before sending a message and immediately after receiving a message.
The UTL_RAW
package is not included with the gateway. It is shipped with each Oracle database. Refer to your Oracle DBA for information about installing the UTL_RAW
package.
The UTL_RAW
functions are called with the following syntax:
UTL_RAW.function(arg1, arg2, ...)
The function name, arguments, their Oracle data types, and the return value data type are provided with each function description in this appendix. For ease of description, the functions are described with PL/SQL syntax that shows the resulting function value placed in a variable as follows:
result := UTL_RAW.function(arg1, arg2, ...);
However, the function can also be used as a component in a PL/SQL expression. For example, the function takes two characters strings, Hello and world!, converts them to raw message data with UTL_RAW
.CAST_TO_RAW
, concatenates them with UTL_RAW
.CONCAT
, and uses the gateway to send them to a WebSphere MQ queue. The same message is retrieved from the queue, converted to a character data type with UTL_RAW.CAST_TO_VARCHAR2
, and then printed.
PGM_UTL.TO_RAW
converts values of the PGM.MQOD
, PGM.MQMD
, PGM.MQPMO
and PGM.MQGMO
object to into raw values.
Syntax
result := PGM_UTL.TO_RAW(input
);
where:
result is a variable that holds the output value of the function. It is of the RAW data type.
input is the input value of the PGM
.MQOD
, PGM
.MQMD
, PGM
.MQPMO
or PGM
.MQGMO
data type objects that is converted to raw data.
UTL_RAW.BIT_AND
performs a bitwise logical AND
operation on two raw values. If the values have different lengths, then the AND
operation is terminated after the last byte of the shorter of the two values. The unprocessed portion of the longer value is appended to the partial result to produce the final result. The length of the resulting value equals the longer of the two input values.
Syntax
result := UTL_RAW.BIT_AND(input1, input2);
where:
result is the variable that holds the output value of the function. It is data type RAW. The value is null if input1
or input2
is null.
input1 is an input value of data type RAW
to BIT_AND
with input2
.
input2 is an input value of data type RAW
to BIT_AND
with input1
.
UTL_RAW
.BIT_COMPLEMENT
performs a bitwise logical COMPLEMENT
operation of a raw value. The length of the resulting value equals the length of the input value.
Syntax
result := UTL_RAW.BIT_COMPLEMENT(input);
where:
result
is the variable that holds the output value of the function. It is of RAW
data type. The value is null if input
is null.
input
is an input value of the RAW
data type on which to perform the COMPLEMENT
operation.
UTL_RAW.BIT_OR
performs a bitwise logical OR
operation of two raw values. If the values have different lengths, then the OR
operation is terminated after the last byte of the shorter of the two values. The unprocessed portion of the longer value is appended to the partial result to produce the final result. The length of the resulting value equals the length of the longer of the two input values.
Syntax
result := UTL_RAW.BIT_OR(input1, input2);
where:
result
is the variable that holds the output value of the function. It is of the data type RAW
. The value is null if input1
or input2
is null.
input1
is an input value of the RAW
data type to BIT_OR
with input2
.
input2
is an input value of the RAW
data type to BIT_OR
with input1
.
UTL_RAW
.BIT_XOR
performs a bitwise logical EXCLUSIVE
OR
operation of two raw values. If the values have different lengths, then the EXCLUSIVE
OR
operation is terminated after the last byte of the shorter of the two values. The unprocessed portion of the longer value is appended to the partial result to produce the final result. The length of the resulting value equals the longer of the two input values.
Syntax
result := UTL_RAW.BIT_XOR(input1, input2);
where:
result
is the variable that holds the output value of the function. It is data type RAW
. The value is null if input1
or input2
is null.
input1
is an input value of the RAW
data type to EXCLUSIVE
OR
with input2
.
input2
is an input value of the RAW
data type to EXCLUSIVE
OR
with input1
.
UTL_RAW
.CAST_TO_RAW
converts a value of data type VARCHAR2
into a raw value with the same number of bytes. The input value is treated as if it were composed of single 8-bit bytes, not characters. Multibyte character boundaries are ignored. The data is not modified in any way, it is only changed to data type RAW
.
Syntax
result := UTL_RAW.CAST_TO_RAW(input);
where:
result
is the variable that holds the output value of the function. It is data type RAW
. The value is null if input
is null.
input
is the input value of the VARCHAR2
data type to convert to raw data.
UTL_RAW
.CAST_TO_VARCHAR2
converts a raw value into a value of data type VARCHAR2
with the same number of data bytes. The result is treated as if it were composed of single 8-bit bytes, not characters. Multibyte character boundaries are ignored. The data is not modified in any way, it is only changed to data type VARCHAR2
.
Syntax
result := UTL_RAW.CAST_TO_VARCHAR2(input);
where:
result
is the variable that holds the output value of the function. It is data type VARCHAR2
. The value is null if input
is null.
input
is the input value of the RAW
data type to convert to data type VARCHAR2
.
UTL_RAW
.COMPARE
compares one raw value to another raw value. If they are identical, then UTL_RAW
.COMPARE
returns 0. If they are not identical, then COMPARE
returns the position of the first byte that does not match. If the input values have different lengths, then the shorter input value is padded on the right by a value that you specify.
Syntax
result := UTL_RAW.COMPARE(input1, input2[, pad]);
where:
result
is the variable that holds the output value of the function. It is of data type NUMBER
. A value of 0 is returned if the values of input1
and input2
are null or identical or the position, numbered from 1, of the first mismatched byte.
input1
is the first input value of the RAW
data type to compare.
input2
is the second input value of the RAW
data type to compare.
padis
a single byte value used to pad the shorter input value. The default is X'00'.
UTL_RAW
.CONCAT
concatenates a set of up to 12 raw values into a single raw value. The values are appended together, left to right, in the order that they appear in the parameter list. Null input values are skipped, and the concatenation continues with the next non-null value.
If the sum of the lengths of the input values exceeds 32 767 bytes, then a VALUE_ERROR
exception is raised.
Syntax
result := UTL_RAW.CONCAT(input1, ... input12);
where:
result
is the variable that holds the output value of the function. It is data type RAW
.
input1
...
input12
are the input values of RAW
data type to concatenate.
UTL_RAW
.CONVERT
converts a raw value to a different character set A
VALUE_ERROR
exception is raised for any of the following conditions:
The input value is null or 0 in length
One or both of the specified character sets is missing, null, or 0 in length
The character set names are invalid or unsupported by the Oracle database
Syntax
result := UTL_RAW.CONVERT(input,new_charset
,old_charset
);
where:
result
is the variable that holds the output value of the function. It is of the RAW
data type.
input
is the input value of the RAW
data type to convert.
new_charset
is the Globalization Support character set to convert input
to.
old_charset
is the Globalization Support character set that input is currently using.
UTL_RAW
.COPIES
returns one or more copies of a value. The values are concatenated together. A
VALUE_ERROR
exception is raised for any of the following conditions:
The input value is null or has a length of 0
A negative number of copies is specified
The length of the result exceeds 32 767 bytes
Syntax
result := UTL_RAW.COPIES(input, number);
where:
result
is the variable that holds the output value of the function. It is of the RAW
data type.
input
is a value of the RAW
data type to copy.
number
is the number of times to copy input. It must be a positive value.
UTL_RAW
.LENGTH
returns the length, in bytes, of a raw value.
Syntax
result := UTL_RAW.LENGTH(input);
where:
result
is the output value of the function. It is of the NUMBER
data type.
input
is the input value of the RAW data type to evaluate.
UTL_RAW
.OVERLAY
replaces a portion of a raw value with a new string of raw data. If the new data is shorter than the length of the overlay area, then the new data is padded to make it long enough. If the new data is longer than the overlay area, then the extra bytes are ignored. If you specify an overlay area that exceeds the length of the input value, then the input value is extended according to the length specified. If you specify a starting position for the overlay area that exceeds the length of the input value, then the input value is padded to the position specified, and then the input value is further extended with the new data.
A
VALUE_ERROR
exception is raised for any of the following conditions:
The new data used to overlay the input value is null or has a length of 0
The portion of the input value to overlay is not defined
The length of the portion to overlay exceeds 32 767 bytes
The number of bytes to overlay is defined as less than 0
The position within the input value to begin the overlay operation is defined as less than 1
Syntax
result := UTL_RAW.OVERLAY(new_bytes
, input, position, length, pad);
where:
result
is the variable that holds the output value of the function. It is of the RAW
data type.
new_bytes
is the new value, a byte string of the RAW
data type, to overlay input with. Bytes are selected from new_bytes
beginning with the leftmost byte.
input
is the input value of data type RAW
to overlay.
position
is the position within input, numbered from 1, at which to begin overlaying. This value must be greater than 0. The default is 1.
length
is the number of bytes to overlay. This must be greater than, or equal to, 0. The default is the length of new_bytes
.
pad
is a single byte value used to pad when length exceeds the overlay length or when position exceeds the length of input
. The default is X'00'.
UTL_RAW
.REVERSE
reverses the byte sequence of a raw value from end-to-end. For example, this function reverses X'0102F3' to X'F30201' or xyz to zyx. The length of the resulting value is the same length as the input value. A
VALUE_ERROR
exception is raised if the input value is null or has a length of 0.
Syntax
result := UTL_RAW.REVERSE(input);
where:
result
is the output value of the function. It is of the RAW
data type.
input
is the input value of the RAW
data type to be reversed.
UTL_RAW
.SUBSTR
removes bytes from a raw value. If you specify a positive number as the starting point for the bytes to remove, then SUBSTR
counts from the beginning of the input value to find the first byte. If you specify a negative number, then UTL_RAW
.SUBSTR
counts backwards from the end of the input value to find the first byte.
A
VALUE_ERROR
exception is raised for any of the following conditions:
The position to begin the removal is specified as 0
The number of bytes to remove is specified as less than 0
Syntax
result := UTL_RAW.SUBSTR(input, position[,length]);
where:
result
is the variable that holds the output value of the function. It is of the RAW
data type. The value is the specified byte or bytes from input
, or the value is a null value if input
is null.
input
is the input value of the RAW
data type from which to extract a portion of its bytes.
position
is the byte position from which to start extraction. This value cannot be 0. If the value of position
is negative, then SUBSTR
counts backwards from the end of input.
length
is the number of bytes to extract from input
after position
. This value must be greater than 0. When not specified, all bytes to the end of input are returned.
UTL_RAW
.TRANSLATE
changes the value of some of the bytes in a raw value according to a scheme that you specify. Bytes in the input value are compared to a matching string, and when found to match, the byte at the same position in the replacement string is copied to the result. It is omitted from the result if the offset exceeds the length of the replacement string. Bytes in the input value that do not appear in the matching string are copied to the resulting value. Only the leftmost occurrence of a byte in the matching string is used, and subsequent duplicate occurrences are ignored.
If the matching string contains more bytes than the replacement string, then the extra bytes at the end of the matching string have no corresponding bytes in the replacement string. Any bytes in the input value that match such bytes are omitted from the resulting value.
A
VALUE_ERROR
exception is raised for any of the following conditions:
The input value is null or has a length of 0
The matching string is null or has a length of 0
The replacement string is null or has a length of 0
Syntax
result := UTL_RAW.TRANSLATE(input, match, replace_bytes);
where:
result
is the variable that holds the output value of the function. It is of data type RAW
.
input
is the input value of data type RAW
to change.
match
specifies the byte 0codes to search for in input
and to change to replace_bytes
. It is of data type RAW
.
replace_bytes
specifies the byte codes that replace the codes specified by match. It is of data type RAW
.
UTL_RAW
.TRANSLITERATE
replaces all occurrences of any bytes in a matching string with the corresponding bytes of a replacement string. Bytes in the input value are compared to the matching string, and if they are not found, then they are copied unaltered to the resulting value. If they are found, then they are replaced in the resulting value by the byte at the same offset in the replacement string, or with the pad byte that you specify when the offset exceeds the length of the replacement string. Only the leftmost occurrence of a byte in the matching string is used. Subsequent duplicate occurrences are ignored. The result value of UTL_RAW
.TRANSLITERATE
is always the same length as the input value.
If the replacement string is shorter than the matching string, then the pad byte is placed in the resulting value when a selected matching string byte has no corresponding byte in the replacement string. A VALUE_ERROR exception is raised when the input value is null or has a length of 0.
UTL_RAW
.TRANSLITERATE
differs from UTL_RAW
.TRANSLATE
in the following ways:
Bytes in the input value that are undefined in the replacement string are padded with a value that you specify
The resulting value is always the same length as the input value
Syntax
result := UTL_RAW.TRANSLITERATE (input, replace_bytes, match, pad);
where:
Result
is the output value of the function. It is data type RAW
.
Input
is the input value of data type RAW
to change.
Replace_bytes
specifies the byte codes to which corresponding bytes of match are changed. This value can be any length that is valid for the RAW data type. The default is a null value and effectively extends with pad to the length of match as necessary.
Match
specifies the byte
codes to match in input
. The value can be any length that is valid for the RAW data type. The default is X'00' through X'FF'.
Pad
is a singe byte value that is used to extend the length of replace_bytes
when replace_bytes
is shorter than match. The default is X'00'.
UTL_RAW
.TRANSLATE
differs from the UTL_RAW
.TRANSLITERATE
function in the following ways:
The raw values used for the matching and replacement strings have no default values
Bytes in the input value that are undefined in the replacement string are omitted in the resulting value
The resulting value can be shorter than the input value
UTL_RAW
.XRANGE
returns a raw value containing all valid one-byte codes within a range that you specify. If the starting byte value is greater than the ending byte value, then the succession of resulting bytes begin with the starting byte, wrapping from X'FF' to X'00', and end at the ending byte.
When specified, the values for t%he starting and ending bytes must be single-byte raw values.
Syntax
result := UTL_RAW.XRANGE(start, end
);
where:
result
is the output value of the function. It is of data type RAW.
start
is a single byte code to start with. The default is X'00'.
end
is a single byte code to end with. The default is X'FF'.
This chapter guides you through the basic concepts and preinstallation steps for Oracle Database Gateway for WebSphere MQ. It contains the following topics:
The preinstallation tasks for the Oracle Database Gateway for WebSphere MQ are divided into the following parts:
Perform the following steps to check for WebSphere MQ software:
Determine where the WebSphere MQ queue manager runs.
Local system
If the WebSphere MQ queue manager runs on a local system, then the queue manager runs on the same system where you intend to install the gateway product set.
Remote system
If the WebSphere MQ queue manager runs on a remote system, then the queue manager runs on a different system, not the system where you intend to install the gateway product set.
Verify that the WebSphere MQ software is already installed. If the WebSphere MQ server software is installed on a different system than the gateway, then the WebSphere MQ client software must be installed on the gateway system.
Identify the name of the WebSphere MQ queue manager.
Identify the WebSphere MQ client channel definition.
If the queue manager is installed on a different system than the gateway, then the WebSphere MQ client software is used to access the remote queue manager. A channel definition is required for this configuration.
Before installing Oracle Database Gateway for WebSphere MQ on UNIX platforms, set the following environment variables:
ORACLE_HOME
ORACLE_SID
DISPLAY
TMP
Note: Verify that the values that you assign to the environment variables, which are listed in this section, are less than 42 characters long. Longer values might generate errors such as "Word too long" during installation. |
ORACLE_HOME
is the root directory in which Oracle software is installed.
Oracle Database Gateway for WebSphere MQ cannot share the same Oracle home directory with other Oracle products. If you have installed other Oracle products, then Oracle Database Gateway for WebSphere MQ must be installed in a different ORACLE_HOME
directory. Refer to Preventing Conflicts Between ORACLE_HOME Directories.
Note: Do not install Oracle Database Gateway for WebSphere MQ in anORACLE_HOME directory containing other Oracle products, including the database. Such an installation could overwrite shared components, causing the products to malfunction. |
To prevent a conflict between the software in an existing ORACLE_HOME
directory and Oracle Database Gateway for WebSphere MQ, you must remove all references to the existing ORACLE_HOME
directory. The following steps describe removing these references.
Unset your existing ORACLE_HOME
variable using one of the following commands.
Edit the following environment variables so that they do not use the existing ORACLE_HOME
value:
Table 4-1 Setting Environment Variables for a New ORACLE_HOME Directory
Environment Variable | Platforms |
---|---|
|
Linux, AIX-based Systems, HP-UX Itanium, and Sun Solaris |
|
Linux, AIX-based Systems, HP-UX Itanium, and Sun Solaris |
|
Linux and Sun Solaris |
|
AIX-based Systems |
|
HP-UX Itanium |
Note: Verify that the C compiler is in yourPATH before you start the installation. |
Set the ORACLE_HOME
environment variable by using one of the following commands:
Set the ORACLE_SID
environment variable by using one of the following commands:
Setting the DISPLAY
environment variable enables you to run Oracle Universal Installer remotely from a local work station. On the system where you run Oracle Universal Installer, set the DISPLAY
environment variable to specify the system name or IP address of your local workstation.
If you get an Xlib error when starting Oracle Universal Installer such as "Failed to connect to server", "Connection refused by server", or "Can't open display", then run the commands on your local workstations as follows:
C shell
prompt> xhost +server_name
Borne or Korn shell
prompt> xhost +server_name
During installation, Oracle Universal Installer uses a temporary directory for swap space. This directory must meet the "Hardware Requirements". The installation might fail if you do not have sufficient space. Oracle Universal Installer checks for the TMP
environment variable to locate the temporary directory. If this environment variable does not exist, then the installer uses the /tmp
directory.
The following example demonstrates how to set the TMP
environment variable.
Oracle Database Gateway for WebSphere MQ uses Oracle Universal Installer to configure environment variables and install components. Oracle Universal Installer guides you through each step of the installation process, so you can choose configuration options for a customized product.
The Oracle Universal Installer includes features that perform the following tasks:
Explore and provide installation options for products
Detect preset environment variables and configuration settings
Set environment variables and configuration during installation
Uninstall products
The Oracle Universal Installer creates the oraInventory
directory the first time it is run on your system. The oraInventory
directory keeps an inventory of the products that Oracle Universal Installer installs on your system as well as other installation information. If you have previously installed Oracle products, then you might already have an oraInventory
directory.
When a UNIX group name is specified, it grants that group the permission to write to the oraInventory
directory. If another group attempts to run Oracle Universal Installer, then they must have permission to write to the oraInventory
directory. If they do not have permission the installation fails.
The user running the Oracle Universal Installer must have permission to write to the oraInventory
directory and all its files. This is required to run the installer.
The location of oraInventory
is defined in /etc/oratab/oraInst.loc
for HP-UX Inanium and AIX-Based Systems and C:\Program Files\Oracle\Inventory\
for Microsoft Windows.
The location of oraInventory
is defined in /var/opt/oraInst.loc
for Sun Solaris.
The latest log file is oraInventory_location/logs/installActions.log
On UNIX based systems and C:\Program Files\Oracle\Inventory\logs\installActions.log
for Microsoft Windows. Log file names of previous installation sessions are in the following format: installActions
datetime
.log
.
Do not delete or manually alter the oraInventory
directory or its contents. Doing this can prevent the Oracle Universal Installer from locating the products that you have installed on your system.
On UNIX based systems, perform the following steps to launch Oracle Universal Installer, which installs Oracle Database Gateway for WebSphere MQ:
Stop all Oracle processes and services (for example, the Oracle database).
Run Oracle Universal Installer.
Log in as the Oracle Database Gateway for WebSphere MQ user.
Start Oracle Universal Installer by entering:
prompt> mount_point/runInstaller
On Microsoft Windows, perform the following steps to launch Oracle Universal Installer, which installs Oracle Database Gateway for WebSphere MQ:
Start your system and select MS Windows from the operating system Loader option. Log in to your MS Windows system as a member of the Administrators group.
If you are installing the gateway for the first time, ensure there is sufficient space on the disk where you are installing the gateway as specified in "Hardware Requirements".
Before installing the software, stop all Oracle NT Services that are running:
From the Start menu, go to Setting, then Control Panel, and then click Services. A list of all NT services is displayed.
Select an Oracle NT service (these services begin with Oracle).
Click Stop.
Continue to select and stop all Oracle NT services until all active Oracle NT Services are stopped.
Load the installation media and start the Oracle Universal Installer.
This launches Oracle Universal Installer using which you can install Oracle Database Gateway for WebSphere MQ.
Installation and User's Guide
11g Release 2 (11.2)
E12417-01
July 2009
Oracle Database Gateway for WebSphere MQ Installation and User's Guide, 11g Release 2 (11.2)
E12417-01
Copyright © 2005, 2009, Oracle and/or its affiliates. All rights reserved.
Primary Author: Maitreyee Chaliha
Contributing Author: Li-Te Chen
This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.
The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.
If this software or related documentation is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:
U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use, duplication, disclosure, modification, and adaptation shall be subject to the restrictions and license terms set forth in the applicable Government contract, and, to the extent applicable by the terms of the Government contract, the additional rights set forth in FAR 52.227-19, Commercial Computer Software License (December 2007). Oracle USA, Inc., 500 Oracle Parkway, Redwood City, CA 94065.
This software is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications which may create a risk of personal injury. If you use this software in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure the safe use of this software. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software in dangerous applications.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
This software and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.
Use the Visual Workbench when developing applications that access WebSphere MQ through the gateway. The Visual Workbench defines an interface for accessing WebSphere MQ and automatically generates the PL/SQL code (the MIP) for Oracle applications to interface with the gateway. Refer to the Oracle Procedural Gateway Visual Workbench for WebSphere MQ Installation and User's Guide for Microsoft Windows (32-Bit) for more information about Visual Workbench.
The MIP uses definitions from the PGM
, PGM_UTL8
, and PGM_SUP
packages. When necessary, you can alter the MIP to include WebSphere MQ functions that are not supported by Visual Workbench. This is done with the definitions and procedures from the PGM
, and PGM_UTL8
, and PGM_SUP
packages.
The PGM
, PGM_UTL8
, and PGM_SUP
packages are installed when the Visual Workbench repository or the DG4MQ deployment environment is created. For more information, refer to "Installing the Oracle Visual Workbench Repository" and "Preparing the Production Oracle Database".
This appendix discusses the PGM
, PGM_UTL8
, and PGM_SUP
packages in the following sections:
The gateway procedures and type definitions of the PGM
package are modeled after the WebSphere MQ MQI calls. For all the relevant calls and structures found in MQI, a corresponding counterpart exists in PGM
and the associated data type definitions exist in pgmobj.sql
. The gateway procedures and PGM
type definitions are named the same as their MQI counterparts. However, the data types of arguments or structure fields are changed into corresponding PL/SQL data types.
Using these procedures and type definitions in an Oracle application is very similar to writing a WebSphere MQ application. The fields of all PGM
type definitions are initialized. These initialization values are based on default values defined by MQI.
The use of gateway procedures and PGM
type definitions requires extensive knowledge of MQI and WebSphere MQ programming in general. These procedures and records follow the MQI flowchart, semantics, and syntax rules.
The PGM
package is installed when the Visual Workbench repository or the DG4MQ deployment environment is created and is granted public access. It has no schema because the gateway omits all schema names when describing or running a procedure. No schema qualifiers need to be prefixed to the names of the procedures and type definitions.
See Also: Refer to IBM MQSeries Application Programming Reference for complete information about writing WebSphere MQ applications and using MQI calls. |
The gateway procedures and PGM provide the following procedures and type definitions:
Table A-1 Procedures and Type Definitions
Procedure | Procedure Purpose | Type Definitions Used by the Procedure |
---|---|---|
|
Opens a queue. |
|
|
Sends a message to the queue that was opened by |
|
Sends a message longer than 32767 bytes to the queue |
| |
|
Retrieves or scans a message from the queue that was opened by |
|
Sends a message longer than 32767 bytes to the queue |
| |
|
Closes the queue that was opened by |
Does not use a type definition. |
The gateway procedures are described in alphabetic order in this appendix. The type definitions are described with the procedures that use them. Only type definition fields that can be changed are described. Other fields equivalent to MQI fields are left out because they are reserved for WebSphere MQ, are not supported by the gateway, or contain values that should not be changed.
A procedure's definition is shown using the IBM argument names associated with the equivalent MQI call. For example:
MQGET(hobj, mqmd, mqgmo, msg)
The syntax of the MQGET
call is as follows:
MQGET(handle, descript, get_options, message);
where:
handle
is your name for the first argument specified in the definition as hobj
.
descript
is your name for the second argument specified in the definition as mqmd
.
get_options
is your name for the third argument specified in the definition as mqgmo
.
message
is your name for the fourth argument specified in the definition as msg
.
You can use your own names for these arguments if you code the arguments in the order shown in the definition.
For more information about PL/SQL, refer to the Oracle Database PL/SQL Language Reference.
The following MQI calls have no equivalent procedures in the gateway because the Oracle database and the gateway automatically perform the functions of these MQI calls:
Transaction control is handled by the Oracle transaction coordinator. The Oracle application does not need to invoke a separate MQBACK
call to undo the changes sent to WebSphere MQ.
A connection to a queue manager is established by the Oracle database and the gateway whenever an Oracle application refers to a gateway procedure. The database link name that is used when calling the gateway procedure determines which queue manager the gateway connects to.
Transaction control is handled by the Oracle transaction coordinator. An Oracle application does not need to invoke a separate MQCMIT
call to commit the changes sent to WebSphere MQ.
Connections to a queue manager are closed by the Oracle database and gateway. An Oracle application does not need to close the connection with the queue manager. Ending the current Oracle session or dropping the database link causes the queue manager connection to end.
This section provides information about how to upgrade Oracle9i DG4MQ and existing customized PL/SQL application programs to use Oracle Database Gateway for WebSphere MQ features. DG4MQ data types and RPC API prototypes are changed to meet the requirements of the gateway infrastructure.
When upgrading DG4MQ to Oracle 10g release 2 or higher, Oracle recommends that you install the newer version of DG4MQ on a separate development Oracle system. After you have finished with system configuration and testing, transfer all of the COBOL copy books and regenerate and recompile MIPs using the Oracle Visual Workbench. For customized codes, make necessary changes and recompile.
Migrating DG4MQ Releases 8 and 9 PL/SQL Applications
To migrate DG4MQ releases 8 and 9 PL/SQL applications:
In the PL/SQL declarative section, remove dblink
references from the following DG4MQ data types:
PGM8
.MQOD
PGM8
.MQMD
PGM8
.MQPMO
PGM8
.MQGMO
Then remove the following PGM8
.MQ
*RAW
data types:
PGM8
.MQODRAW
PGM8
.MQMDRAW
PGM8
.MQPMORAW
PGM8
.MQGMORAW
In the PL/SQL declarative section, change the data type of the handle of the queue, the third argument of PGM
.MQOPEN
, from BINARY_INTEGER
to PGM
.MQOH
and replace the package name PGM8
with PGM
.
Change the data type of the handles of the queue, the third argument of PGM
.MQOPEN,
from BINARY_INTEGER
to PGM
.MQOH
.
For example, for version 8 and 9 change the following data types to those listed for Oracle 10g:
objdesc PGM8.MQOD; msgdesc PGM8.MQMD; putmsgopts PGM8.MQPMO; getmsgopts PGM8.MQGMO; hobj BINARY_INTEGER; mqodRaw PGM8.MQODRAW; mqmdRaw PGM8.MQMDRAW; mqpmoRaw PGM8.MQPMORAW; mqgmoRaw PGM8.MQGMORAW;
The data types for Oracle 10g release 2 and higher:
objdesc PGM.MQOD; msgdesc PGM.MQMD; putmsgopts PGM.MQPMO; getmsgopts PGM.MQGMO; hobj PGM.MQOH;
In the PL/SQL executable section, remove dblink
references from the following DG4MQ procedures:
PGM8.MQOPEN@dblink() PGM8.MQPUT@dblink() PGM8.MQGET@dblink() PGM8.MQCLOSE@dblink()
Then define the dblink
in the new PGM.MQOD
type where the object queue name is defined.
For example, for version 8 and 9:
objdesc.objectname := 'QUEUE1';
For Oracle 10g release 2 and higher:
objdesc.objectname := 'QUEUE1'; objdesc.dblinkname := 'dblink';
If necessary, change the package name PGM8
of all DG4MQ procedures to PGM
.
For example, for version 8 and 9:
PGM8.MQOPEN@dblink(); PGM8.MQPUT@dblink(); PGM8.MQGET@dblink(); PGM8.MQCLOSE@dblink();
For Oracle 10g release 2 and higher:
PGM.MQOPEN; PGM.MQPUT; PGM.MQGET; PGM.MQCLOSE;
In the PL/SQL executable section, remove all statements starting with PGM_UTL8
.RAW_TO_
*, remove all PGM_UTL8
.TO_RAW
statements, and replace all references to the MQ
*RAW
data types with their matching MQ
* data types in the following DG4MQ procedures:
PGM
.MQOPEN
;
PGM
.MQPU
;
PGM
.MQGET
;
PGM
.MQCLOSE
;
For example, for versions 8 and 9:
mqodRaw := PGM_UTL8.TO_RAW(objdesc); PGM8.MQOPEN@dblink(mqodRaw, options, hobj); objdesc := PGM_UTL8.RAW_TO_MQMD(mqodRaw); mqmdRaw := PGM_UTL8.TO_RAW(msgdesc); mqpmoRaw := PGM_UTL8.TO_RAW(putmsgopts); PGM8.MQPUT@dblink(hobj, mqmdRaw, mqpmoRaw, putbuffer); putmsgopts := PGM_UTL8.RAW_TO_MQPMO(mqpmoRaw); msgdesc := PGM_UTL8.RAW_TO_MQMD(mqmdRaw); mqmdRaw := PGM_UTL8.TO_RAW(msgdesc); mqgmoRaw := PGM_UTL8.TO_RAW(getmsgopts); PGM8.MQGET@dblink(hobj, mqmdRaw, mqgmoRaw, putbuffer); getmsgopts := PGM_UTL8.RAW_TO_MQGMO(mqgmoRaw); msgdesc := PGM_UTL8.RAW_TO_MQMD(mqmdRaw);
For Oracle 10g release 2 and higher:
PGM.MQOPEN(objdesc, options, hobj); PGM.MQPUT(hobj, msgdesc, putmsgopts, putbuffer); PGM.MQGET(hobj, msgdesc, getmsgopts, getbuffer);
In PL/SQL executable section, remove all statements that reference the old MQ
*RAW
data types.
Migrating DG4MQ Release 4.0.1.*.* PL/SQL Applications
To migrate applications:
In the PL/SQL declarative section, remove dblink
references from the following DG4MQ data types:
PGM
.MQOD
PGM
.MQMD
PGM
.MQPMO
PGM
.MQGMO
In the PL/SQL executable section, remove dblink
references from the following DG4MQ procedures and define the dblink
in the new PGM
.MQOD
object where the object queue name is defined:
PGM
.MQOPEN
@
dblink
()
PGM
.MQPUT
@
dblink
()
PGM
.MQGET
@
dblink
()
PGM
.MQCLOSE
@
dblink
()
For example, for version 4:
PGM.MQOPEN@dblink(objdesc, options, hobj); objdesc.objectname :='QUEUE1'; PGM.MQPUT@dblink(hobj, msgdesc, putmsgopts, putbuffer); PGM.MQGET@dblink(hobj, msgdesc, getmsgopts, putbuffer); PGM.MQCLOSE@dblink(hobj, options);
MQCLOSE
closes a queue. On return, the queue handle is invalid and your application must reopen the queue with another call to MQOPEN
before issuing another MQPUT
, MQGET
, or MQCLOSE
call to the queue.
MQCLOSE
differs from MQI calls in the following ways:
The connection handle argument is omitted from MQCLOSE
because the gateway automatically takes care of managing queue manager connections.
The MQI completion code is not included in the procedure argument list. When a gateway procedure fails because the corresponding MQI call failed, then an Oracle error message is returned to the caller.
The MQI reason code is not included in the procedure argument list. When the corresponding MQI call for a gateway procedure returns a reason code, then the reason code is included in the Oracle error message returned to the caller.
Definition
MQCLOSE(hobj, options
)
where:
hobj
contains the handle for the queue to close. The handle is returned by a previous call to MQOPEN
. This input argument is a new PGM
.MQOH
object in Oracle 10g release 2.
options
specifies the close action. Use PGM_SUP
.MQCO_NONE
or the other PGM_SUP
constants for a close option. Refer to MQCLOSE Values. This input argument is of the BINARY_INTEGER
PL/SQL data type.
You can use your own variable names when arguments are in the required order as follows:
MQCLOSE(handle, close_options);
MQGET
retrieves a message from a queue. The queue must already be open from a previous call to MQOPEN
with the PGM_SUP
.MQOO_INPUT_AS_Q_DEF
(or an equivalent option) option set. Retrieved messages for this form of MQGET
must be shorter than 32767 bytes.
MQGET
differs from MQI calls in the following ways:
The connection handle argument is omitted from MQGET
because the gateway automatically takes care of managing queue manager connections.
The MQI completion code is not included in the procedure's argument list. When a gateway procedure fails because the corresponding MQI call failed, then an Oracle error message is returned to the caller.
The MQI reason code is not included in the procedure's argument list. When the corresponding MQI call for a gateway procedure returns a reason code, then the reason code is included in the Oracle error message that was returned to the caller.
The msg
length argument is not included in the procedure's argument list because the Oracle database and the gateway automatically keep track of the message data length.
Definition
MQGET(hobj,mqmd
,mqgmo
,msg
)
where:
hobj
contains the handle for the queue to open. The handle is returned by a previous call to MQOPEN
. This input argument is a new PGM
.MQOH
object in Oracle 10g release 2.
mqmd
is used on input to describe the attributes of the message being retrieved. Use the fields of the PGM
.MQMD
object type definition to describe these attributes.
On output, mqmd
contains information about how the request was processed. The queue manager sets some of the PGM
.MQMD
object fields on return.
This input and output argument is PL/SQL PGM
.MQMD
data type. For the details of PGM
.MQMD
, refer to PGM.MQMD Type Definition.
mqgmo
is used on input to describe the option values that control the retrieve request. Use the fields of the PGM
.MQGMO
object type definition to describe these options.
On output, the queue manager sets some of the PGM
.MQGMO
object fields on return.
This input and output argument is PL/SQL PGM
.MQGMO
data type. For the details of the PGM
.MQGMO
object, refer to PGM.MQGMO Type Definition.
msg
contains the retrieved message. This output argument is PL/SQL data type RAW
or PGM
.MQGET_BUFFER
.
Examples
Using your own variable names when arguments are in the required order:
MQGET(handle, descript, opts, message);
The following example, which is provided as a sample with the gateway (ORACLE_HOME\dg4mq\getsample.sql on Microsoft Windows and
ORACLE_HOME
/dg4mq/sample/getsample.sql
on UNIX based systems), reads all messages from a WebSphere MQ queue. For more information, refer to the IBM publication on WebSphere MQ Application Programming.
Example A-1 getsample.sql
---- Copyright Oracle, 2007 All Rights Reserved. -- -- NAME -- getsample.sql -- -- DESCRIPTION -- -- Specify the database link name you created for the gateway. To do this, -- replace the database link name 'YOUR_DBLINK_NAME' with the dblink name -- you chose when the database link was created. -- -- This script performs a test run for the MQSeries gateway. In this -- script the queuename is 'YOUR_QUEUE_NAME', replace it with a valid -- queue name at the queue manager the gateway is configured for. -- -- NOTES -- Run the script from the SQL*Plus command line. -- -- Make the sure the user is granted 'EXECUTE' on package dbms_output -- SET SERVEROUTPUT ON DECLARE objdesc PGM.MQOD; msgDesc PGM.MQMD; getOptions PGM.MQGMO; objectHandle PGM.MQOH; message raw(32767); BEGIN objdesc.OBJECTNAME := 'QUEUE1'; objdesc.DBLINKNAME := 'dg4mqdepdblink'; -- Open the queue 'YOUR_QUEUE_NAME' for reading. PGM.MQOPEN(objdesc, PGM_SUP.MQOO_INPUT_AS_Q_DEF, objectHandle); -- Get all messages from the queue. WHILE TRUE LOOP -- Reset msgid and correlid to get the next message. msgDesc.MSGID := PGM_SUP.MQMI_NONE; msgDesc.CORRELID := PGM_SUP.MQCI_NONE; PGM.MQGET(objectHandle, msgDesc, getOptions, message); -- Process the message.... DBMS_OUTPUT.PUT_LINE('message read back = ' || rawtohex(message)); END LOOP; EXCEPTION WHEN PGM_SUP.NO_MORE_MESSAGES THEN DBMS_OUTPUT.PUT_LINE('Warning: No more message found on the queue'); -- Close the queue again. PGM.MQCLOSE(objectHandle, PGM_SUP.MQCO_NONE); WHEN OTHERS THEN -- Re-raise the error; DBMS_OUTPUT.PUT_LINE('Error: Oracle Database Gateway for WebSphere MQ verification script failed.'); DBMS_OUTPUT.PUT_LINE(SQLERRM); raise; END; /
Notes:
Note: The PL/SQL block fails if the exception clause is left out. In that case, thePGM_SUP.NO_MORE_MESSAGES error code is raised. The MSGID and CORRELID fields that are used for MQGET are set after each call to MQGET . If they are not reset at each cycle, then MQGET checks for the next message that has the same identifiers as the last read operation, which usually do not exist. The PL/SQL block would only read one message. |
PGM
.MQMD
specifies the control information that accompanies a message when it travels between the sending and receiving applications. It also contains information about how the message is handled by the queue manager or by the receiving application. PGM
.MQMD
describes the attributes of the message being retrieved.
You can use the default values for PGM
.MQMD
fields or change the fields for your application requirements. For example, to change a field value, do the following:
mqmd.field_name := field_value;
where:
mqmd
is the PGM
.MQMD
object data type and it describes the attributes of the message being retrieved
field_name
is a field name of the PGM
.MQMD
object type definition. You can set as many fields as necessary. Refer to Table A-2 for field names and descriptions.
field_value
is the value to assign to field_name
. You can specify a value or use a PGM_SUP
constant to assign a value.
Table A-2 PGM.MQMD Object Fields
Field Name | Description | PL/SQL Data Type | Initial Value |
---|---|---|---|
|
Allows the application that sends a message to specify which report message (or messages) should be created by the queue manager when an expected or unexpected event occurs. Use a |