How to create event based scheduler jobs in oracle





Using Events to Start Jobs

This section contains:

See Also:

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 consumed by the Scheduler:

  • Events raised by your application

An application can raise an event to be consumed by the Scheduler. The Scheduler reacts to the event by starting a job. 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.

See “Starting Jobs with Events Raised by Your Application”.

  • File arrival events raised by a file watcher

You can create a file watcher—a Scheduler object introduced in Oracle Database 11g Release 2—to watch for the arrival of a file on a system. You can then configure a job to start when the file watcher detects the presence of the file. For example, a data warehouse for a chain of stores loads data from end-of-day revenue reports uploaded from the point-of-sale systems in the stores. The data warehouse load job starts each time a new end-of-day report arrives.

See “Starting a Job When a File Arrives on a System”

See Also:

Starting Jobs with Events Raised by Your 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. You can create a named 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.

To raise an event to notify the Scheduler to start a job, your application enqueues a message onto an Oracle Streams Advanced Queuing queue that was specified when setting up the job. When the job starts, it 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, by default, events that occur while the job is already running are ignored; the event gets consumed, but does not trigger another run of the job. Beginning in Oracle Database 11g Release 1, you can change this default behavior by setting the job attribute PARALLEL_INSTANCES to TRUE. In this case, an instance of the job is started for every instance of the event, and all job instances are lightweight jobs. See the SET_ATTRIBUTE procedure in Oracle Database PL/SQL Packages and Types Reference for details.

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

Table 29-5 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 when an application signals the Scheduler that inventory levels for an item have fallen to a low threshold level:

BEGIN

DBMS_SCHEDULER.CREATE_JOB (

job_name            =>  ‘process_lowinv_j1’,

program_name        =>  ‘process_lowinv_p1’,

event_condition     =>  ‘tab.user_data.event_type = ”LOW_INVENTORY”’,

queue_spec          =>  ‘inv_events_q, inv_agent1’,

enabled             =>  TRUE,

comments            =>  ‘Start an inventory replenishment job’);

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_lowinv_j1’,

program_name        =>  ‘process_lowinv_p1’,

schedule_name       =>  ‘inventory_events_schedule’,

enabled             =>  TRUE,

comments            =>  ‘Start an inventory replenishment job’);

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 = ”LOW_INVENTORY”’, ‘inv_events_q, inv_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     =>  ‘inventory_events_schedule’,

start_date        =>  SYSTIMESTAMP,

event_condition   =>  ‘tab.user_data.event_type = ”LOW_INVENTORY”’,

queue_spec        =>  ‘inv_events_q, inv_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 (‘inventory_events_schedule’, ‘event_spec’,

‘tab.user_data.event_type = ”LOW_INVENTORY”’, ‘inv_events_q, inv_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

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 ;

/

Starting a Job When a File Arrives on a System

You can configure the Scheduler to start a job when a file arrives on the local system or a remote system. The job is an event-based job, and the file arrival event is raised by a file watcher, which is a Scheduler object introduced in Oracle Database 11g Release 2.

This section contains:

About File Watchers

file watcher is a Scheduler object that defines the location, name, and other properties of a file whose arrival on a system causes the Scheduler to start a job. You create a file watcher and then create any number of event-based jobs or event schedules that reference the file watcher. When the file watcher detects the arrival of the designated file, it raises a file arrival event. The job started by the file arrival event can retrieve the event message to learn about the newly arrived file. The message contains the information required to find the file, open it, and process it.

A file watcher can watch for a file on the local system (the same host computer running Oracle Database) or a remote system. Remote systems must be running the Scheduler agent, and the agent must be registered with the database.

File watchers check for the arrival of files every 10 minutes. You can adjust this interval. See “Changing the File Arrival Detection Interval” for details.

You must have the CREATE JOB system privilege to create a file watcher in your own schema. You require the CREATE ANY JOB system privilege to create a file watcher in a schema different from your own (except the SYS schema, which is disallowed). You can grant the EXECUTE object privilege on a file watcher so that jobs in different schemas can reference it. You can also grant the ALTER object privilege on a file watcher so that another user can modify it.

To use file watchers, the database Java virtual machine (JVM) component must be installed.

Enabling File Arrival Events from Remote Systems

To receive file arrival events from a remote system, you must install the Scheduler agent on that system, and you must register the agent with the database. The remote system does not require a running Oracle Database instance to generate file arrival events.

To enable the raising of file arrival events at remote systems: 

  1. Set up the local database to run remote external jobs.

See “Setting Up the Database for Remote Jobs” for instructions.

  1. Install, configure, register, and start the Scheduler agent on the first remote system.

See “Installing, Configuring, Registering, and Starting the Scheduler Agent” for instructions.

This adds the remote host to the list of external destinations maintained on the local database.

  1. Repeat the previous step for each additional remote system.

Creating File Watchers and File Watcher Jobs

You perform the following tasks to create a file watcher and create the event-based job that starts when the designated file arrives.

Task 1   – Create a Credential

The file watcher requires a Scheduler credential object (a credential) with which to authenticate with the host operating system for access to the file. See “Credentials” for information on privileges required to create credentials.

Perform these steps:

  1. Create a credential for the operating system user that must have access to the watched-for file.
  2. BEGIN
  3. CREATE_CREDENTIAL(‘WATCH_CREDENTIAL’, ‘salesapps’, ‘sa324w1’);
  4. END;
  5. /
  6. Grant the EXECUTEobject privilege on the credential to the schema that owns the event-based job that the file watcher will start.
  7. GRANT EXECUTE ON WATCH_CREDENTIAL to DSSUSER;

Task 2   – Create a File Watcher

Perform these steps:

  1. Create the file watcher, assigning attributes as described in the CREATE_FILE_WATCHERprocedure documentation in Oracle Database PL/SQL Packages and Types Reference. You can specify wildcard parameters in the file name. A ‘?’ prefix in the DIRECTORY_PATH attribute denotes the path to the Oracle home directory. A NULL destination indicates the local host. To watch for the file on a remote host, provide a valid external destination name, which you can obtain from the view ALL_SCHEDULER_EXTERNAL_DESTS.
  2. BEGIN
  3. CREATE_FILE_WATCHER(
  4. FILE_WATCHER_NAME => ‘EOD_FILE_WATCHER’,
  5. DIRECTORY_PATH    => ‘?/eod_reports’,
  6. FILE_NAME         => ‘eod*.txt’,
  7. CREDENTIAL_NAME   => ‘WATCH_CREDENTIAL’,
  8. DESTINATION       => NULL,
  9. ENABLED           => FALSE);
  10. END;
  11. /
  12. Grant EXECUTEon the file watcher to any schema that owns an event-based job that references the file watcher.
  13. GRANT EXECUTE ON EOD_FILE_WATCHER to DSSUSER;

Task 3   – Create a Program Object with a Metadata Argument

So that your application can retrieve the file arrival event message content, which includes file name, file size, and so on, create a Scheduler program object with a metadata argument that references the event message.

Perform these steps:

  1. Create the program.
  2. BEGIN
  3. CREATE_PROGRAM(
  4. PROGRAM_NAME        => ‘DSSUSER.EOD_PROGRAM’,
  5. PROGRAM_TYPE        => ‘STORED_PROCEDURE’,
  6. PROGRAM_ACTION      => ‘EOD_PROCESSOR’,
  7. NUMBER_OF_ARGUMENTS => 1,
  8. ENABLED             => FALSE);
  9. END;
  10. /
  11. Define the metadata argument using the event_message
  12. BEGIN
  13. DEFINE_METADATA_ARGUMENT(
  14. PROGRAM_NAME => ‘DSSUSER.EOD_PROGRAM’,
  15. METADATA_ATTRIBUTE => ‘event_message’,
  16. ARGUMENT_POSITION => 1);
  17. END;
  18. /
  19. Create the stored procedure that the program invokes.

The stored procedure that processes the file arrival event must have an argument of type SYS.SCHEDULER_FILEWATCHER_RESULT, which is the data type of the event message. The position of that argument must match the position of the defined metadata argument. The procedure can then access attributes of this abstract data type to learn about the arrived file.

See Also:

Task 4   – Create an Event-Based Job That References the File Watcher

Create the event-based job as described in “Creating an Event-Based Job”, with the following exception: instead of providing a queue specification in the queue_spec attribute, provide the name of the file watcher. You would typically leave the event_condition job attribute null, but you can provide a condition if desired.

As an alternative to setting the queue_spec attribute for the job, you can create an event schedule, reference the file watcher in the queue_spec attribute of the event schedule, and reference the event schedule in the schedule_name attribute of the job.

Perform these steps to prepare the event-based job:

  1. Create the job.
  2. BEGIN
  3. CREATE_JOB(
  4. JOB_NAME        => ‘DSSUSER.EOD_JOB’,
  5. PROGRAM_NAME    => ‘DSSUSER.EOD_PROGRAM’,
  6. EVENT_CONDITION => NULL,
  7. QUEUE_SPEC      => ‘EOD_FILE_WATCHER’,
  8. AUTO_DROP       => FALSE,
  9. ENABLED         => FALSE);
  10. END;
  11. /
  12. If you want the job to run for each instance of the file arrival event, even if the job is already processing a previous event, set the parallel_instancesattribute to TRUE. With this setting, the job runs as a lightweight job so that multiple instances of the job can be started quickly. If you want to discard file watcher events that occur while the event-based job is already processing another, leave the parallel_instancesattribute FALSE (the default).
  13. BEGIN
  14. SET_ATTRIBUTE(‘DSSUSER.EOD_JOB’,’PARALLEL_INSTANCES’,TRUE);
  15. END;
  16. /

For more information about this attribute, see the SET_ATTRIBUTE description in Oracle Database PL/SQL Packages and Types Reference.

See Also:

Task 5   – Enable All Objects

Enable the file watcher, the program, and the job.

BEGIN

DBMS_SCHEDULER.ENABLE(‘DSSUSER.EOD_PROGRAM,DSSUSER.EOD_JOB,EOD_FILE_WATCHER’);

END;

/

File Arrival Example

In this example, an event-based job watches for the arrival of end-of-day sales reports onto the local host from various locations. As each report file arrives, a stored procedure captures information about the file and stores the information in a table called eod_reports. A regularly scheduled report aggregation job can then query this table, process all unprocessed files, and mark any newly processed files as processed.

It is assumed that the database user running the following code has been granted EXECUTE on the SYS.SCHEDULER_FILEWATCHER_RESULT data type.

begin

dbms_scheduler.create_credential(

credential_name => ‘watch_credential’,

username        => ‘pos1’,

password        => ‘jk4545st’);

end;

/

 

create table eod_reports (when timestamp, file_name varchar2(100),

file_size number, processed char(1));

 

create or replace procedure q_eod_report

(payload IN sys.scheduler_filewatcher_result) as

begin

insert into eod_reports values

(payload.file_timestamp,

payload.directory_path || ‘/’ || payload.actual_file_name,

payload.file_size,

‘N’);

end;

/

 

begin

dbms_scheduler.create_program(

program_name        => ‘eod_prog’,

program_type        => ‘stored_procedure’,

program_action      => ‘q_eod_report’,

number_of_arguments => 1,

enabled             => false);

dbms_scheduler.define_metadata_argument(

program_name        => ‘eod_prog’,

metadata_attribute  => ‘event_message’,

argument_position   => 1);

dbms_scheduler.enable(‘eod_prog’);

end;

/

 

begin

dbms_scheduler.create_file_watcher(

file_watcher_name => ‘eod_reports_watcher’,

directory_path    => ‘?/eod_reports’,

file_name         => ‘eod*.txt’,

credential_name   => ‘watch_credential’,

destination       => null,

enabled           => false);

end;

/

 

begin

dbms_scheduler.create_job(

job_name        => ‘eod_job’,

program_name    => ‘eod_prog’,

event_condition => ‘tab.user_data.file_size > 10’,

queue_spec      => ‘eod_reports_watcher’,

auto_drop       => false,

enabled         => false);

dbms_scheduler.set_attribute(‘eod_job’,’parallel_instances’,true);

end;

/

 

exec dbms_scheduler.enable(‘eod_reports_watcher,eod_job’);

Managing File Watchers

The DBMS_SCHEDULER PL/SQL package provides procedures for enabling, disabling, dropping, and setting attributes for file watchers.

The section contains:

See Also:

Oracle Database PL/SQL Packages and Types Reference for information about the DBMS_SCHEDULER PL/SQL package

Enabling File Watchers

If a file watcher is disabled, use DBMS_SCHEDULER.ENABLE to enable it, as shown in Task 5, “- Enable All Objects”.

You can enable a file watcher only if all of its attributes are set to legal values and the file watcher owner has EXECUTE privileges on the specified credential.

Altering File Watchers

Use the DBMS_SCHEDULER.SET_ATTRIBUTE and DBMS_SCHEDULER.SET_ATTRIBUTE_NULL package procedures to modify the attributes of a file watcher. See the CREATE_FILE_WATCHER procedure description for information about file watcher attributes.

Disabling and Dropping File Watchers

Use DBMS_SCHEDULER.DISABLE to disable a file watcher and DBMS_SCHEDULER.DROP_FILE_WATCHER to drop a file watcher. You cannot disable or drop a file watcher if there are jobs that depend on it. To force a disable or drop operation in this case, set the FORCE attribute to TRUE. If you force disabling or dropping a file watcher, jobs that depend on it become disabled.

Changing the File Arrival Detection Interval

File watchers check for the arrival of files every ten minutes by default. You can change this interval.

To change the file arrival detection interval: 

  1. Connect to the database as the SYS user.
  2. Change the REPEAT_INTERVAL attribute of the predefined schedule SYS.FILE_WATCHER_SCHEDULE. Use any valid calendaring syntax.

The following example changes the file arrival detection frequency to every two minutes.

BEGIN

DBMS_SCHEDULER.SET_ATTRIBUTE(‘FILE_WATCHER_SCHEDULE’, ‘REPEAT_INTERVAL’,

‘FREQ=MINUTELY;INTERVAL=2’);

END;

/

Viewing File Watcher Information

You can view information about file watchers by querying the views *_SCHEDULER_FILE_WATCHERS.

SELECT file_watcher_name, destination, directory_path, file_name, credential_name

FROM dba_scheduler_file_watchers;

 

FILE_WATCHER_NAME    DESTINATION          DIRECTORY_PATH       FILE_NAME  CREDENTIAL_NAME

——————– ——————– ——————– ———- —————-

MYFW                 dsshost.example.com  /tmp                 abc        MYFW_CRED

EOD_FILE_WATCHER                          ?/eod_reports        eod*.txt   WATCH_CREDENTIAL

 

Author: admin