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. The value of the implicit input :DATE_LAST_CHECKED is character. 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 tabbed 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.

Suppose your Select statement contains the following lines:

 .
	.
	WHERE ...
	AND creation_date > 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.