Using Events

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),
);

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.

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;
/

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;
/

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 ;
/