Creating a Periodic Alert (Oracle Alert)

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

This section focuses on the first task of defining a periodic alert and its frequency and divides the task into smaller sub-tasks.

To define a periodic alert:

To set the frequency for a periodic alert:

To enter a SQL Select statement for your periodic alert:

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 32K.

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.

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

Creating a Periodic Alert

To define a periodic alert:

To set the frequency for a periodic alert:

To enter a SQL Select statement for your periodic alert:

To verify the SQL statement: