Skip Headers
Oracle® Database Administrator's Guide
11g Release 1 (11.1)

Part Number B28310-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

Using Events

The Scheduler works with two kinds of events:

This section provides details on how to work with both kinds of events, and includes the following topics:

See Also:

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-7 summarizes the one administration task involving events raised by the Scheduler.

Table 27-7 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. For a list of bit flags, see the discussion of DBMS_SCHEDULER.SET_ATTRIBUTE in Oracle Database PL/SQL Packages and Types Reference.

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. (Event types are defined as constants in the package.)

  • job_failed (bit flag value = 4)

  • job_sch_lim_reached (bit flag value = 64)

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

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:

  1. Log in to the database as the SYS user or as a user with the MANAGE ANY QUEUE privilege.

  2. Subscribe to the queue using a new or existing agent.

  3. 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 SET_ATTRIBUTE procedure 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.
spare1spare8 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 card-swipe 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 = ''CARD_SWIPE'' 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-8 describes common administration tasks involving events raised by an application (and consumed by the Scheduler) and the procedures associated with them.

Table 27-8 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 Event-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 whenever someone swipes a badge to enter a data center:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name            =>  'my_job',
   program_name        =>  'my_program',
   event_condition     =>  'tab.user_data.event_type = ''CARD_SWIPE''',
   queue_spec          =>  'entry_events_q, entry_agent1',
   enabled             =>  TRUE,
   comments            =>  'Start job when someone swipes a badge');
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            =>  'my_job',
   program_name        =>  'my_program',
   schedule_name       =>  'entry_events_schedule',
   enabled             =>  TRUE,
   comments            =>  'Start job when someone swipes a badge');
END;
/

See "Creating an Event Schedule" for more information.

Altering an Event-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 = ''BAD_BADGE''', 'entry_events_q, entry_agent1');
END;
/

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

Creating an Event 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     =>  'entry_events_schedule',
   start_date        =>  SYSTIMESTAMP,
   event_condition   =>  'tab.user_data.event_type = ''CARD_SWIPE''', 
   queue_spec        =>  'entry_events_q, entry_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 Event 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 ('entry_events_schedule', 'event_spec',
   'tab.user_data.event_type = ''BAD_BADGE''', 'entry_events_q, entry_agent1');
END;
/

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

Passing Event Messages 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
  -- do other work
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 ;
/