Previous	Next		Contents	Index	Navigation	Glossary	Library

• 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

Before you define an event 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 an event alert:

1. Navigate to the Alerts form.

2. In the Application field, use the list of values to choose the name of the application that owns the alert. 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. In the Type field, choose Event.

5. Check Enabled to enable your event alert.

   To specify an event table:

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

Although the application you enter here need not be the same application that owns the alert, both applications must reside in the same Oracle database and the application that owns the alert has to have Select privileges on the tables listed in the alert Select statement.

Attention: You cannot use a view as the event table for your alert.

Attention: Do not define an event alert on the table FND_CONCURRENT_REQUESTS. Oracle Alert submits a concurrent request to the concurrent manager when an event alert is triggered by an insert or update to an event table. For concurrent processing to occur, every submitted concurrent request automatically gets inserted as a row in the FND_CONCURRENT_REQUESTS table. If you define an event alert on this table, you create a situation where the event alert will cause an exception to occur recursively. See: How Oracle Alert Checks Alerts.

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

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

4. 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 event alert:

1. Enter a SQL Select statement in the Select Statement field that retrieves all the data your alert needs to perform the actions you plan to define.

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

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

3. Your event alert Select statement must also include a join from the table where the event transaction occurs to an implicit input we call :ROWID. Oracle Alert supplies the values for implicit inputs. Oracle Alert uses :ROWID to pinpoint the inserted or updated record in the table that caused the event.

Attention: You must include :ROWID in your event alert's Select statement Where clause, or Oracle Alert will not be able to locate the row where the event transaction occurred.

Oracle Alert also automatically supplies the value for the implicit input :MAILID when triggering an event alert. The value for :MAILID is the electronic mail address of the application user who triggered the event. This value comes from the E-Mail field in the Users form of the System Administrator responsibility. If the E-Mail field is null, Oracle Alert uses the operating system user ID or the Application username of the user who triggered the event, depending on the value set for the Default User Mail Account option in the Oracle Alert Options form. If you want to send an alert message to that user, or use that user's email username in an action, simply select :MAILID into an output. Oracle Alert does not require that you use :MAILID in your Select statement.

Attention: The inputs :MAILID and :ROWID are reserved terms that you should not use for anything other than their intended purposes.

Here is an example of an event alert Select statement that reports the creation of new users:

SELECT	user_name,
			:MAILID
INTO		&NEWUSER,
			&USER
FROM		fnd_user
WHERE	rowid = :ROWID 

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.

4. 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 event alert. See: Specifying Alert Details.

See Also

Creating an Action Set for an Alert

Deleting an Alert

Transferring Alert Definitions