Oracle Periodic Alert Setup

Periodic Alert:

Periodic alert, checks the database for information according to a schedule you
define. In a periodic alert 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.

Actions that Oracle Alert to perform
once it runs the SQL statement. An action can entail sending the retrieved
information to someone in an electronic mail message, running a concurrent
program, running an operating script, or running a SQL statement script. We
include all the actions we want Oracle Alert to perform, in an action set.

Setting Up an Periodic Alert:

1.  Go to Alert

responsibility and navigate Alert > Define.

Enter the name of the application that will own
the alert
Enter a suitable Name of the alert (up to 50
characters), and give it a meaningful description (up to 240 characters)
Select a frequency for your periodic alert 
(On-Demand in this case)
          Defining SQL Query : 
 Enter a
SQL Select statement that retrieves all the data your alert needs to perform
the actions you plan to define
Your periodic alert Select statement must
include an INTO clause that contains one output for each column selected by
your Select statement
Once you finish specifying the details for your
alert definition, you need to create the actions for your alert
Do not use set operators in your Select
You can use PL/SQL functions in your Select
statement to fetch complex business logic
Click on the ‘Verify’ button to check the select statement is

on the
Run button to execute the Select
You can view all the input and output column details in
Tab. 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.

§  Identify any inputs with a colon before the name, for
example, :INPUT_NAME.

Ex., SELECT user_name,
Input parameter



sysdate = NVL(password_date,


BY user_name;

§  And Create Input in Alert details window as shown below.
§  Identify any outputs with an ampersand (&) before the
name, for example, &OUTPUT_NAME.
§  In the Installations tabbed region, specify an Oracle
ID if you want Oracle Alert to check your alert against that specific Oracle
ID. You can select only those Oracle IDs that are associated with the
application that owns your alert


After you define your alert you need to create the actions you want your alert
to perform. For that click on the




§  Enter a name (up to 80 characters) and description (up to 240
characters) for your alert action.
§  Select a level for your action: Detail, Summary, or No
Detail action:
performs once for each individual exception found
Summary action:
performs once for all exceptions found
No exception action:
performs when no exceptions are found.

Click on
Action Details tab to display the Action
Details window.


§  Select the Action Type field as Message
if you want to send emails. Other action types are: Concurrent Program,
Operating System Script and SQL Script.

§  Specify the electronic mail IDs of the recipients you want to
send your message to in the To field.

§  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

§  You can enter as many recipients as you want, up to 240

§  You can also enter alert outputs or response variables in any
of the alert detail fields. Oracle Alert automatically substitutes the
associated output value when checking the alert or the associated response
variable value when reading the response.

§  Save your changes.

Click on
Action Sets tab in the main Alert


§  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

§  Enter a Sequence number that lets you order the execution of
action sets during an alert check.

§  Give any suitable name and description.

§  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.
Click on
Action Set Details


§  Go to Members
§  Find and attach the action that is created in earlier step.
§  Save the changes.
Since it is an
On Demand periodic alert, we can run
the alert at any time we want. For that go to Request > Check and enter the
alert details. Then click on
Submit Request.

§  This will fire one concurrent program which you can view by
going through the navigation:  Request
> View
§  View the Log and Output files of the concurrent program to
find that the alert is fired successfully.
your mailbox and you should get emails that are sent from Oracle Alerts.


  • October 11, 2015 | 107 views