ORACLE ALERTS
Overview of Alerts:
Oracle Alerts facilitates the flow of information within the organization by letting us create entities called alerts to monitor the business information and to notify us of the information we want.
Oracle Alerts
Keeps us informed of the critical activities in the database.
Delivers the key information from applications, in the format we want.
Provides us with regular reports on database information.
Automate system maintenance, and routine online tasks.
Types of Alerts:
Event Alert
Periodic Alert
Event Alert: An Event Alert immediately notifies us of activity in the database as it occurs i.e. This type of
alert will fire on a database event, such as an update or insert into a specified table.
Periodic Alert: A periodic Alert checks the database for information according to a schedule we
define.
Defining Periodic Alert:
Responsibility : Alert Manager, Vision Enterprises
Navigation : Alert → Define
1. Choose the Application that owns the alert for example: Inventory.
2. Enter Name (for example XX_TEST1) for the alert.
3. Check Enabled flag – for activation of alert. You can leave it unchecked until you are ready to run the alert.
4. Choose Periodic tab. Enter the Frequency and Start Time you want the alert to run.
Select a frequency for your periodic alert from nine frequency options:
• On Demand—If you choose this frequency, Oracle Alert checks your periodic alert only when you specify. You do not have to fill in any other field in the Periodic Details block. You check on
demand alerts by using the Request Periodic Alert Check window.
• On Day of the Month—If you choose this frequency, Oracle Alert checks your alert on a monthly basis on the day number you enter in the Day field.
• On Day of the Week—If you choose this frequency, Oracle Alert checks your alert on the day of the week you enter in the Day field.
• Every N Calendar Days—If you choose this frequency, enter a value in the Days field. Oracle Alert considers every day a calendar day, and does not skip holidays.
• Every Day—Choosing this frequency is the same as choosing Every N Calendar Days and entering a value of 1 in the Days field.
• Every Other Day—Choosing this frequency is the same as choosing Every N Calendar Days and entering a value of 2 in the Days field.
• Every N Business Days—If you choose this frequency, enter a value in the Days field. Oracle Alert lets you choose your business days, but does not skip any holidays. A value of 1 indicates that Oracle Alert should check the alert every business day, and a value of 2 indicates that Oracle Alert should check the alert every other business day. If you enter 3 in the Days field, Oracle Alert checks your periodic alert every three business days.
For example, if you enable your alert on a Monday, Oracle Alert checks the alert first on that Monday, then on Thursday, then on the following Tuesday, then the following Friday, and so on.
• Every Business Day—Choosing this frequency is the same as choosing Every N Business Days and entering a value of 1 in the Days field.
• Every Other Business Day—Choosing this frequency is the same as choosing Every N Business Days and entering a value of 2 in the Days field.
6. Specify the number of days you want to keep the alert history logs in Keep Days.
7. Enter SQL statement in Select Statement.
Select statement must include an INTO clause that contains one output for each column selected by your Select statement.
For Example:
SELECT SEGMENT1,:DAYS
INTO &ITEM_NAME,&NDAYS
FROM MTL_SYSTEM_ITEMS_B
WHERE TRUNC(CREATION_DATE)=TRUNC(SYSDATE-:DAYS);
Identify inputs with a colon before the name (in above example :DAYS) and outputs with an ampersand (&)
before the name (in above example &ITEM_NAME, &NDAYS).
you may also import a SQL Select statement from a file in your operating system.
Choose Verify to parse the Select statement. The results will be displayed in a Note window.
Choose Run to execute the Select statement. It will display the number of rows returned in a Note window.
Save your work.
Re-Query the alert created and click on Alert Details Button.
The Alert Details window includes information such as which Application installations you want the alert to run against, what default values you want your inputs variables to use, and what additional characteristics you want your output variables to have.
The Alert Details window that appears contains three tabbed regions: Installations, Inputs, and Outputs.
In the Inputs tabbed region, Oracle Alert automatically displays the inputs used in your Select statement, unless they are the implicit.
Specify the Default Values for the inputs.
In the Outputs tabbed region, Oracle Alert automatically displays the outputs used in your alert Select statement without the ampersand (&).
In the Installations tabbed region,Oracle Alert automatically displays the Oracle ID that is associated with the application that owns your alert.
Specify the organization you want the alert to run against in the Operating Unit field.
Creating Alert Actions:
After defining a Period or Event alert, you need to create actions for the alert to perform. On the same alert form, click on Actions button.
1. Enter the Action Name for the Alert.
2. Select a level for Action: Detail, Summary or No Exception.
During an alert check, a detail action performs once for each individual exception found, a summary action performs once for all exceptions found, and a no exception action performs when no exceptions are found.
Choose Action Details button in Actions form.
On the Action Details page, select the type of action you want to create in the Action Type field.
Depending upon the action level and type you choose, different fields appear in the Action Details window.
Message Action: To send a message as an action we create Message Actions.
Concurrent Program Actions: To run a concurrent program as an event action we create Concurrent Program Actions.
Operating Script Actions: To run an operating script as an event action create Operating Script Actions.
SQL Statement Script Actions: To run a SQL statement script as its action then create SQL Statement Script Actions.
Specify the electronic mail IDs of the recipients you want to send your message to in the To: field.
You can also enter the IDs of recipients to whom you want to send ”carbon” (Cc) and ”blind” (Bcc) copies of your alert message.
Specify the subject for the message in Subject field.
If you want to write your message in Oracle Alert, choose the Text option and enter the text in the Text field. (You can also click on the Import button and import the contents of a file into the Text field.)
You can include any outputs in the message text. (In the above screen shot you can see that ITEM_NAME is the output variable).
Save the Record.
Creating an Action Set:
Once you create your alert actions, you must include them in an enabled action set for Oracle Alert to perform during an alert check. An action set can include an unlimited number of actions and any combination of actions.
During each action set check, Oracle Alert executes each action set member in the sequence you specify.
Steps:
Click on Action Set button in Alert main form and enter the Action Set Name and save the record.
Re-Query the Alert and click on Action Set Button. You can see that input and output variables are
automatically displayed in respective Input and Output tabs.
Note: At runtime, alert will use the values from the Action Sets form. If you want the alert to run for different
Input value then make changes in the Action Set Definition form.
In the Members tab choose the Action you want the alert to perform.
Save the Record.
Running an Alert:
To manually run a Periodic Alert, Navigate to Request → Check
Enter the Application that owns the Alert
Enter the name of the Periodic Alert you want to check and submit the Request.
Once the concurrent program is done successfully navigate to Alert → History to review history.
Enter the Application that owns the Alert.
Enter the name of the Periodic Alert you want to view.
Oracle Alert automatically displays the alert type, the number of days of history kept, and when the alert was last checked.
Click on Find Checks button.
In this form it displays the history of action sets that were run: the date and time when the action set completed and the Oracle ID that the action set ran against.
In the Action Set Checks it displays the name of the Action set and the number of exceptions found and the number of actions performed.
Click on Find Exceptions in the History window.
In this form it displays the Exceptions found for each out put variable and their corresponding values.
Click on Find Actions button.
In this form it displays the action name, type of action and Finished time of the action.
It also displays the details of the recipient to whom the message was sent.
Defining Event Alert:
Responsibility : Alert Manager, Vision Enterprises
Navigation : Alert → Define
1. Choose the Application that owns the alert i.e. Inventory
2. Enter Name & Description for the alert
3. Check Enabled flag – for activation of alert. You can leave it unchecked until you are ready to run the alert
4. Choose Event tab.
5. Choose the Application and database table you want Oracle Alert to monitor.
6. Check After Insert and/or After Update if you want the event alert to run when a user inserts and/or updates a row in the database table.
7. Specify the number of days you want to keep the alert history logs in Keep Days.
8. Enter SQL statement in Select Statement
SELECT SEGMENT1, INVENTORY_ITEM_ID,:ROWID,:ORG_ID,:MAILID
INTO &ITEM_NAME, &item_id,&ROWID,&ORG_ID,&MAILID
FROM MTL_SYSTEM_ITEMS_B
WHERE TRUNC(LAST_UPDATE_DATE)=TRUNC(SYSDATE);
Choose Verify to parse the Select statement. The results will be displayed in a Note window.
Choose Run to execute the Select statement. It will display the number of rows returned in a Note window.
Creating Actions and Action sets for Event alert is same as Creating Actions and Action sets for Periodic alert.
Event alert gets checked when an insert or an update to an event table occurs.
Implicit Inputs:
Oracle Alert automatically displays values for the Implicit Inputs.
:ROWID, :MAILID, :ORG_ID and :DATE_LAST_CHECKED.
The values of the implicit inputs are as follows:
• ROWID—Contains the ID number of the row where the insert or update that triggers an event alert
occurs.
• MAILID—Contains the email user name of the person who enters an insert or update that triggers an
event alert.
• ORG_ID—Contains the organization ID that is selected when the alert runs.
• DATE_LAST_CHECKED—Contains the date and time that the alert was most recently checked.
For Example consider an Event Alert with the select statement:
SELECT SEGMENT1, INVENTORY_ITEM_ID,:ROWID,:ORG_ID,: DATE_LAST_CHECKED
INTO &ITEM_NAME, &item_id,&ROWID,&ORG_ID,& DATE_LAST_CHECKED
FROM MTL_SYSTEM_ITEMS_B
where TRUNC(LAST_UPDATE_DATE)=TRUNC(SYSDATE);
Text in the Action Details
-------------------------------
item name :&ITEM_NAME
ITEM_ID: &ITEM_ID
ROWID: &ROWID
ORG_ID:&ORG_ID
DATE_LAST_CHECKED:&DATE_LAST_CHECKED
Output
------------
item name :SR_ITEM
ITEM_ID: 17849
ROWID: AAAehOAAkAAAB9SAAL
ORG_ID:204
DATE_LAST_CHECKED:31-MAR-2009 11:34:28
References:
Alerts User Guide.
http://www.aboutoracleapps.com/2007/07/oracle-alerts-interview-questions.html
