Techie Architect: Oracle DB to IBM MQ connector

Oracle DB to IBM MQ connector

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:

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

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;

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.
<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

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

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

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

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

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

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

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)

40 comments:

AldoOctober 29, 2011 12:23 AM
Mr. Fouad, thank you so much for this information!!!
Im from Panama City, and we were already giving up on the "connect oracle and MQ by stored java procedures" idea because we were not able to upload the java classes in a correct way.

But I kept doing some research, and when i was about to give up too and try a Webservice... I found your blog.

Im really grateful, you have no idea how much youve helped me with this tutorial... You saved my life (and helped me get a bonus too hahahaha)

Greeting from Panama City.

PS: Sorry for my english, its not my first language.

Aldo
ReplyDelete
Replies
Ashraf FouadOctober 29, 2011 9:51 AM
Anytime :) glad to hear this article helped someone somewhere
ReplyDelete
Replies
Ashraf FouadDecember 12, 2011 2:55 PM
We have just went production last Friday (2011-Dec-09) on Oracle 11gr2, and AIX 7.1 without any issues.

I'm just recording this to make sure this solution is platform independent.
ReplyDelete
Replies
AnonymousJuly 22, 2012 4:37 PM
Hello,

I am trying to get this working on an Oracle 11g - 11.2 - instance on linux.

I have tested the method I am trying to call - JmsProducer - on a command prompt with a standalone JRE and it works.

When trying to load the jars in Oracle, I also received some errors when loading, but I still continued. When executing the procedure, I get a NullPointerException, when I try to execute. This happens on the line whenre I am trying to get JmsFactoryFactory instance.

Now, I am also running into another problem. I was unable to use dropjava to drop the jars that I had added as -jarsasdbobjects. So I asked the DBA to recreate the schema.

After he did that, now I am not able to load the javaee jar - that I was previously able to. It loads part of it and I start getting connection closed error. Could the DBA have missed some set-up when he re-created the schema?

Thank you
ReplyDelete
Replies
AnonymousJuly 24, 2012 2:12 AM
Thank you so much for the detailed explanation. I will have to wait until I am able to load the javaee-api-5.0.0.jar. The loadjava command loads part of it and I start getting Connection Closed error - ORA-00600: internal error code, arguments: [kqlInvObj:user], [70], [], [], [], [], [], [], [], [], [], []
ORA-06512: at line 1

java.sql.SQLException: ORA-00600: internal error code, arguments: [kqlInvObj:user], [70], [], [], [], [], [], [], [], [], [], []
ORA-06512: at line 1

Even when I was getting the nullpointerexception error for which you have given a detailed explanation, I used to be able to load the javaee jar. But after the DBA recreated the schema for me when I wasn't able to drop the jars, I am unable to load this jar.

Once I get all of it squared away, I will follow your steps / instructions and let you know how that worked out for me.

Thanks again for your help
ReplyDelete
Replies
AnonymousAugust 03, 2012 4:16 AM
I am trying to run a simple java application for the MQClient. I created a tester class and tried to call all the three methods. SendAndReceive, SendAndForget and Receive.

I am able to successfully put a message in the queue, but the receive part in SemndAndReceive always fails with the error - MQJE001: Completion Code '2', Reason '2033'. When searching, at many places it indicates that you can get this error if there is nothing in the queue, but when I browse the queue, there are about 60 messages.

The stacktrace shows

[MQJE001: Completion Code '2', Reason '2033'. MQRC_NO_MSG_AVAILABLEcom.ibm.mq.MQException: MQJE001: Completion Code '2', Reason '2033'.
at com.ibm.mq.MQDestination.getInt(MQDestination.java:647)
at com.ibm.mq.MQDestination.get(MQDestination.java:456)
at com.test.javasp.mq.MQClient.getMessage(MQClient.java:233)
at com.test.javasp.mq.MQClient.sendAndReceive(MQClient.java:362)
at com.test.javasp.mq.MQClientTester.main(MQClientTester.java:34)
, null, null, null, null, null, null, null, null, null]

I tried to use both MQ 7.0.1.8 jars as well as MQ 7.5 jars, with the same outcome.

Am I supposed to do some additional set-up when running it as an application? Of course, the put part is working, so I don't know, how would that be the case.

Thank you,
ReplyDelete
Replies
AnonymousAugust 03, 2012 4:30 PM
Thank you for the reply. I have not done any MQ development before so learning to do that as well along with learning to put this in as a Java Stored Procedure in Oracle.

I do agree that the code you have provided is generic and is supposed to run as is. But due to my lack on knowledge in MQ, I wasn't sure if I needed to make some change on my side to add any options when doing the get call. When I looked at the message, it has correlationId set to zero, not the mesage id, so I thought, I may need to change the put call to do that. From your explanation, it appears that MQ administrator needs to make that change.

I will let you know once it gets through.
ReplyDelete
Replies
UnknownMarch 07, 2013 10:05 AM
This post is probably where I got the most useful information for my research. Thanks for posting, maybe we can see more on this.
Are you aware of any other websites on this
IBM-MQ WEBSPHERE Online training
.
ReplyDelete
Replies
UnknownJune 09, 2013 9:49 PM
attractive piece of information, I had come to know about your blog from my friend arjun, ahmedabad,i have read atleast eleven posts of yours by now, and let me tell you, your website gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanks a lot once again, Regards, Synonyms In Oracle

ReplyDelete
Replies
AnonymousJune 15, 2013 11:22 PM
Your earlier explanations helped me a lot and I could get it working in our environment. Thak you so much for your detailed help.

I have one question that I haven't found an answer to.

In the start of the article, it mentions Oracle 11g supports JDK 1.5 level. And a little later in the table of required jars, it indicates we should use javaee-api-5.0.0.jar Java Enterprise edition ( This version should match Oracle JVM version )

I had thought JavaEE 5 supports JDK 1.6 and J2EE 1.4 supports JDK 1.5. So, how can we use the JavaEE 5.0 api jar instead of 1.4? Am I missing something here?

Thanks again
ReplyDelete
Replies
RomanJuly 03, 2013 2:08 PM
This comment has been removed by the author.
ReplyDelete
Replies
RomanJuly 03, 2013 2:09 PM
This comment has been removed by the author.
ReplyDelete
Replies
RomanJuly 03, 2013 2:10 PM
Hello!
I am trying to run a simple java application for the MQClient. I tried to call SendAndForget. In NetBeans it works fine, but in Oracle 11g (11.2 - instance on linux) I have same error:
java.lang.NullPointerException
at MQClient.putMessage(MQClient_2:198)
at MQClient.sendAndForget(MQClient_2:306)

this line is:
MQMessage mqMessage = new MQMessage();

Thank you
ReplyDelete
Replies
JakesIVFebruary 25, 2014 7:48 AM
I am trying to implement this solution and on first grant command I get an error.
grant connect to ORAMQ;
"ORA-01919: role 'CONNECT' does not exist"
SQLPLUS reports oracle release
"Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production"
JAVA -version
java version "1.4.2_14"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_14-b05)
Java HotSpot(TM) Client VM (build 1.4.2_14-b05, mixed mode)
So far I have been not been successful in deploying MQ to Oracle getting warning when loading my client app. and warning becomes error when running the app.
"ORA-29552: verification warning: java.lang.VerifyError: signature: (Ljava/lang/String;)I) catch_type not subclass of Throwable"
and got this when loading com.ibm.mq.jar
"loading com.ibm.ma.jar in Oracle
errors : class com/ibm/mq/MessageBufferManager
ORA-29552: verification warning: java.lang.VerifyError: (class: com/ibm/messageBufferManager, method: getBufferedMessage signature: (IILcom/ibm/mq/MQM;Lcom/ibm/mq/MQGetMessageOptions;)[B) Incompatible argument to function"
Seems like my case is different than what you described here.
ReplyDelete
Replies
UnknownJuly 16, 2015 12:48 PM
This comment has been removed by the author.
ReplyDelete
Replies
UnknownJuly 16, 2015 1:43 PM
Where can I find the correct javaee-api-5.0.0.jar?

When I try to load javaee-api-5.0.0.jar (jar size: 3994473) I got the following error:
ORA-29532: Java call terminated by uncaught Java exception: oracle.aurora.rdbms.MalformedDefinitionException: In ClassHandle.create in schema XXXXXX
ORA-29509: incorrectly formed Java binary class definition

When I use javaee-api-5.0-1.jar (jar size: 748851) then I can load com.ibm.mq.jar successfully without error, but when I run the program I get error at the following lines:
MQQueueManager queueManager = new MQQueueManager( qManager, props );
Error at the first run:
ORA-29532: Java call terminated by uncaught Java exception: java.lang.ExceptionInInitializerError
Error at the second run:
ORA-29532: Java call terminated by uncaught Java exception: java.lang.NoClassDefFoundError

The Orce JVM version:
java version "1.5.0.28"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0.28-_20_mar_2013_05_55)
Java HotSpot(TM) Server VM (build 1.5.0.28 jinteg:03.20.13-05:25 IA64, mixed mode)

It is possible that the the solution not work at HP environment, or I not use the correct javaee-api?
Thank you!
ReplyDelete
Replies
Paul FennemaNovember 09, 2017 2:11 PM
Hi Ashraf,

We are trying to implement your solution but unfortunately we are still stuck at the issue that when executing the stored procedure we get the following error:

ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
java.lang.ExceptionInInitializerError
ORA-06512: at "EMCF_MIS.SET_AND_FORGET", line 1
ORA-06512: at line 1

We tried several approaches to know where the ExceptionInInitializerError is coming from, but to no avail. Looking in the oracle database for which classes are invalid (not resolved) does not gives us a lot of clues unfortunately. Do you know of a way we debug the cause of the behaviour and hopefully solve it?

Thanks, Paul

ReplyDelete
Replies
Paul FennemaNovember 11, 2017 4:11 PM
Hi Ashraf,

Thanks for your help, these are the versions etc:
[oracle@hi032009 pf]$ java -version
java version "1.5.0_30"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_30-b03)
Java HotSpot(TM) 64-Bit Server VM (build 1.5.0_30-b03, mixed mode)
Oracle Path: /opt/oracle/product/11.2.0/db_1/jdk/bin:

I used your MQClient source with only the addition of a main method to aid in debugging which calls the set_and_forget method:
public static void main(String[] args) {
String [] messageIds = new String[10];
String [] result = new String[10];
sendAndForget( "emcfqa", 1414, "SMB.TST.SVRCONN", 1208, "QMNAA017", "SMB2CCM.PUT", "Hello World","SMB2CCM.GET", messageIds, result);
System.out.println(result[0]); // Display the string.
}

When I run this code, after the call to createQueueManager, the program counter immediately goes to the finally block with queue.close() as you can see in the output of jdb (which I attached)

Root Thread[1] next
>
Step completed: "thread=Root Thread", MQClient.sendAndForget(), line=306 bci=36
306 result[0] = "1: Start";

Root Thread[1] list
302 result[0] = "";
303
304 try
305 {
306 => result[0] = "1: Start";
307 queueManager = createQueueManager( mqHost, mqListener, mqChannel, ccsid, qManagerName );
308 result[0] = "2: QManager created";
309 queue = queueManager.accessQueue( queueName, qWriteOptions );
310 result[0] = "3: Write Queue created";
311
Root Thread[1] next
>
Step completed: "thread=Root Thread", MQClient.sendAndForget(), line=307 bci=48
307 queueManager = createQueueManager( mqHost, mqListener, mqChannel, ccsid, qManagerName );

Root Thread[1] list
303
304 try
305 {
306 result[0] = "1: Start";
307 => queueManager = createQueueManager( mqHost, mqListener, mqChannel, ccsid, qManagerName );
308 result[0] = "2: QManager created";
309 queue = queueManager.accessQueue( queueName, qWriteOptions );
310 result[0] = "3: Write Queue created";
311
312 mqMessage = putMessage( queue, messageContent, replyToQueueName );
Root Thread[1] next
>
Step completed: "thread=Root Thread", MQClient.sendAndForget(), line=330 bci=354
330 queue.close();

Root Thread[1] print result[0]
result[0] = "1: Start"
Root Thread[1] print queue
queue = null

Root Thread[1] print queueManager
queueManager = null
Root Thread[1] cont

The queueManager and queue are both null as you can see.

The permissions of the user are:
EMCF_MIS CONNECT NO YES NO
EMCF_MIS JAVADEBUGPRIV NO NO NO
EMCF_MIS JAVAIDPRIV NO NO NO
EMCF_MIS JAVASYSPRIV NO NO NO
EMCF_MIS JAVAUSERPRIV NO NO NO
EMCF_MIS JAVA_ADMIN NO NO NO
EMCF_MIS JAVA_DEPLOY NO NO NO
EMCF_MIS OWB$CLIENT NO NO NO
EMCF_MIS OWB_DESIGNCENTER_VIEW NO YES NO
EMCF_MIS OWB_USER NO YES NO
EMCF_MIS RESOURCE NO NO NO
EMCF_MIS SELECT_CATALOG_ROLE NO NO NO

Now looking at the privileges it could be that these are wrong?

Thanks again for your help!

ReplyDelete
Replies
Paul FennemaNovember 15, 2017 5:13 PM
Hi Ashraf,

We finally got it to work; it appears that 2 additional java permissions needed to be set:

Call dbms_java.grant_permission( 'ORAMQ', 'SYS:javax.management.MBeanServerPermission', 'createMBeanServer', '' );
Call dbms_java.grant_permission( 'ORAMQ', 'SYS:javax.management.MBeanPermission', 'com.ibm.mq.jmqi.monitoring.TraceControlImpl#-[MQ Classes for Java:name=TraceControl,type=Jmqi CommonServices]', 'registerMBean' );

We did not see the error message before as you need to redirect the java output to the sql console:
set serveroutput on;
set serveroutput on size 1000000;
call dbms_java.set_output(1000000);

We're all happy now :-)

ReplyDelete
Replies

Add comment