Oracle Alerts

…For the beginners only…

By Ratan Banerjee

Contents

• What is Oracle Alert?

• What are the difference types of Alert?

• How to set up an Alert? (Different components of Alert)

• How Alert Works?

• Limitations of Oracle Alert?

What is Oracle Alert?

• Complete exception control solution

• Easy electronic emailing

• Execution of process

• Schedule in regular interval

• Replacement of Database Trigger

• Take predefined actions

• Take actions depends on user response.

Alerts

• Periodic Alert– Variable frequency

• Event Alert– All possible events

• Customized Alert Frequency• Customized Alert Actions

(Details / Summary action, No exception, History, Duplicate Checking

• Action Escalation• Response Processing

Creating a Periodic Alert

• Set Frequency (On Demand, On day of …)

• Set time / interval

• Enter / import select statement

• Verify / Run select statement

Creating a Periodic Alert

Creating an Event Alert

• Application & Table name

• After Insert / Update on Table

• Enter / import select statement

• Verify / Run select statement

Caution : No event alert on FND_CONCURRENT_REQUESTS

Creating an Event Alert

Sample Select Statement

SELECT user_name, password_date, :THRESHOLD_DAYSINTO &USER, &LASTDATE, &NUMDAYSFROM fnd_userWHERE sysdate = NVL(password_date,sysdate) +

:THRESHOLD_DAYSORDER BY user_name

Specifying Alert Details (Input)

Reserved Inputs :- ROWID, MAILID, ORG_ID, DATE_LAST_CHECKED.

Specifying Alert Details (Output)

Specifying Alert Details (Installation)

Alert Actions

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

Create Alert Action

Create Action Detail (email)

Define a Distribution List

Create Action Details (submit a concurrent Program)

Create Action Details (Operating System Script)

Create Action Details (SQL Statement Script)

Create a Action Set

Alert History & Escalation

• Keep No of days

• Check for Duplicates

• Tools Escalation Group

• Include Escalation Group to Action Set

Review Alert History

Response Processing Alert

• Define an alert• Create an alert message action that

solicits a response• Create response actions• Define an action set• Create a response set• Define possible responses• Attach the response set to the alert

message action

Response Processing Alert(Response Set)

Response Processing Alert(Response Variables)

Response Processing Alert(Valid Responses)

Response Processing Alert(Invalid Responses)

Response Processing Alert(No Responses)

How Alert Checks

• Periodic Alert Scheduler

• Check Periodic Alert

• Check Event Alert

• Response Processor

Manually submit Periodic alert

Manually submit Event Alert

• Update your table with specific action.

?

Setting up Oracle Alert

Will be covered in next session…