I need help with JMS and Oracle Trigger in JBoss 3.2.2 RC3

[cheenu78]

hi all,
I have a senario where in, I need to publish a message when a table in inserted or updated. That is, is it possible for an Oracle Trigger to publish a message to the JMS Topic?
thanks in advance
srinivas

[genman]

This is more of an Oracle question than JMS one.

You can run any Java code you want using Java stored procedures in Oracle. Then it becomes a matter of including the JBoss jars and running the example client code in Oracle.

[p0six]

It's a little round about, but if you have Oracle Advanced Queue you can publish your messages to the an Oracle Advanced Queue and then use the Oracle Advanced Queue Patch to connect the JBoss Server to the queue.

[sanjewad]

Hi
We have the exact problem. Did you find a solution to this?

[bruce2]

I have been chasing down exactly this problem for the last week.

My two previous posts are on this and related issues.

I wanted to invalidate entity beans in JBoss using asynchronous events generated by database triggers.

I started off trying to send JMS messages directly to the JBoss cache invalidation service from Java stored procedures in Oracle. I managed to get this working after a fashion in Oracle 9.0.1, but it was throwing an Exception in server.log with every message due to acknowledgements not being received properly. I raised this issue with Oracle Metalink and they informed me that they were de-supporting pretty much of all of J2EE in Oracle 9.2 and above. They want you too use OC4J.
So I've decided that sending JMS messages from Oracle to JBoss is out.

I considered sending messsages to Oracle AQ, and doing an EJB callout from there, but seems likely to run into similar problems when Oracle loses J2EE (although I haven't investigated that idea fully).
I don't know about the patch that p0six mentions.

I'm actually now thinking of writing an mbean to run as a service in JBoss and using good old-fashioned sockets to send messages from Oracle. This means I only have to use J2SE functionality which Oracle will (hopefully) continue to support for the foreseeable future.

[bwallis42]

"cheenu78" wrote:
hi all,
I have a senario where in, I need to publish a message when a table in inserted or updated. That is, is it possible for an Oracle Trigger to publish a message to the JMS Topic?
thanks in advance
srinivas

A late reply I know, but... We have done this successfully, but we do a little extra work to decouple oracle and JBoss. Our trigger writes a record to a DB table for each change and we periodically poll that table (from an MBean using JDBC) and publish the message using standard JBoss JMS functionality.

At the time this was quite successful and easy to maintain but the overhead is a bit high if there are a lot of changes. We are investigating using Oracle Streams (in 9.2.0.5.0) to asynchronously capture and publish the changes and then to interface to the queue either directly or via the oracle JMS interface from our J2EE application. Haven't got that working yet.

brian wallis...

[raja05]

Bruce
Do you know that there is a flushCache method on all the Entity beans thats exposed through JMX? That means you could have a adaptor(RMI, HTTP, whatever) to get to the jmx object that wraps ur ejb and call flush cache on that.

[nivek]

We accompliished this using socket's. Oracle trigger fires off a
socket message using the Oracle utl_smtp package.
Then the message can be handled how you wish by your socket
server (i.e., JDBC, JMS etc..).
I heard Oracle is desupporting J2EE support inside the DB
and going w/ there OC4J crap..