CODEWITHSUNDEEP
javaoracle-databasejmsoracle11gtomcat6
Mike
Mike

Reputation: 7914

Email notification on events - Oracle Queue

I've events table in Oracle database. I want to send email notification whenever a record is inserted into Oracle table. I'm using Tomcat & I don't have JMS or any other support.

I need to finish this in next 2.5 weeks.

Can I use Oracle Advanced Queuing in which Oracle will enqueue the data from events table? But I'm not sure if I can dequeu it with standalone Java Program?

Can any one recommend me some ways to achieve this with a sample example?

Note: I've to use JAVA only as its a requirement for us.

Thanks

Upvotes: 0

Views: 1114

Answers (4)

GrahamA
GrahamA

Reputation: 5923

Do you have to use Java to send the email? In the past I've used UTL_MAIL and a trigger to send emails from within the database when a row has been inserted.

You would need to create a process to send the email, possible using dbms_scheduler. AQ was a cost option at the time so I didn't use it.

ULT_MAIL
DBMS_SCHEDULER
Example showing how to send an email

You will need to get UTL_MAIL instaled in the Oralce Database as it's not part of the default install and, depending on the configuration of you mail server, have the database added to you list of 'safe senders'

Upvotes: 2

Mohsin
Mohsin

Reputation: 852

Using Oracle AQ will be much easier to do the task. Check the link -

http://docs.oracle.com/cd/B10501_01/appdev.920/a96587/apexampl.htm#59717

Upvotes: 0

Ro2man
Ro2man

Reputation: 1

one of the simpler ways to get it done is to create an INSERT trigger on that table, that will make a call to the email stored proc to email notification. of course you have to work with your email administrator to work out the proper email server address/name to use - if exchange use mail. as an example. if you need assistance with the scripts let me know via this post.

Upvotes: 0

Stephen ODonnell
Stephen ODonnell

Reputation: 4466

I have never done it, but you can use Oracle AQ from Java directly. Worst case, you could write some PLSQL to dequeue the next message and call that standard stored procedure from Java. Try googling for oracle AQ java.

For your problem, I would either use a trigger to place a message on the queue everytime a row is inserted into the table, or change the API used to insert into the table to also enqueue a message onto the queue.

Then you could have a separate process that reads each message from the queue and sends the email.

Upvotes: 1

Related Questions