Creating Self-Referencing Alerts (Oracle Alert Help)


	Previous	Next		Contents	Index	Navigation	Glossary	Library

Creating Self-Referencing Alerts

You can create an alert that checks your database for only those exceptions that are new since the last time the alert was checked - without saving history. A "self-referencing" alert uses its own "date-last-checked" value as the starting point when it checks your database for new exceptions.

A self-referencing alert compares the value of a date and time column with the value of an implicit input called :DATE_LAST_CHECKED in the WHERE clause of the alert Select statement. An implicit input is an input that Oracle Alert automatically provides the value for. When checking a self-referencing alert, Oracle Alert automatically provides the date and time as the value for :DATE_LAST_CHECKED.

When Oracle Alert checks your alert, it uses the date and time information in the column you specify in your Where clause to determine whether the exceptions returned by the alert Select statement were created or updated since the date the alert was last checked. Your self-referencing alert returns only those exceptions that occurred more recently than the date and time value of :DATE_LAST_CHECKED. Then, Oracle Alert updates the value of :DATE_LAST_CHECKED with the date and time information from the current alert check. See: How Oracle Alert Checks Alerts.

Oracle Alert never displays :DATE_LAST_CHECKED in the Inputs alternative region of either the Alert Details window or the Action Sets window in the Alerts form.

To create a self-referencing alert:

You create a self-referencing alert the same way you would create any other alert, but you tailor your alert Select statement by adding to the Where clause a comparison between the values in the implicit input :DATE_LAST_CHECKED and the column that contains the date and time information that you want your alert to reference.

Example

Suppose your Select statement contains the following lines:

	.
	.
	WHERE ...
	AND TO_DATE(creation_date, 'DD-MON-YYYY HH24:MI:SS')	
		> TO_DATE(:DATE_LAST_CHECKED, 'DD-MON-YYYY 						HH24:MI:SS')

In this example, Oracle Alert compares the date and time information in the column CREATION_DATE with the date and time information in :DATE_LAST_CHECKED to determine which exceptions are new.

Attention: The format of the date and time information in the column in the Where clause must match the format of the date and time information in :DATE_LAST_CHECKED. The format of the date and time information stored in the DATE_LAST_CHECKED column is:

		DD-MON-YYYY HH24:MI:SS

Attention: If you choose to use the TO_DATE function to modify date and time information, you must use it to modify both the column your alert references and the DATE_LAST_CHECKED column.

Creating Self-Referencing Alerts

To create a self-referencing alert:

See Also