• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Email Alerts

 
sudha swami
Ranch Hand
Posts: 186
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

When ever there is an insert of record in the table, I want to generate an email alert? What is the best approach to follow?


Thanks
Sudha
 
Arun Kumarr
Ranch Hand
Posts: 661
Eclipse IDE Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Assuming you are going to use some sort of java code to make the alerts, did you try googling on how to call java from oracle, db2, mysql etc.,
 
Dinesh Kumar Ramakrishnan
Ranch Hand
Posts: 68
Firefox Browser Java Windows XP
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I can be done with oracle trigger and UTL_SMTP. give a try.

Create a trigger on the table based on the event after inserted on. And use UTL_SMTP package to sending email.

Here i am attaching tutorial links for UTL_SMTP
http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_web.htm#ADFNS1001
http://oracle-base.com/articles/misc/email-from-oracle-plsql.php
 
sudha swami
Ranch Hand
Posts: 186
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

thanks for the reply. Its not a good design practice if you use trigger with UTL_SMTP package because if the SMTP server is down, the entire tranascation will rollback. Is there any alertnative approach?
Any thoughts about Database Change Notification which is introduced in Oracle 11g?


Thanks
Sudha
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
sudha swami wrote:Its not a good design practice if you use trigger with UTL_SMTP package because if the SMTP server is down, the entire tranascation will rollback. Is there any alertnative approach?

I'm not very fond of sending emails from the database, but principally it is possible to use DBMS_JOB package to schedule a job that will send the email. If the original transaction is rolled back, the job isn't created (and the email isn't sent). Of course the scheduled job starts only after the original transaction commits, and therefore even if it fails, the original transaction isn't affected.

Any thoughts about Database Change Notification which is introduced in Oracle 11g?

If you update the table rarely, in small transactions (few rows/tables affected), and only a few rows at a time, the database change notification might work for you. If you run a large transaction that updates the table, you might end up getting a notification that something in the table changed, without telling you which specific rows were affected, which might not be that useful to you.

Oracle's documentation says:
For best performance of change notification, the following guidelines are presented.Registered objects are few and mostly read-only and that modifications to those objects are the exception rather than the rule. If the object is extremely volatile, then it will cause a large number of invalidation notifications to be sent, and potentially a lot of storage in the invalidation queue on the server. If there are frequent and a large number of notifications, it can slow down OLTP throughput due to the overhead of generating the notifications.It is also a good idea to keep the number of duplicate registrations on any given object low (ideally one) in order to avoid the same notification message being replicated to multiple recipients.

Also, the database change notification registration is independent of the connection that created it. It might cease to exist for various reasons, and you won't be notified about it. In one project where we wanted to use it, we had to verify in some intervals it existed and recreate it if it failed. We eventually had to abandon the approach because our tables weren't "mostly read-only" after all.

I'd say the first solution - trigger and DBMS_JOB - might be easier (a lot easier, actually) than setting up the DCN. We ended up replacing the DCN with a set of triggers ourselves.
 
sudha swami
Ranch Hand
Posts: 186
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

Thanks for the reply Martin. The information was very helpful and clear. We don't have many inserts in the table.

I needed one clarificaton in the below quote

"Also, the database change notification registration is independent of the connection that created it. It might cease to exist for various reasons, and you won't be notified about it. In one project where we wanted to use it, we had to verify in some intervals it existed and recreate it if it failed. We eventually had to abandon the approach because our tables weren't "mostly read-only" after all. "

How did you verify in the intervals whether its existed or not and recreated it when failed?

Thanks
Sudha



 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There's a data dictionary view user_change_notification_regs in Oracle which contains all DCN registrations created by the current user. We got a list of all existing registration IDs from this view and compared it to the list of registrations in the application server. When any registration was missing, we dropped the missing one from the list, recreated it and put the new one into the list. We kept registration details so that we knew exactly which query needed to be re-registered.

 
sudha swami
Ranch Hand
Posts: 186
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Martin,

Thanks for the reply. I need some clarification for the following:


1.Whenever there is an insert in the table, the trigger gets invoked and sends an email with the new record details. If the SMTP server is down, will insert of the record in the table gets rolledback.
2.Do we have any concept similar to DCN?
3. If we run a scheduler on a table, how do we know whether new record is inserted in table.Email alerts are sent only for the new records.

Thanks
Sudha
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
First of all, are you using Oracle? I assume you do, but you haven't explicitly told so.

sudha swami wrote:1.Whenever there is an insert in the table, the trigger gets invoked and sends an email with the new record details. If the SMTP server is down, will insert of the record in the table gets rolledback.

Are you talking about a trigger-based solution? You can use DBMS_JOB in trigger in Oracle to send the email; this solution assures that if the SMPT server is down, the transaction won't be rolled back.

If this doesn't answer your question, please clarify which solution are you speaking about. You can read about DBMS_JOB here.

2.Do we have any concept similar to DCN?

Do you mean in other databases than Oracle? Or a similar concept in Oracle? (I don't know about any in either case, except some trigger-based solution you'd program yourself.)

3. If we run a scheduler on a table, how do we know whether new record is inserted in table.Email alerts are sent only for the new records.

I'm afraid I don't understand again. Which scheduler? (We haven't spoken about "scheduler" yet.)

If you want to be able to find new records added since last time you've looked, there are certainly possible solutions. If you only insert into the table, never update or delete, that solution might be much easier. We can talk about it in more detail.
 
sudha swami
Ranch Hand
Posts: 186
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Martin,

Thank You for your inputs and guidance.

1. I am talking about the oracle trigger based solution. Inside the trigger, if I don't use DBMS_JOB to send an email instead I use UTL_SMTP code directly, will insert transaction gets rolled back when the SMTP server is down? Why?
2.Similar concept to DCN in Java. In one of the threads you were talking about client result cache.
3.I am looking only for inserts in the table since last 15 minutes.


Sudha
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
sudha swami wrote:1. I am talking about the oracle trigger based solution. Inside the trigger, if I don't use DBMS_JOB to send an email instead I use UTL_SMTP code directly, will insert transaction gets rolled back when the SMTP server is down? Why?

I'd guess that what happened here was the procedure in the UTL_SMPT package has thrown an exception. An unhandled exception in a trigger causes the statement that invoked the trigger to fail as well, which should in turn result into an SQLException in your application. The rest depends on how your application handles it, but the standard procedure is to rollback the transaction in such case. You should be able to verify in your log files that this is actually happening (hopefully you do log exceptions ).

You could try to handle the exception in the trigger - PL/SQL has constructs for that, similar to Java's try-catch.

(Using DBMS_JOB has an additional advantage: sending occurs outside of your transaction, so if sending the mail takes significant time for some reason, the original transaction isn't affected by it.)

2.Similar concept to DCN in Java. In one of the threads you were talking about client result cache.

That's something different. If you're executing one query repeatedly (say, select * from my_table), the client result cache feature (which requires JDBC OCI client, I believe) would make sure that this query isn't executed in the database over and over again, but is only re-executed when the results of that query have actually changed. This lowers the load on the database. I haven't used this feature myself, so I might not be able to help you here much.

3.I am looking only for inserts in the table since last 15 minutes.

Is your intent to be able to process every new record in certain way (say, to sent an email about every new record inserted)? If so, the easiest solution by far would be to add a column to the table (eg. unprocessed number(1) default 1), and only select rows where unprocessed is not null. These would be the new rows. And once you process that row (send a mail...), you'll update the unprocessed column to null. (This trick with the null value would allow you to construct an index on the unprocessed column, which would only contain the unprocessed records and therefore stay small, as null values aren't indexed in Oracle.)

Techniques that wouldn't require additional column to be added can be a bit complicated, as it is in general very tricky to select rows added in last 15 minutes without risks of missing some.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic