Hello All,

Just wondering what will be the best approach to send insert, updates and deletes from an Oracle Database to any topic/message queue product(ActiveMQ, Kafka, IBM MQ ..and so on). I have been trying to find the best way to do it, but have not been able to come to a conclusion.

I had looked into 2 ways

1. Using PL/SQL triggers to send to AQ(ORacle Advanced Queue).
2. Using same PL/SQL triggers and read message in java(JMS) and forward them to a selected message queue product (Looks straightforward to me but looks like an extra step)

Are there any other better ways to do it? Or the above methods are incorrect?

PS: This table gets around 20k to 30k updates per hour. And also I cannot get any message product I want, mostly TIBCO, but I am assuming the way to do it will be the same.

Thanks in Advance! Appreciate your help.

CodePudding user response：

There are several ways to send insert, update, and delete operations from an Oracle database to a message queue. Here are a few options you can consider:

1. Using Oracle Advanced Queueing (AQ): You can use AQ to capture changes to the database using triggers, and then send the change data to a message queue. This is a native solution provided by Oracle and is relatively easy to set up.

2. Using a Java application with a Java Messaging Service (JMS) provider: You can use a Java application to capture changes to the database using triggers, and then send the change data to a message queue using a JMS provider, such as ActiveMQ, Kafka, or IBM MQ. This option requires some programming effort, but it gives you the flexibility to choose from a wide range of JMS providers.

3. Using a change data capture (CDC) tool: There are several CDC tools available that can capture changes to the database and send them to a message queue in real-time. Some examples include Oracle GoldenGate, Attunity Replicate, and HVR. These tools are typically more powerful and scalable than the other options, but they may also be more expensive and require more setup and maintenance.

Overall, the best approach will depend on your specific requirements and constraints. You should consider factors such as performance, reliability, complexity, and cost when choosing the right solution for your use case.