Using Events to Start Jobs
This section contains:
- About Events
- Starting Jobs with Events Raised by Your Application
- Starting a Job When a File Arrives on a System
See Also:
- “Examples of Creating Jobs and Schedules Based on Events”
- “Creating and Managing Job 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 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:
- Oracle Streams Advanced Queuing User’s Guidefor more information on Advanced Queuing
- “Monitoring Job State with Events Raised by the Scheduler”for information about how your application can consume job state change events raised by the Scheduler
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
- Enabling File Arrival Events from Remote Systems
- Creating File Watchers and File Watcher Jobs
- File Arrival Example
- Managing File Watchers
- Viewing File Watcher Information
About File Watchers
A 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:
- Set up the local database to run remote external jobs.
See “Setting Up the Database for Remote Jobs” for instructions.
- 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.
- 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:
- Create a credential for the operating system user that must have access to the watched-for file.
- BEGIN
- CREATE_CREDENTIAL(‘WATCH_CREDENTIAL’, ‘salesapps’, ‘sa324w1’);
- END;
- /
- Grant the EXECUTEobject privilege on the credential to the schema that owns the event-based job that the file watcher will start.
- GRANT EXECUTE ON WATCH_CREDENTIAL to DSSUSER;
Task 2 – Create a File Watcher
Perform these steps:
- 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.
- BEGIN
- CREATE_FILE_WATCHER(
- FILE_WATCHER_NAME => ‘EOD_FILE_WATCHER’,
- DIRECTORY_PATH => ‘?/eod_reports’,
- FILE_NAME => ‘eod*.txt’,
- CREDENTIAL_NAME => ‘WATCH_CREDENTIAL’,
- DESTINATION => NULL,
- ENABLED => FALSE);
- END;
- /
- Grant EXECUTEon the file watcher to any schema that owns an event-based job that references the file watcher.
- 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:
- Create the program.
- BEGIN
- CREATE_PROGRAM(
- PROGRAM_NAME => ‘DSSUSER.EOD_PROGRAM’,
- PROGRAM_TYPE => ‘STORED_PROCEDURE’,
- PROGRAM_ACTION => ‘EOD_PROCESSOR’,
- NUMBER_OF_ARGUMENTS => 1,
- ENABLED => FALSE);
- END;
- /
- Define the metadata argument using the event_message
- BEGIN
- DEFINE_METADATA_ARGUMENT(
- PROGRAM_NAME => ‘DSSUSER.EOD_PROGRAM’,
- METADATA_ATTRIBUTE => ‘event_message’,
- ARGUMENT_POSITION => 1);
- END;
- /
- 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:
- Oracle Database PL/SQL Packages and Types Referencefor a description of the DEFINE_METADATA_ARGUMENT procedure
- Oracle Database PL/SQL Packages and Types Referencefor a description of the SCHEDULER_FILEWATCHER_RESULT type
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:
- Create the job.
- BEGIN
- CREATE_JOB(
- JOB_NAME => ‘DSSUSER.EOD_JOB’,
- PROGRAM_NAME => ‘DSSUSER.EOD_PROGRAM’,
- EVENT_CONDITION => NULL,
- QUEUE_SPEC => ‘EOD_FILE_WATCHER’,
- AUTO_DROP => FALSE,
- ENABLED => FALSE);
- END;
- /
- 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).
- BEGIN
- SET_ATTRIBUTE(‘DSSUSER.EOD_JOB’,’PARALLEL_INSTANCES’,TRUE);
- END;
- /
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:
- Enabling File Watchers
- Altering File Watchers
- Disabling and Dropping File Watchers
- Changing the File Arrival Detection Interval
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:
- Connect to the database as the SYS user.
- 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