Scheduling EDN Business Events using DBMS_SCHEDULER

Introduction

Scheduling BPEL processes to automatically run at a specific time is a common requirement. Lucas Jellema has provided an article describing several options for this; http://technology.amis.nl/2006/10/22/starting-a-bpel-process-instance-according-to-a-timed-schedule-in-oracle-bpel-pm/

Quartz can be used to schedule processes from the applicationserver; http://javaoraclesoa.blogspot.com/2012/02/scheduling-bpel-processes.html. This provides some challenges when working in a clustered environment however. I didn't get this to work. In a non-clustered environment, I've not had any issues. In the used Quartz example, the schedule and BPEL process to call, were hardcoded into the scheduling process (SOAScheduler). This setup is thus not very flexible (although can be extended of course to introduce flexibility)

In this post I will describe another (in my opinion probably usually better) solution on how BPEL processes can be scheduled. DBMS_SCHEDULER and the Event Delivery Network (EDN) API will be used for this. DBMS_SCHEDULER is a PL/SQL package which can be used for scheduling database processes. Oracle has provided a PL/SQL API for firing EDN Business Events. This API can be called from a DBMS_SCHEDULER job. JDeveloper makes it easy to work with EDN Business Events in BPEL.

This solution has benefits compared to others such as;
- loose coupling through the usage of the Event Delivery Network
- no active polling from the BPEL process on a database table is required for this mechanism to work
- the EDN is supported in the GUI and makes for easy developing
- DBMS_SCHEDULER jobs can be managed from the Enterprise Manager and from PL/SQL (http://docs.oracle.com/cd/B19306_01/server.102/b14231/scheduse.htm#i1022969) which improved flexibility (also for maintenance)
- this mechanism requires relatively little (and simple) code to work

Implementation

I will describe a database implementation which is not how you eventually want to do this in a production environment; I created a package and jobs in the DEV_SOAINFRA schema. I did this for demonstration purposes only. How you probably want to implement this is by using a separate user for the database jobs and package and granting that user execute privilege on the EDN API procedure in the DEV_SOAINFRA schema (edn_publish_event) and scheduling privileges.

To be able to call the API package, I need to know how the message from my business event exactly looks. It is not very easy to reconstruct the exact business event from the definition. What I did to get an example business event, was the following;

Enable EDN logging

Execute the following script under the DEV_SOAINFRA user;

DECLARE
  ENABLED NUMBER;
BEGIN
  ENABLED := 1;
  EDN_ENABLE_LOGGING(
    ENABLED => ENABLED
  );
END;

This causes EDN messages to be logged in the table; EDN_LOG_MESSAGES under the same schema.

Create a simple BPEL process to fire a business event

In BPEL create a simple process with an exposed SOAP binding, which fires a business event and start this process from the Enterprise Manager. I've used the event definition at; http://dl.dropbox.com/u/6693935/blog/eventdefinition.zip

Get the business event

Open up your favourite PL/SQL editor, go to the DEV_SOAINFRA schema and get a sample business event from EDN_LOG_MESSAGES.

You need the XML part of the log message which starts with Body. In this example it is;

<business-event xmlns:ns="http://schemas.oracle.com/events/edl/CommonEvents" xmlns="http://oracle.com/fabric/businessEvent">
   <name>ns:CommonDataEvent</name>
   <id>5d52e455-2d5e-479a-bd20-18e8ca86b50d</id>
   <priority>5</priority>
   <content>
      <itemCollectionArray xmlns:ns1="http://test.ms/itemcollections" xmlns="http://test.ms/itemcollections"/>
   </content>
</business-event>

Remove information which you don't need. This event can be stripped to;

<business-event xmlns:ns="http://schemas.oracle.com/events/edl/CommonEvents" xmlns="http://oracle.com/fabric/businessEvent">
   <name>ns:CommonDataEvent</name>
   <priority>5</priority>
   <content>
      <itemCollectionArray xmlns:ns1="http://test.ms/itemcollections" xmlns="http://test.ms/itemcollections"/>
   </content>
</business-event>

Create a procedure to fire the business event

I've created the following package in DEV_SOAINFRA to fire the business event. In bold is the actual call to the API. the 1 specified after the message is the priority. Default is 5.

create or replace
PACKAGE SOA_EVENT AS
 procedure start_event;
END SOA_EVENT;
/


create or replace
PACKAGE BODY SOA_EVENT AS
 procedure start_event AS
 BEGIN
   edn_publish_event(
'http://schemas.oracle.com/events/edl/CommonEvents',
'CommonDataEvent',
'<business-event xmlns:ns="http://schemas.oracle.com/events/edl/CommonEvents" xmlns="http://oracle.com/fabric/businessEvent">
   <name>ns:CommonDataEvent</name>
   <priority>5</priority>
   <content>
      <itemCollectionArray xmlns:ns1="http://test.ms/itemcollections" xmlns="http://test.ms/itemcollections"/>
   </content>
</business-event>
',1);
 commit;
 END start_event;
END SOA_EVENT;

Grant scheduling privileges

To be able to use DBMS_SCHEDULER, you need some grants. You can use the system user to grant them;

grant create job to dev_soainfra;
grant manage scheduler to dev_soainfra;

DBMS_SCHEDULER configuration

The below part is specific for this example. You can find more examples on; http://www.apex-at-work.com/2009/06/dbmsscheduler-examples.html

begin
-- daily from Monday to Sunday at 22:00 (10:00 p.m.)
dbms_scheduler.create_schedule
(schedule_name => 'INTERVAL_DAILY_2200',
 start_date=> trunc(sysdate)+18/24, -- start today 18:00 (06:00 p.m.)
 repeat_interval=> 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN;
BYHOUR=22;',
 comments=>'Runtime: Every day (Mon-Sun) at 22:00 oclock');
end;
/
begin
   -- Call a procedure of a database package
   dbms_scheduler.create_program
   (program_name=> 'START_EVENT',
    program_type=> 'STORED_PROCEDURE',
    program_action=> 'SOA_EVENT.start_event',
    enabled=>true,
    comments=>'Procedure to trigger start event'
    );
end;
/
begin
   -- Connect both dbms_scheduler parts by creating the final job
   dbms_scheduler.create_job
    (job_name => 'JOB_START_EVENT',
     program_name=> 'START_EVENT',
     schedule_name=>'INTERVAL_DAILY_2200',
     enabled=>true,
     auto_drop=>false,
     comments=>'Job to trigger the Start event');
end;

Result

The defined business event will fire according to schedule and you can create a BPEL process which listens to this event and thus gets triggered according to the set schedule.