Is there any Oracle product that would let me access MSMQ via Oracle database/AQ/PL*Sql ?

The reasoning being :
It has been decided to use MSMQ for all the systems in the company (decision not in our control). Our database group needs to be able to access messages on the MSMQ queue and update Oracle data in our system. Expertise of our group is Oracle DB/PL*sql and not .net. If we could somehow access MSMQ via Oracle database (even if it's a separate Oracle product that we have to buy it separately), we could keep access to MSMQ transparent (to some extent) for our group. Does such a thing exist ?

How do you pick up from IBM MQ from PLSQL ?

hi tom,

I am trying to setup messaging gateway, and I cannot get past (in the AQ developers guide) the creating of the objects , all works fine until the end of the script catmgw.sql

Oracle 9i
Solaris 8

the error i get is as follows


Synonym created.


Synonym created.

begin
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel


CREATE OR REPLACE PACKAGE mgwi_reg wrapped
*
ERROR at line 1:
ORA-03114: not connected to ORACLE


CREATE OR REPLACE PACKAGE BODY mgwi_reg wrapped
*
ERROR at line 1:
ORA-03114: not connected to ORACLE


begin
*
ERROR at line 1:
ORA-03114: not connected to ORACLE


SQL> 

any ideas why I might be getting disconnected at this point ?

a little lost!

thanks 
phil 

Tom,

This seems to be the topic closest to Messaging Gateway, so here goes...
First, I have set up messaging gateway and a MQSeries client which are working very nicely together. I am sending messages triggered by changes in the database onto a MQSeries queue and all is fine. But what I would like to do is to have transactional control so that no messages are missed due to
- MQ Client is down
- Receiveing MQ Queue is down
- Network is temporarily down
- MGW is not running

Or do I have all of that through AQ for free :-o? If so do you have any points to useful guidelines? (other than the AQ Oracle documentation that is ;-)
Thanks,
/Tony

The environment is like follows:
- 9.2.0.6.0 Database
- Messaging Gateway
- MQSeries Client 5.3

And then there are a bunch of tables that make up the structure of products - and two of these mark the final variant changes, so I have set up triggers for these tables that put xml messages on one MQSeries Queue each (depending on what change on which table caused the trigger to fire).

Also since it says in the documentation that :

"The MGW agent may or may not automatically restart after a database shutdown or crash. The MGW agent should always be shut down before shutting down the database. If the MGW agent is running when a database SHUTDOWN NORMAL is done, the database will not shut down due to the database connections held by the MGW agent. For IMMEDIATE or ABORT the agent will not restart if the agent has time to exit normally; otherwise the agent will restart the next time the database is started."

Would it be a way to use system triggers on startup and shutdown to handle the shutdown and startup of the MGW automatically?

Best Regards,
/Tony

Ok,
But do you have any pointers to my first question now that I have specified a bit more about the configuration.

Brgds,
/Tony

Ok...

I get it now - I even did some tests that proves it.

One thing though - I tried to connect to a MQSeries server that has a remote Queue defined but I keep getting the "bad native queue ..." error
oracle.mgw.common.GatewayException: [800] MQSeries queue manager, TEST_QM, creation failed
...
[Linked Exception]
com.ibm.mq.MQException: MQJE001: Completion Code 2, Reason 2059
...

According to MQ Series Documentation 2059 is:

2059 MQRC_Q_MGR_NOT_AVAILABLE X'80B'

Queue manager not available for connection.
On an MQCONN call, the queue manager identified by the Name parameter is not available for connection at this time.
- On MVS/ESA, for CICS applications, this reason can occur on any call if the original connect specified a queue manager whose name was recognized, but which is not available.
- On OS/400, this reason can also be returned by the MQOPEN and MQPUT1 calls, when MQHC_DEF_HCONN is specified for the Hconn parameter.

If the connection is from a client application, this reason code can occur if there is an error with the client-connection or the corresponding server-connection channel definitions. This reason code will also occur if the optional MVS/ESA client attachment feature has not been installed.

This reason also occurs if an application attempts to connect to a queue manager within a group (see the Name parameter of MQCONN), when none of the queue managers in the group is available for connection at this time.

Corrective action: Ensure that the queue manager has been started. If the connection is from a client application, check the channel definitions.



But could it be that MGW links can not refer to remote queues on a referenced MQSeries server/Queue manager?
(I am sending messages onto a MQSeries local queue successfully)

I know that you've not fiddled around too much with MQSeries but maybe you have some info on mgw links referencing remote queues on a MQSeries Server, or? ;-)

Brgds,
/Tony

Dear Tom,

Thanks for helping us through this forum.

I am curious to know few things about Oracle's Advanced Queue(AQ) which are not in the document. Since Advanced Replication and Streams are built on top of AQ, like to
know the underlying technology for AQ.

1)My understanding is Advanced Queue is built based on Java Message System(JMS) specification (don't know-- may be JMS 1.0 or JMS1.1) provided by Sun Microsystems. Is it correct?

2) As oracle JDBC provides more functionality than standard JDBC specification,AQ is Oracle's JMS Extensions(in addition to Standard JMS) provides additional features like
Administrative API to create queue tables, queues and topics
Supports transacted sessions that enable you to perform JMS as well as SQL operations in one atomic transaction
In addition to the standard JMS message types, Oracle supports AdtMessages
etc.
3) If AQ meets JMS specification then it can communicate with any 3rd party vendor product(like IBM MQ Series,MS Queue ,etc) that meets JMS specification. Right?
Then why do we need separate Gateway to communicate with each messaging product?

4) Is AQ written in pure Java or C?

Thanks for your valuable time.

Many Thanks Tom. On sunday evening, no one will respond like you do.

I was thinking the other way(AS is built based on JMS spec) because I looked at one of the Oracle Class diagram at
</code> http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96587/adq_bgph.htm#649819 <code>

Here oracle.jms package inherited many things from javax.jms package.

Tony's issue with MGW and remote queues , check out - Oracle 9.2.0.1 Messaging Gateway Bundled Patch 3 , on metalink.

regards
phil

I have a issue
1. we are using j2ee app to send messages to OJMS(AQ) and have created MDB(message driven bean) which is listener, that process messages which are propogated by oracle AQ.
2. Application are deployed in clustered envir. in Oracle AS 9.0.4
3. database is 10g which is RAC enabled has 6 RAC with 2 RAC active for load balancing.

what we are seeing is we can send messages, but the messages are sitting in queue table for over period of time and then suddenly get enqueuing( we are not setting any delay while adding on the queue). Is there any thing I need to specify in AQ that we might be missing or in MDB that we are missing.
Do any body have any idea?
help is appreciated
Regards
Feroz

Hi Tom,

I want to enqueue array of messages using OJMS just like it can be done using DBMS_AQ.ENQUEUE_ARRAY in PL/SQL. And dequeue them either as an array of messages or as an individual message. Each message may have their own property.

Is it possible? If it is then how?

Also waiting for the response for the above asked question since we have the requirement to implement OJMS on RAC 10gR2.

Thanks in adv.
Sanju.

Hi,

This is my firs time placing a queston here, hopefully in the right place...

We are trying to integrate Oracle db 10g (forms application) to a non-oracle db using message queuing. One solution would maybe be AQ/Oracle Message gateway, someone told that we could do the same with Java Driven Bean? Which is the more appropriate way considering our environment? Why not use MG, what would be the benefit of using JDB?

Thanks beforehand

Hi,

Sorry, i mixed terms in my head when i wrote the question. The other option was Message-driven bean not java driven bean as i wrote earlier.

Thanks you very much for the help.

Hi,

Thanks for the tip, we will concider this JMS as an option for message gateway. Is there any other options for persistant message queuing?

Dear Tom,

I have been trying - without any success - to make MGW work.

ENV.:
Oracle Enterprise 10g R2
O/S : Solaris SPARC 9

catmgw installed
AQ + Streams tested and is working

However, I am hitting brick walls with MGW config, i.e.
1) I followed various on-line docs to set up listener + tnsnames + mgw.ora = but it really is unclear so I must be doing it wrong
2) I installed the IBM Web-Sphere MQ6 client
3) Oracle docs all say "configure this and that according to your environment" + and later: "We assume that you have configured listener+tnsnames+mgw.ora correctly and here is what you should do to find why you are getting ORA-28575 error..."

Tom,
Have you ever worked with this environment (or know one who has?) = i.e. configured AQ/Streams + MGW to work together and push/pull data to/from IBM non-Oracle system and Web-Sphere MQ 6.

Perhaps - just a thought - I could try a simple configuration first where AQ Streams on one Oracle database would send a message to MGW which in turn would pass it onto another Oracle DB to receive - as a proof of concept that MGW works first and without added complexity of MQ6 series agent to an IBM system..

BTW.
We have reported this to Oracle via metalink - but have not received any reply.

Kind regards
Andrew