Previous	Next		Contents	Index	Navigation	Glossary	Library

• 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

Before you define a periodic alert, make sure you do the following:

• Define an Oracle Office account for Oracle Alert. See: Setting Up Users and the Directory Service (Oracle Office Administrator's Guide)

• Set up a connection between Oracle Alert and Oracle Office. See: Specifying Oracle Alert Options.

   To define a periodic alert:

1. Navigate to the Alerts form.

2. Enter the name of the application that owns the alert in the Application field. This application must reside in the same Oracle database as Oracle Alert.

3. Name the alert (up to 50 characters), and give it a meaningful description (up to 240 characters). See: Oracle Alert Naming Conventions.

Enter a name for the alert that is unique within the application. Use an initial character other than a pound sign (#), a colon (:), or a percentage sign (%).

4. Select Periodic in the Type field.

5. Check Enabled to enable your periodic alert.

   To set the frequency for a periodic alert:

1. Select a frequency for your periodic alert. You can choose 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. 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. 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.

You can also check all periodic alerts at any time, regardless of the frequency you assign them, using the Request Periodic Alert Check window. See: Checking a Periodic Alert.

2. 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.

3. 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.

For example, suppose you want Oracle Alert to check your alert every 2 and a half hours between the hours of 9:00 A.M. and 7:00 P.M. Enter 09:00:00 in the Start Time field, 19:00:00 in the End Time field, and 02:30:00 in the Check Interval field.

If you want Oracle Alert to check your alert just once a day, enter 00:00:00 in the Start Time field and leave End Time blank.

4. 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. See: Overview of Oracle Alert History

5. 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.

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

   To enter a SQL Select statement for your periodic alert:

1. 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. 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.

Suggestion: If you want to use an input value in an action for this alert, select the input into an output. Then you can use the output when you define actions for this alert.

When selecting number columns, Oracle Alert uses the number formats defined in your database. Optionally, you can format your number outputs as real numbers by specifying a SQL*Plus format mask in your Select statement. For each number output, simply add a pound sign (#) and format mask to your output name. For example, if you select purchase price into the output &PRICE, add "#9999.99" after &PRICE for Oracle Alert to display the value to two decimal places. Your number output looks like: &PRICE#9999.99.

Here is an example of a periodic alert Select statement that looks for users who have not changed their passwords within the number of days specified by the value in :THRESHOLD_DAYS.:

SELECT	user_name,
			password_date,
			:THRESHOLD_DAYS
INTO		&USER,
			&LASTDATE,
			&NUMDAYS
FROM		fnd_user
WHERE	sysdate = NVL(password_date,
			sysdate) + :THRESHOLD_DAYS
ORDER BY	user_name 

Note: Although Oracle Alert does not support PL/SQL statements as the alert SQL statement definition, you can create a PL/SQL packaged function that contains PL/SQL logic and enter a SQL Select statement that calls that packaged function. For example, you can enter a SQL Select statement that looks like:

SELECT package1.function1(:INPUT1, column1)
INTO &OUTPUT1
FROM table1

In this example,

package1

is the name of the PL/SQL package and

function1

is the name of user-defined PL/SQL function stored in the package.

To ensure that your PL/SQL function is callable from a SQL statement, verify that the packaged function meets the requirements listed in your PL/SQL Release 2.1 and Oracle Precompilers Release 1.6 Addendum.

Attention: The SQL Select statement must be less than 64K.

2. Rather than typing in your Select statement, you may also import a SQL Select statement from a file in your operating system. Choose Import... and specify the location and name of the file that contains the SQL Select statement you want to import into your alert definition.

If the Select statement in the file does not contain an Into clause, Oracle Alert automatically inserts an Into clause into the alert Select statement. This default Into clause contains three outputs: &OUTPUT1, &OUTPUT2, and &OUTPUT3. You may want to give these outputs meaningful names.

If the Select statement in the file contains an Into clause, Oracle Alert preserves the Into clause. However, if the Into clause contains an incorrect number of outputs (the number of outputs does not match the number of columns you are selecting), Oracle Alert inserts the default Into clause into the alert Select statement.

Attention: The file you import must be less than 64K.

   To verify the SQL statement:

1. You can verify the accuracy and effectiveness of your Select statement. Choose Verify to parse your Select statement and display the result in a Note window.

2. Choose 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.

3. Once you are satisfied with the SQL statement, save your work.

4. Navigate to the Alert Details window to complete the definition of your periodic alert. See: Specifying Alert Details.

See Also

Checking a Periodic Alert

Transferring Alert Definitions

Creating Alert Actions

Creating an Action Set for an Alert

Deleting an Alert