Alerts-in-Oracle Applications

Basic Business needs

• Keep you informed of database exception conditions, as they occur.
• Keep you informed of exception conditions through a single point your electronic mail.
• Take predefined actions when it finds exceptions in your database, without user intervention.
• Take the actions you specify, depending upon your response to an alert message.

Use of Alerts 

• Oracle Alert facilitates the flow of information within your organization by letting you create entities called alerts to monitor your business information and to notify you of the information you want.
• You can define one of two types of alerts: an event alert or a periodic alert.

Periodic Alerts:

A periodic alert, on the other hand, checks the database for information according to a schedule you define. When you create a periodic alert, you specify the following:

• A SQL Select statement that retrieves specific database information.
• The frequency that you want the periodic alert to run the SQL statement.

To create a periodic alert, you perform the following tasks in the order listed:

• Define your periodic alert and specify its frequency
• Specify the details for your alert
• Define actions for your alert
• Create action sets containing the actions you want your alert to perform

Alert Manager->Alert->Define

Application: Enter Application Name

Name: Name of the alert(Upto 50 characters)

Description: Description about alert (Up to 240 Characters)

Select Frequency for Periodic alert

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. If you want your alert checked on the last day of the month, enter the number 31. Oracle Alert checks the alert on the 28th, 29th, 30th, or 31st, whichever is the actual last day of the month.

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.

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.

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.

Depending on the frequency you choose in the previous step, the Start Time and End Time fields become enabled. Enter 24–hour clock time values in these fields to specify when to start and/or end the periodic alert.

Check Interval: You may also specify the number of times within a 24–hour period that Oracle Alert checks your alert. Enter 24–hour clock time values in the Start Time, End Time and Check Interval fields.

Keep Days: 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.

End Date: Specify a value in the End Date field if you want to disable your alert by a certain date. Note that you can enter a date in this field only if the alert is enabled. 

Last Checked: In the Last Checked field, Oracle Alert displays the last date that this exception condition was checked, if it has been checked.

Select Statement: Enter select statement to retrieve data.

• Select statement must include an INTO clause.
• Colon(:) is used to identify inputs
• Ampersand (&) is used to identify outputs

Import: Using Import button a select statement can be imported from operating system. 

Export: Using export button a select statement can be exported

Verify: Verify to parse your Select statement and display the result in a Note window.

Run: Run to execute the Select statement in one of your application's Oracle IDs, and display the number of rows returned in a Note window.

Event Alert:

An event alert immediately notifies you of activity in your database as it occurs. When you create an event alert, you specify the following

• A database event that you want to monitor, that is, an insert and/or an update to a specific database table.
• A SQL Select statement that retrieves specific database information as a result of the database event. 

To create an event alert, you perform the following tasks in the order listed:

• Define the database events that will trigger your alert
• Specify the details for your alert
• Define actions for your alert
• Create action sets containing the actions you want your alert to perform

 Alert Manager->Alert->Define

Application: Specify the name of the application and the database table that you want Oracle Alert to monitor.

 

Table: Enter table Name

• Check After Insert and/or After Update if you want to run your event alert when an application user inserts and/or updates a row in the database table.
• Define actions for your alert
• Create action sets containing the actions you want your alert to perform

The Alert Details window that appears contains three tabbed regions: Installations, Inputs, and Outputs.

Inputs: In the Inputs tabbed region, Oracle Alert automatically displays the inputs used in your Select statement.

• Description: Enter description
• Data Type: Specify Data type (Character, Number or date)
• Default Values: Enter Default values up to 240 characters

 

Outputs: In the Outputs tabbed region, Oracle Alert automatically displays the outputs used in your alert Select statement without the ampersand (&) and any numbering format.

               In the Inputs tabbed region, Oracle Alert automatically displays the inputs used in your Select statement.

Max Length: You can specify the maximum number of output characters you want to display in your detail or summary message actions.

Number Format: If your output value is numeric, enter the SQL*Plus format mask

Check for Duplicates: You can also check the Check for Duplicates check box to customize the combination of outputs you want to verify as a possible duplicate exception each time the alert is checked.

• Description: Enter description
• Data Type: Specify Data type (Character, Number or date)
• Default Values: Enter Default values up to 240 characters

Alert Actions

There are four types of actions you can create: 

• Message actions
• Concurrent program actions
• Operating script actions
• SQL statement script actions

Action Name: Enter Name upto 80 Characters

Action Level: Select level of action

• Detail: Performs once for each individual exception found
• Summary: Performs once for all exceptions found
• No Exception: action performs when no exceptions are found

Click on Action details to define action details

Action Details->Define Message

• List: Enter the distribution list. If you enter a distribution list name, Oracle Alert displays all electronic mail IDs on the list in the To field, and does not let you change any values in the To, Cc, Bcc, Print For, or Printer fields.
• Reply To: You can specify a value in the Reply To field that overrides the default Oracle Alert mail account that appears in the Reply To prompt of message actions.
• If the distribution list is not specified Enter the valid email id’s in To, Cc, Bcc fields.
• If you list more than one recipient in any of these recipient fields, separate each recipient by a space, or a comma, or a combination of the two.
• Print For User: Enter in the Print For User field, the names, or alert outputs or response variables that represent the names of the recipients for whom you want to print a copy of the message.
• Subject field: enter a brief statement that describes the message’s subject matter. You can enter up to 240 characters.
• Response set: Enter response set to receive a response
• Text or File: For detail or no exception message actions, the message text that you specify in Oracle Alert must be less than 2000 characters, while the message file that you specify must be less than 64K.
• If the distribution list is not specified Enter the valid email id’s in To, Cc, Bcc fields.
• Subject field: enter a brief statement that describes the message’s subject matter. You can enter up to 240 characters.

Action Details->Concurrent Program

• Application Name: Enter application name
• Concurrent Program Name: Enter concurrent program name to run.
• Arguments: Enter arguments in comma separated

Action Details->SQL statement script

 

• Application Name: Enter application name
• Arguments: Enter arguments in comma separated
• File or Script: specify file name resides in SQL directory or enter SQL statement that represents the file name and location.

Action Sets

• The alert actions must include in an enabled action set for Oracle Alert to perform during an alert check.
• You can define as many action sets as you want for each alert.
• Oracle Alert executes the alert Select statement once for each action set defined

• Seq: Enter sequence number
• Action set name: Enter action set name
• Suppress Duplicates: Check Suppress Duplicates if you want Oracle Alert to suppress the actions in this action set if the exception found is a duplicate that occurred during the last alert check.
• Action: Display the list of predefined actions, escalation groups, or threshold groups in the Action field to choose the members you want to include in the action set.