I have a little experience with Oracle Alerts, but I was hoping someone with more knowledge in this area can tell me what advantage is there to using a periodic Alert vs. a scheduled concurrent program that runs a PL/SQL procedure.
Here is the scenario - the alert or program will run hourly and and send out an email if an exception condition is found. PL/SQL procedure sends the email via sendmail.
Looking at the Pros and Cons, this is what I see:
PL/SQL Pros: allows for greater complexity of determining exception, can change the run interval in Oracle scheduler, no need to re-migrate any objects, can document code with comments, seems to run slightly faster than alert.
Cons: Takes slightly longer for initial creation.
Alert Pros: Fairly quick to setup
Cons: Must change and re-migrate to change run interval, workflow mailer must be up and running as well as periodic alert scheduler (unless I am wrong about this), making two additional points of possible failure, can't comment SQL in the text window, the alert rejects it.
Anyone see any other pros and cons? Or why one might choose one over the other?
Yes, that seems logical, but what surprised me this week in testing a fairly simple alert and comparing it to the PL/SQL version was that the PL/SQL ran much faster. 2 seconds vs. 90 seconds when an exception was found. So, I'm having a hard time justifying ever using a periodic alert. Just wonder if anyone else has seen similar performance differences.
Both options carries merits and demerits based on your fitment, we usually go for PL/SQL with the concurrent program to decentralize maintenance to business users for scheduling and addition/deletion of recipient/contents based on time to time changing business dynamics or policies.
Event Alert behaves sometime very weird and will start sending 100s emails, to overcome this as an organizational strategy we stopped creating event alerts and even designed our own Custom Alert Manager to avoid maintenance from tech team. Once alerts are developed by tech team, it will be routed to business team for maintenance.