Oracle Alert Creation Example

I had created one alert long time back and few days back i had to test one alert and I did not remember anything, So read the this document and noted following points for future reference.

1. Responsibility: Alert Manager


2. Navigation: Alert -> Define


3. You will see following form (with all fields balnk)
   
   


4. Periodic tab has information for concurrent program "Periodic Alert Scheduler". "Periodic Alert Scheduler" will schedule a concurrent program to run the alert based on Frequency column value.
   
   Concurrent program name will be Alert Name + ' ' + (Check Periodic Alert).
   
   Start Time, End Time and Check Interval are used for running it multiple times in a day starting at "Stat Time and ending at "End Time" at an interval given by "Check Interval" Value. Suppose an alert is defined to run "Every 1 Calendar Days" and start time, end time and check interval are 00:00:00, 9:00:00 and 01:00:00 and "Periodic Alert Scheduler" is scheduled to run everyday at 7 am, then Alert will run between 7 am to 9 pm every 1 hours. Three times in this case. If "Periodic Alert Scheduler" was scheduled to run at 12 am everyday then this alert will run 10 times every day (12 am to 9 am). In this particular case i had changed the schedule of "Periodic Alert Scheduler" to start at 7am and run indefinitely.
   
   If "Periodic Alert Scheduler" is not running then any alert will not be run. This may happen in development environment so that users do not get unnecessary alert about test data or developer can change the email change the alert recipient's email address


5. Import Button: If you do not want to cut and paste query from a file or sql developer, you can use this button to enter sql statement for Alert


6. Export Button: If you do not want to use cut and paste, you can use this button to save query to a file


7. Verify Button: Use this button to check for syntax errors


8. Run Button: Use this button to know how many rows will be returned.


9. Actions Button: It seems results of sql statement is called EXCEPTION. Based on results of the query you may want to
   
   
   1. If Query returns No rows then it is a case of "NO Exception" and you may take an action such as send email that informing alert recipient that No results were found. it may be good or bad. For example if no orders were shipped yesterday then it is bad and if no concurrent programs failed yesterday, it is good
   
   
   2. If query returns some result then you may send "Summary" action i.e. send one email providing results of sql statements
   
   
   3. If query returns some results then you may send "Detail" action i.e. send alert for each row of query result. For example, Alert query may identify users whose password will expire within N days. Detail action can send email to each user separately and request them to change password.
   
   
   Each action can be of following 4 types
   
   
   1. Message
   
   
   2. Concurrent Program
   
   
   3. Operating system Script
   
   
   4. Sql Statement Script
   
   
   i have use only "Message" action type. For "Message" action type, you need to fill in detail like Email Address for alert recipient, subject and Layout for Query output.


10. Action Sets Button: Any of the "Actions" created in previous step will not do anything unless included in some action set. In Members tab in Action Set Detail form, you may added actions created in previous step.


11. Response Set Button: Did not use it


12. Alert Details Button: In this form you can define operating unit for Alert. If i available under table "Installations" I did not change Oracle ID value, it was displayed by default as APPS