In my quest to find a way for communicating Oracle DB to IBM MQ, I found my answer using Oracle Messaging Gateway (OMG) using this provided article from IBM developer works.
It seems working, and actually good idea, but main issue was the cost versus the functionality required. My main need is either to send a text message to queue (Asynchronous / Synchronous) i.e. (Send and forget / Send and Receive). Price by the time of this article was around (xx,000 USD per CPU) !!!!
So, I have decided to look into how to build custom code to solve this issue, and be platform independent as I have situation where Oracle is running on AIX, as well as Windows. As, I have developed several Java stored procedure before I thought it will be a good idea and will do the trick, and I was right :)
I have tried this using Oracle 11gr2 on Windows 2008r2 having IBM MQ 7.0.0.2 installed on same server and another AIX 6.1 server as well. I think the steps can be applied on different versions as long you understand the below concepts.
I- Concepts:
1- You need to know what version of Oracle JVM, you are running, this can simply done by issuing the following command:
This is important as Oracle JVM is just SE (Standard Edition) while we need to import Java Enterprise Edition into Oracle to be able to connect to MQ, so now you know which version of Enterprise should be imported.
2- Oracle JVM doesn't support JNI, so connectivity to IBM MQ can never be in Binding mode and should be in client mode, as binding mode use native OS call so Java classes will need to load dll on windows which is not allowed.
3-If u r a Java developer u will always think of modifying CLASSPATH to include external Java libraries of MQ, but actually Oracle JVM is not working with same concept, and all libraries should be loaded into Oracle DB schema. For more details; I think better read this Oracle page from documentation.
4- Oracle supports loading jar files but not nested jar files (jar files containing other jar files).
II- Required Jar files:
My trial was based on asktom old posting it didn't provide complete solution but the idea which is the most important part in finding the solution, which is trying to load jar by jar and looking into errors to identify dependency on other jar files till I get my main jar (com.ibm.mq.jar) file imported to DB without errors.
I have done this practice and found below list of required jar files:
III- User creation & Permissions:
1- Create user to have all related jars (Or use your own user but make sure to have required privileges) & java classes loaded into “ORAMQ”:
2- Grant roles to user:
3- Grant Java permissions to user:
<QMANAGER_LISTENER>It should be replaced by Queue manager listener.
It seems working, and actually good idea, but main issue was the cost versus the functionality required. My main need is either to send a text message to queue (Asynchronous / Synchronous) i.e. (Send and forget / Send and Receive). Price by the time of this article was around (xx,000 USD per CPU) !!!!
So, I have decided to look into how to build custom code to solve this issue, and be platform independent as I have situation where Oracle is running on AIX, as well as Windows. As, I have developed several Java stored procedure before I thought it will be a good idea and will do the trick, and I was right :)
I have tried this using Oracle 11gr2 on Windows 2008r2 having IBM MQ 7.0.0.2 installed on same server and another AIX 6.1 server as well. I think the steps can be applied on different versions as long you understand the below concepts.
I- Concepts:
1- You need to know what version of Oracle JVM, you are running, this can simply done by issuing the following command:
%ORACLE_HOME%\dbhome_1\jdk\bin>java -version
For Oracle 11g, the following result should be shown:
java version "1.5.0_17"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_17-b04)
Java HotSpot(TM) 64-Bit Server VM (build 1.5.0_17-b04, mixed mode)
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_17-b04)
Java HotSpot(TM) 64-Bit Server VM (build 1.5.0_17-b04, mixed mode)
This is important as Oracle JVM is just SE (Standard Edition) while we need to import Java Enterprise Edition into Oracle to be able to connect to MQ, so now you know which version of Enterprise should be imported.
2- Oracle JVM doesn't support JNI, so connectivity to IBM MQ can never be in Binding mode and should be in client mode, as binding mode use native OS call so Java classes will need to load dll on windows which is not allowed.
3-If u r a Java developer u will always think of modifying CLASSPATH to include external Java libraries of MQ, but actually Oracle JVM is not working with same concept, and all libraries should be loaded into Oracle DB schema. For more details; I think better read this Oracle page from documentation.
4- Oracle supports loading jar files but not nested jar files (jar files containing other jar files).
II- Required Jar files:
My trial was based on asktom old posting it didn't provide complete solution but the idea which is the most important part in finding the solution, which is trying to load jar by jar and looking into errors to identify dependency on other jar files till I get my main jar (com.ibm.mq.jar) file imported to DB without errors.
I have done this practice and found below list of required jar files:
| Jar name | Description and Location |
| javaee-api-5.0.0.jar | Java Enterprise edition This version should match Oracle JVM version. Available here |
| com.ibm.msg.client.commonservices.jar | IBM message client common services This is inside content of another jar, but Oracle loadjava utility can’t load nested jar files, so this file needs extraction. %IBM_MQ%\java\lib\OSGi\com.ibm.msg.client.osgi.jms_7.0.1.3.jar\com.ibm.msg.client.commonservices.jar |
| com.ibm.mq.commonservices.jar | IBM MQ common services %IBM_MQ%\java\lib\com.ibm.mq.commonservices.jar |
| com.ibm.mq.pcf.jar | IBM MQ PCF %IBM_MQ%\java\lib\com.ibm.mq.pcf.jar |
| com.ibm.mq.headers.jar | IBM MQ headers %IBM_MQ%\java\lib\com.ibm.mq.headers.jar |
| com.ibm.mq.jmqi.jar | IBM MQ jmqi %IBM_MQ%\java\lib\com.ibm.mq.jmqi.jar |
| com.ibm.mq.jar | IBM MQ %IBM_MQ%\java\lib\com.ibm.mq.jar |
| commons-codec-1.3.jar | Apache commons codec Used for converting RAW to HEX and vice versa for printing MQ message id, we are using very small functionality from the jar so upgrade is not required unless actually needed. Download from apache common codec |
III- User creation & Permissions:
1- Create user to have all related jars (Or use your own user but make sure to have required privileges) & java classes loaded into “ORAMQ”:
create user ORAMQ identified by <password>;
2- Grant roles to user:
grant connect to ORAMQ;
grant resource to ORAMQ;
grant debug connect session to ORAMQ;
grant resource to ORAMQ;
grant debug connect session to ORAMQ;
3- Grant Java permissions to user:
Call dbms_java.grant_permission( 'ORAMQ', 'SYS:java.lang.RuntimePermission', 'getClassLoader', '' );
Call dbms_java.grant_permission( 'ORAMQ', 'SYS:java.net.SocketPermission', '<QMANAGER_HOST>:<QMANAGER_LISTENER>', 'connect,resolve' );
Commit;
<QMANAGER_HOST>It should be replaced by Queue manager machine name or IP.Call dbms_java.grant_permission( 'ORAMQ', 'SYS:java.net.SocketPermission', '<QMANAGER_HOST>:<QMANAGER_LISTENER>', 'connect,resolve' );
Commit;
<QMANAGER_LISTENER>It should be replaced by Queue manager listener.
IV- Loading external jars:
1- Loading Java Enterprise to Oracle:
%ORACLE_HOME%\dbhome_1\BIN\loadjava -user ORAMQ/ORAMQ -verbose -fileout %INSTALL_DIR%\loadjava-1-javaee-api-5.0.0.jar.log %INSTALL_DIR%\javaee-api-5.0.0.jar -resolve
exiting : Failures occurred during processing
2- Loading IBM message client common services:
%ORACLE_HOME%\dbhome_1\BIN\loadjava -user ORAMQ/ORAMQ -verbose -fileout %INSTALL_DIR%\loadjava-2-com.ibm.msg.client.commonservices.jar.log %INSTALL_DIR%\com.ibm.msg.client.commonservices.jar -resolve
Classes Loaded: 87
Resources Loaded: 16
Sources Loaded: 0
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 0
Errors: 0
Resources Loaded: 16
Sources Loaded: 0
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 0
Errors: 0
3- Loading IBM MQ common services:
%ORACLE_HOME%\dbhome_1\BIN\loadjava -user ORAMQ/ORAMQ -verbose -fileout %INSTALL_DIR%\loadjava-3-com.ibm.mq.commonservices.jar.log %INSTALL_DIR%\com.ibm.mq.commonservices.jar –resolve
Classes Loaded: 76
Resources Loaded: 15
Sources Loaded: 0
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 76
Errors: 0
Resources Loaded: 15
Sources Loaded: 0
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 76
Errors: 0
4- Loading IBM MQ PCF:
%ORACLE_HOME%\dbhome_1\BIN\loadjava -user ORAMQ/ORAMQ -verbose -fileout %INSTALL_DIR%\loadjava-4-com.ibm.mq.pcf.jar.log %INSTALL_DIR%\com.ibm.mq.pcf.jar -resolve
exiting : Failures occurred during processing
5- Loading IBM MQ common services:
%ORACLE_HOME%\dbhome_1\BIN\loadjava -user ORAMQ/ORAMQ -verbose -fileout %INSTALL_DIR%\loadjava-5-com.ibm.mq.headers.jar.log %INSTALL_DIR%\com.ibm.mq.headers.jar -resolve
exiting : Failures occurred during processing
6- Loading IBM MQ JMQI:
%ORACLE_HOME%\dbhome_1\BIN\loadjava -user ORAMQ/ORAMQ -verbose -fileout %INSTALL_DIR%\loadjava-6-com.ibm.mq.jmqi.jar.log %INSTALL_DIR%\com.ibm.mq.jmqi.jar -resolve
exiting : Failures occurred during processing
7- Loading IBM MQ:
%ORACLE_HOME%\dbhome_1\BIN\loadjava -user ORAMQ/ORAMQ -verbose -fileout %INSTALL_DIR%\loadjava-7-com.ibm.mq.jar.log %INSTALL_DIR%\com.ibm.mq.jar -resolve
Classes Loaded: 117
Resources Loaded: 15
Sources Loaded: 0
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 117
Errors: 0
Resources Loaded: 15
Sources Loaded: 0
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 117
Errors: 0
The above is important validation, as this is the jar that is been called from Java utility, it should compile without any errors.
8- Loading Apache commons codec:
%ORACLE_HOME%\dbhome_1\BIN\loadjava -user ORAMQ/ORAMQ -verbose -fileout %INSTALL_DIR%\loadjava-8-commons-codec-1.3.jar.log %INSTALL_DIR%\commons-codec-1.3.jar -resolve
Classes Loaded: 25
Resources Loaded: 8
Sources Loaded: 0
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 0
Errors: 0
Resources Loaded: 8
Sources Loaded: 0
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 0
Errors: 0
V- Loading Java Stored Procedure:
1- MQReasonCodeResolver.java: This class is mainly adding helpful functionality in MQException to return representative string better than error code:
%ORACLE_HOME%\dbhome_1\BIN\loadjava -user ORAMQ/ORAMQ -verbose -fileout %INSTALL_DIR%\loadjava-9-MQReasonCodeResolver.java.log %INSTALL_DIR%\MQReasonCodeResolver.java –resolve
Classes Loaded: 0
Resources Loaded: 0
Sources Loaded: 1
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 0
Errors: 0
Resources Loaded: 0
Sources Loaded: 1
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 0
Errors: 0
2- MQClient.java: This class is the main class responsible for MQ connectivity:
%ORACLE_HOME%\dbhome_1\BIN\loadjava -user ORAMQ/ORAMQ -verbose -fileout %INSTALL_DIR%\loadjava-9-MQClient.java.log %INSTALL_DIR%\MQClient.java –resolve
Classes Loaded: 0
Resources Loaded: 0
Sources Loaded: 1
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 0
Errors: 0
Resources Loaded: 0
Sources Loaded: 1
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 0
Errors: 0
VI- Publish as PL/SQL Stored Procedure:
PROCEDURE MQ_SEND_AND_RECEIVE(P_HOST IN VARCHAR2,
P_LISTENER IN NUMBER,
P_QMCHANNEL IN VARCHAR2,
P_CCSID IN NUMBER,
P_QMANAGER IN VARCHAR2,
P_QUEUE IN VARCHAR2,
P_MESSAGE IN VARCHAR2,
P_REPLYTOQUEUE IN VARCHAR2,
P_WAITMILLISECOND IN NUMBER,
P_MSGID OUT VARCHAR2,
P_RESULT OUT VARCHAR2,
P_MESSAGE_REPLY OUT VARCHAR2) AS
LANGUAGE JAVA NAME 'MQClient.sendAndReceive(java.lang.String, int, java.lang.String, int, java.lang.String, java.lang.String, java.lang.String, java.lang.String, int, java.lang.String[], java.lang.String[], java.lang.String[])';
PROCEDURE MQ_SEND_AND_FORGET(P_HOST IN VARCHAR2,
P_LISTENER IN NUMBER,
P_QMCHANNEL IN VARCHAR2,
P_CCSID IN NUMBER,
P_QMANAGER IN VARCHAR2,
P_QUEUE IN VARCHAR2,
P_MESSAGE IN VARCHAR2,
P_REPLY_QUEUE IN VARCHAR2,
P_MSGID OUT VARCHAR2,
P_RESULT OUT VARCHAR2) AS
LANGUAGE JAVA NAME 'MQClient.sendAndForget(java.lang.String, int, java.lang.String, int, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String[], java.lang.String[])';
P_LISTENER IN NUMBER,
P_QMCHANNEL IN VARCHAR2,
P_CCSID IN NUMBER,
P_QMANAGER IN VARCHAR2,
P_QUEUE IN VARCHAR2,
P_MESSAGE IN VARCHAR2,
P_REPLYTOQUEUE IN VARCHAR2,
P_WAITMILLISECOND IN NUMBER,
P_MSGID OUT VARCHAR2,
P_RESULT OUT VARCHAR2,
P_MESSAGE_REPLY OUT VARCHAR2) AS
LANGUAGE JAVA NAME 'MQClient.sendAndReceive(java.lang.String, int, java.lang.String, int, java.lang.String, java.lang.String, java.lang.String, java.lang.String, int, java.lang.String[], java.lang.String[], java.lang.String[])';
PROCEDURE MQ_SEND_AND_FORGET(P_HOST IN VARCHAR2,
P_LISTENER IN NUMBER,
P_QMCHANNEL IN VARCHAR2,
P_CCSID IN NUMBER,
P_QMANAGER IN VARCHAR2,
P_QUEUE IN VARCHAR2,
P_MESSAGE IN VARCHAR2,
P_REPLY_QUEUE IN VARCHAR2,
P_MSGID OUT VARCHAR2,
P_RESULT OUT VARCHAR2) AS
LANGUAGE JAVA NAME 'MQClient.sendAndForget(java.lang.String, int, java.lang.String, int, java.lang.String, java.lang.String, java.lang.String, java.lang.String, java.lang.String[], java.lang.String[])';
Now u can use above PL/SQL procedures for communicating with IBM MQ from Oracle DB :)
Finally, I know this was a very long posting but I hope it will help someone out there.
Update 14-Nov-2017:
For resolving any issues, you will need to debug the Java code to identify the issue, I have provided another version MQClientDebug.java for logging with samples for logging in Win & Linux, you will just need to download and rename the file to MQClient.java, do whatever edit you want to log & trace and load it to Oracle as per step V.2.
You will need to tell Oracle to give write permission to Java for this purpose using the following command:
call DBMS_JAVA.grant_permission('ORAMQ', 'java.io.FilePermission', '<<ALL FILES>>', 'read ,write, execute, delete');
Commit;
Note this is for debugging only (Development environment, you should not deploy such version to production or grant Java this permission)