Using Events

This section contains:

About Events
Using Events Raised by the Scheduler
Using Events Raised by an Application

See Also:

"Examples of Creating Jobs and Schedules Based on Events"
"Using Chains" for information on how to use events with chains to achieve precise control over process flow

About Events

An event is a message sent by one application or system process to another to indicate that some action or occurrence has been detected. An event is raised (sent) by one application or process, and consumed (received) by one or more applications or processes.

There are two kinds of events in the Scheduler:

Events raised by the Scheduler

The Scheduler can raise an event to indicate state changes that occur within the Scheduler itself. For example, the Scheduler can raise an event when a job starts, when a job completes, when a job exceeds its allotted run time, and so on. The consumer of the event is an application that takes some action in response to the event.

For example, if due to a high system load, a job is still not started 30 minutes after the scheduled start time, the Scheduler can raise an event that causes a handler application to send a notification e-mail to the database administrator.
Events raised by an application

An application can raise an event to be consumed by the Scheduler. The Scheduler reacts to the event by starting a job. You can create a schedule that references an event instead of containing date, time, and recurrence information. If a job is given such a schedule (an event schedule), the job runs when the event is raised.

For example, when an inventory tracking system notices that the inventory has gone below a certain threshold, it can raise an event that starts an inventory replenishment job.

The Scheduler uses Oracle Streams Advanced Queuing to raise and consume events. When raising a job state change event, the Scheduler enqueues a message onto a default event queue. Applications subscribe to this queue, dequeue event messages, and take appropriate action. When raising an event to notify the Scheduler to start a job, an application enqueues a message onto a queue that was specified when setting up the job.

See Also:

Oracle Streams Advanced Queuing User's Guide for more information on Advanced Queuing

Using Events Raised by the Scheduler

You can set up a job so that the Scheduler raises an event when the job changes state. You do so by setting the raise_events job attribute. Because you cannot set this attribute with the CREATE_JOB procedure, you must first create the job and then alter the job with the SET_ATTRIBUTE procedure.

By default, until you alter a job with SET_ATTRIBUTE, a job does not raise any state change events.

Table 27-8 summarizes the one administration task involving events raised by the Scheduler.

Table 27-8 Event Tasks and Their Procedures for Events Raised by the Scheduler

Task	Procedure	Privilege Needed
Altering a Job to Raise Events	`SET_ATTRIBUTE`	`CREATE` `ANY` `JOB` or ownership of job being altered or `ALTER` privileges on the job

After you enable job state change events for a job, the Scheduler raises these events by enqueuing messages onto the Scheduler event queue SYS.SCHEDULER$_EVENT_QUEUE. This queue is a secure queue, so depending on your application, you may have to configure the queue to enable certain users to perform operations on it. See Oracle Streams Concepts and Administration for information on secure queues.

To prevent unlimited growth of the Scheduler event queue, events raised by the Scheduler expire in 24 hours by default. (Expired events are deleted from the queue.) You can change this expiry time by setting the event_expiry_time Scheduler attribute with the SET_SCHEDULER_ATTRIBUTE procedure. See Oracle Database PL/SQL Packages and Types Reference for more information.

Altering a Job to Raise Events

To enable job state change events for a job, you use the SET_ATTRIBUTE procedure to turn on bit flags in the raise_events job attribute. Each bit flag represents a different job state to raise an event for. For example, turning on the least significant bit enables job started events to be raised. To enable multiple state change event types in one call, you add the desired bit flag values together and supply the result as an argument to SET_ATTRIBUTE.

The following example enables multiple state change events for job dw_reports. It enables the following event types, both of which indicate some kind of error.

JOB_FAILED
JOB_SCH_LIM_REACHED

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE('dw_reports', 'raise_events',
   DBMS_SCHEDULER.JOB_FAILED + DBMS_SCHEDULER.JOB_SCH_LIM_REACHED);
END;
/

See Also:

The discussion of DBMS_SCHEDULER.SET_ATTRIBUTE in Oracle Database PL/SQL Packages and Types Reference for the names and values of job state bit flags

Consuming Scheduler-Raised Events with your Application

To consume Scheduler events, your application must subscribe to the Scheduler event queue SYS.SCHEDULER$_EVENT_QUEUE. This queue is a secure queue and is owned by SYS. To create a subscription to this queue for a user, do the following:

Log in to the database as the SYS user or as a user with the MANAGE ANY QUEUE privilege.
Subscribe to the queue using a new or existing agent.
Run the package procedure DBMS_AQADM.ENABLE_DB_ACCESS as follows:
```
DBMS_AQADM.ENABLE_DB_ACCESS(agent_name, db_username);
```
where agent_name references the agent that you used to subscribe to the events queue, and db_username is the user for whom you want to create a subscription.

There is no need to grant dequeue privileges to the user. The dequeue privilege is granted on the Scheduler event queue to PUBLIC.

As an alternative, the user can subscribe to the Scheduler event queue using the ADD_EVENT_QUEUE_SUBSCRIBER procedure, as shown in the following example:

DBMS_SCHEDULER.ADD_EVENT_QUEUE_SUBSCRIBER(subscriber_name);

where subscriber_name is the name of the Oracle Streams Advanced Queuing (AQ) agent to be used to subscribe to the Scheduler event queue. (If it is NULL, an agent is created whose name is the user name of the calling user.) This call both creates a subscription to the Scheduler event queue and grants the user permission to dequeue using the designated agent. The subscription is rule-based. The rule permits the user to see only events raised by jobs that the user owns, and filters out all other messages. After the subscription is in place, the user can either poll for messages at regular intervals or register with AQ for notification.

See Oracle Streams Advanced Queuing User's Guide for more information.

Scheduler Event Queue

The Scheduler event queue SYS.SCHEDULER$_EVENT_QUEUE is of type scheduler$_event_info. The following are details on this type.

create or replace type sys.scheduler$_event_info as object
(
  event_type         VARCHAR2(4000),
  object_owner       VARCHAR2(4000),
  object_name        VARCHAR2(4000),
  event_timestamp    TIMESTAMP WITH TIME ZONE,
  error_code         NUMBER,
  error_msg          VARCHAR2(4000),
  event_status       NUMBER,
  log_id             NUMBER,
  run_count          NUMBER,
  failure_count      NUMBER,
  retry_count        NUMBER,
  spare1             NUMBER,
  spare2             NUMBER,
  spare3             VARCHAR2(4000),
  spare4             VARCHAR2(4000),
  spare5             TIMESTAMP WITH TIME ZONE,
  spare6             TIMESTAMP WITH TIME ZONE,
  spare7             RAW(2000),
  spare8             RAW(2000),
);

Attribute	Description
`event_type`	One of "`JOB_STARTED`", "`JOB_SUCCEEDED`", "`JOB_FAILED`", "`JOB_BROKEN`", "`JOB_COMPLETED`", "`JOB_STOPPED`", "`JOB_SCH_LIM_REACHED`", "`JOB_DISABLED`", "`JOB_CHAIN_STALLED`", "`JOB_OVER_MAX_DUR`". For descriptions of these event types, see the Constants section for the `DBMS_SCHEDULER` package in Oracle Database PL/SQL Packages and Types Reference.
`object_owner`	Owner of the job that raised the event.
`object_name`	Name of the job that raised the event.
`event_timestamp`	Time at which the event occurred.
`error_code`	Applicable only when an error is thrown during job execution. Contains the top-level error code.
`error_msg`	Applicable only when an error is thrown during job execution. Contains the entire error stack.
`event_status`	Adds further qualification to the event type. If `event_type` is "`JOB_STARTED`," a status of 1 indicates that it is a normal start, and a status of 2 indicates that it is a retry. If `event_type` is "`JOB_FAILED`," a status of 4 indicates that it was a failure due to an error that was thrown during job execution, and a status of 8 indicates that it was an abnormal termination of some kind. If `event_type` is "`JOB_STOPPED`," a status of 16 indicates that it was a normal stop, and a status of 32 indicates that it was a stop with the `FORCE` option set to `TRUE`.
`log_id`	Points to the ID in the scheduler job log from which additional information can be obtained. Note that there need not always be a log entry corresponding to an event. In such cases, `log_id` is `NULL`.
`run_count`	Run count for the job when the event was raised.
`failure_count`	Failure count for the job when the event was raised.
`retry_count`	Retry count for the job when the event was raised.
`spare1` – `spare8`	Currently not implemented

Using Events Raised by an Application

Your application can raise an event to notify the Scheduler to start a job. A job started in this way is referred to as an event-based job. The job can optionally retrieve the message content of the event.

To create an event-based job, you must set these two additional attributes:

queue_spec

A queue specification that includes the name of the queue where your application enqueues messages to raise job start events, or in the case of a secure queue, the queue name followed by a comma and the agent name.
event_condition

A conditional expression based on message properties that must evaluate to TRUE for the message to start the job. The expression must have the syntax of an Oracle Streams Advanced Queuing rule. Accordingly, you can include user data properties in the expression, provided that the message payload is an object type, and that you prefix object attributes in the expression with tab.user_data.

For more information on rules, see the DBMS_AQADM.ADD_SUBSCRIBER procedure in Oracle Database PL/SQL Packages and Types Reference.

The following example sets event_condition to select only low-inventory events that occur after midnight and before 9:00 a.m. Assume that the message payload is an object with two attributes called event_type and event_timestamp.
```
event_condition = 'tab.user_data.event_type = ''LOW_INVENTORY'' and 
extract hour from tab.user_data.event_timestamp < 9'
```

You can specify queue_spec and event_condition as inline job attributes, or you can create an event schedule with these attributes and point to this schedule from the job.

Note:

The Scheduler runs the event-based job for each occurrence of an event that matches event_condition. However, events that occur while the job is already running are ignored; the event gets consumed, but does not trigger another run of the job.

Table 27-9 describes common administration tasks involving events raised by an application (and consumed by the Scheduler) and the procedures associated with them.

Table 27-9 Event Tasks and Their Procedures for Events Raised by an Application

Task	Procedure	Privilege Needed
Creating an Event-Based Job	`CREATE_JOB`	`CREATE` `JOB` or `CREATE` `ANY` `JOB`
Altering an Event-Based Job	`SET_ATTRIBUTE`	`CREATE` `ANY` `JOB` or ownership of the job being altered or ALTER privileges on the job
Creating an Event Schedule	`CREATE_EVENT_SCHEDULE`	`CREATE` `JOB` or `CREATE` `ANY` `JOB`
Altering an Event Schedule	`SET_ATTRIBUTE`	`CREATE` `ANY` `JOB` or ownership of the schedule being altered or ALTER privileges on the schedule

See Also:

Oracle Streams Advanced Queuing User's Guide for information on how to create queues and enqueue messages.

Creating an Eve nt-Based Job

You use the CREATE_JOB procedure or Enterprise Manager to create an event-based job. The job can include event information inline as job attributes or can specify event information by pointing to an event schedule.

Like jobs based on time schedules, event-based jobs are not auto-dropped unless the job end date passes, max_runs is reached, or the maximum number of failures (max_failures) is reached.

Specifying Event Information as Job Attributes

To specify event information as job attributes, you use an alternate syntax of CREATE_JOB that includes the queue_spec and event_condition attributes.

The following example creates a job that starts when an application signals the Scheduler that a file has arrived on a server:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name            =>  'process_file_j1',
   program_name        =>  'process_file_p1',
   event_condition     =>  'tab.user_data.event_type = ''FILE_ARRIVAL''',
   queue_spec          =>  'file_events_q, file_agent1',
   enabled             =>  TRUE,
   comments            =>  'Start job when a file arrives on the system');
END;
/

See Oracle Database PL/SQL Packages and Types Reference for more information regarding the CREATE_JOB procedure.

Specifying Event Information in an Event Schedule

To specify event information with an event schedule, you set the job's schedule_name attribute to the name of an event schedule, as shown in the following example:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name            =>  'process_file_j1',
   program_name        =>  'process_file_p1',
   schedule_name       =>  'file_events_schedule',
   enabled             =>  TRUE,
   comments            =>  'Start job when a file arrives on the system');
END;
/

See "Creating an Event Schedule" for more information.

Altering an Ev ent-Based Job

You alter an event-based job by using the SET_ATTRIBUTE procedure. For jobs that specify the event inline, you cannot set the queue_spec and event_condition attributes individually with SET_ATTRIBUTE. Instead, you must set an attribute called event_spec, and pass an event condition and queue specification as the third and fourth arguments, respectively, to SET_ATTRIBUTE.

The following is an example of using the event_spec attribute:

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE ('my_job', 'event_spec', 
   'tab.user_data.event_type = ''FILE_ARRIVAL''', 'file_events_q, file_agent1');
END;
/

See Oracle Database PL/SQL Packages and Types Reference for more information regarding the SET_ATTRIBUTE procedure.

Creating an Ev ent Schedule

You can create a schedule that is based on an event. You can then reuse the schedule for multiple jobs. To do so, use the CREATE_EVENT_SCHEDULE procedure, or use Enterprise Manager. The following is an example of creating an event schedule:

BEGIN
DBMS_SCHEDULER.CREATE_EVENT_SCHEDULE (
   schedule_name     =>  'file_events_schedule',
   start_date        =>  SYSTIMESTAMP,
   event_condition   =>  'tab.user_data.event_type = ''FILE_ARRIVAL''', 
   queue_spec        =>  'file_events_q, file_agent1');
END;
/

You can drop an event schedule using the DROP_SCHEDULE procedure. See Oracle Database PL/SQL Packages and Types Reference for more information on CREATE_EVENT_SCHEDULE.

Altering an Ev ent Schedule

You alter the event information in an event schedule in the same way that you alter event information in a job. For more information, see "Altering an Event-Based Job".

The following example demonstrates how to use the SET_ATTRIBUTE procedure and the event_spec attribute to alter event information in an event schedule.

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE ('file_events_schedule', 'event_spec',
   'tab.user_data.event_type = ''FILE_ARRIVAL''', 'file_events_q, file_agent1');
END;
/

See Oracle Database PL/SQL Packages and Types Reference for more information regarding the SET_ATTRIBUTE procedure.

Passing Event Mess ages into an Event-Based Job

Through a metadata argument, the Scheduler can pass to an event-based job the message content of the event that started the job. The following rules apply:

The job must use a named program of type STORED_PROCEDURE.
One of the named program's arguments must be a metadata argument with metadata_attribute set to EVENT_MESSAGE.
The stored procedure that implements the program must have an argument at the position corresponding to the named program's metadata argument. The argument type must be the data type of the queue where your application queues the job-start event.

If you use the RUN_JOB procedure to manually run a job that has an EVENT_MESSAGE metadata argument, the value passed to that argument is NULL.

The following example shows how to construct an event-based job that can receive the event message content:

create or replace procedure my_stored_proc (event_msg IN event_queue_type)
as
begin
  -- retrieve and process message body
end;
/ 
 
begin
  dbms_scheduler.create_program (
      program_name => 'my_prog',
      program_action=> 'my_stored_proc',
      program_type => 'STORED_PROCEDURE',
      number_of_arguments => 1,
      enabled => FALSE) ;
 
  dbms_scheduler.define_metadata_argument (
      program_name => 'my_prog',
      argument_position => 1 ,
      metadata_attribute => 'EVENT_MESSAGE') ;
 
  dbms_scheduler.enable ('my_prog');
exception
  when others then raise ;
end ;
/
 
begin
  dbms_scheduler.create_job (
     job_name => 'my_evt_job' ,
     program_name => 'my_prog',
     schedule_name => 'my_evt_sch',
     enabled => true,
     auto_Drop => false) ;
exception
  when others then raise ;
end ;
/

Using Events

About Events

Using Events Raised by the Scheduler

Altering a Job to Raise Events

Consuming Scheduler-Raised Events with your Application

Using Events Raised by an Application

Creating an Event-Based Job

Specifying Event Information as Job Attributes

Specifying Event Information in an Event Schedule

Altering an Event-Based Job

Creating an Event Schedule

Altering an Event Schedule

Passing Event Messages into an Event-Based Job

Creating an Eve nt-Based Job

Altering an Ev ent-Based Job

Creating an Ev ent Schedule

Altering an Ev ent Schedule

Passing Event Mess ages into an Event-Based Job