Oracle Alert
What
are Oracle Alerts?
Oracle Alerts monitor your Database information and notify you
when the condition that you have specified is found. You can define Alerts in any
Oracle application or custom Oracle application. Some applications (Purchasing,
for example) supply Alerts that Can simply be activated and used. There are two
type of Alerts, Event and Periodic.
1. Periodic Alerts
2. Event based Alerts.
Event Based Alerts:
These Alerts are fired/triggered based
on some change in data in the database.
Periodic Alert:
These Alerts are triggered hourly,daily, weekly, monthly or yearly based on your input.
EX: Employee Birthday can be any calendar day of the year. So we will tell oracle apps
to check daily once every calender day and see if today is employee's birthday and send email if true.
You need Alert Manager Responsibility to define a new Alert.
Navigate:
Alert Manager -> Alert -> Define
We need to write SQL which satisfies our condition and also to fetch required details.
SELECT global_name,
date_of_birth,
email_address into &emp_name,
&dob,
&emp_email
FROM per_all_people_f
WHERE trunc(sysdate) between effective_start_date and effective_end_date
AND to_char(to_date(date_of_birth),'dd') = to_char(to_date(sysdate),'dd')
AND to_char(to_date(date_of_birth),'mm') = to_char(to_date(sysdate),'mm');
Note:
- Your periodic alert Select statement must include an INTO clause that contains one output for each column selected by your Select statement.
- Identify any inputs with a colon before the name, for example, :INPUT_NAME.
- Identify any outputs with an ampersand (&) before the name, for example, &OUTPUT_NAME.
- Do not use set operators in your Select statement.
- You can use PL/SQL functions in your Select statement to fetch complex business logic.
- § Enter the name of the application that will own the alert§ Enter a suitable Name of the alert (up to 50 characters), andgive it a meaningful description (up to 240 characters).§ Select a frequency for your periodic alert. You can choose from nine frequency options:1. On Demand2. On Day of the Month3. On Day of the Week4. Every N Calendar Days5. Every Day6. Every Other Day7. Every N Business Days8. Every Business Day9. Every Other Business Day§ Choose ‘On Demand’ frequency when you are developing a periodic alert so that you can test your alert at any time you want. When you will sure that the alert is working fine, then you can change the frequency as per business need.§ Depending on the frequency you choose in the previous step, the Start Time and End Time fields become enabled. You may also specify the number of times within a 24-hour period that Oracle Alert checks your alert.§ Specify a value in the Keep _ Days field to indicate the number of days of exceptions, actions, and response actions history you want to keep for this alert.§ Specify a value in the End Date field if you want to disable your alert by a certain date.§ Enter a SQL Select statement that retrieves all the data your alert needs to perform the actions you plan to define.
If any row that matches condition, it is called exception in Alert.
So when you click "run" button, it will display the number of exceptions occurred.
Next step is to define action if condition matches.
Don't forget to select action level of type "Detail".
This is because action should be performed once for every Alert Exception.
We also need "Action Sets" and attach the action which we just created.
Go to Alert Manager -> Request -> Check, and schedule the Alert to run it sometime after current time.
It will submit a concurrent program. [In this example CP Name is "Birthday Wishes E-mail Alert (Check Periodic Alert)"]
Once concurrent program is successfully ran, we can verify the number of exception from
Alert Manager -> History and query for alert.
