How to notify JBoss with Oracle trigger

I need to implement the following:

There is a table A which is supposed to be changed by DML operations coming from trigger on another table B.

I want to notify Jboss bean every time the trigger works. In best case I'd like to have an opportunity to see OLD and NEW data in the bean.

What is the best way to do it? Could you recommend any material to read?

Answers


Alex's proposal to use Database Change Notification is probably the best way to go. Here's a few other options for actions to signal an event to a Java process from an Oracle PL/SQL package (which you can call from a trigger):

  • Use DBMS_PIPE to asynchronously send a message to a Java client listening on the same pipe.
  • Use DBMS_ALERT to send a message to a Java client listening on the target alert. The message will not be received until the transaction commits.
  • Use UTL_HTTP or UTL_SMTP or UTL_TCP to send a message to a Java process listening on the target HTTP, SMTP or TCP endpoint.
  • Send an Oracle/AQ JMS message to a Java client listening on the target queue/topic.
  • Create a JMS client in Java and load the java classes [and dependencies] into the Oracle database. Wrap the class in a PL/SQL procedure and call it to signal a Java client listening on the target queue/topic.

There's a lot more info on sending JMS messages from PL/SQL in the question Calling/Using JMS from PL/SQL.


You could have the trigger insert a row in a signal table.

A JBoss process could then monitor the signal table, checking for new signals at regular intervals.


Need Your Help

CXF - No message body reader

json rest cxf provider

I've tried many of the solutions in this forum to address this issue with no luck. I am using Apache CXF (v.2.5.11) and I need to post some Json to a REST endpoint and have the data unmarshaled to ...

Optimize MethodInfo.Invoke when method info is only known at runtime

c# performance reflection invoke

I am using reflection to call methods, and invoking the methods using Invoke() doesn't do it for me, too slow. I generate thousands - millions of method calls, it is a test automation tool. At comp...