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.

Scheduling BPEL processes

Introduction

A BPEL process needs a trigger to be started. This trigger can be the result of polling of one of the JCA adapters. The trigger can also be external such as for example a service call.

Usually the trigger is a database event which for example can be a change in a table or a direct call using a database table trigger and the UTL_HTTP package.

Using UTL_HTTP from a database trigger to directly call a BPEL process, requires the endpoint of the BPEL process to be known in the database and configuration of the ACL (access control list). This however, can be difficult to implement and maintain if there are many (database and application server) environments.

The customer

Sometimes, it is difficult to obtain a trigger from the database. This is the case if for example changes in a table can not be monitored directly by using a polling databaseadapter. If the customer uses Updateable views (see http://psoug.org/reference/views.html on Updateable views) BPEL can use them, but not all views are updateable. Of course, a separate audittable or file can be used for that, however this requires additional changes out of scope for SCA deployment.

Other options based among others suggested by Lucas Jellema on

http://technology.amis.nl/blog/1372/starting-a-bpel-process-instance-according-to-a-timed-schedule-in-oracle-bpel-pm

were tried. Using the database adapter to poll for a function result from dual to check if processing was required, was not easily implemented since the database adapter wizard requires a mechanism to record processed records. Disclaimer; I spend little time on experimenting with this. Also I didn't like the idea of using a BPEL process to loop and using the BPEL While and Wait activities to provide scheduling, since this process also needs to be started (chicken/egg story) and the processflow would become enormous if for example it would run for several years every few seconds. Such a flow could cause, amongst other things, memory problems if opened in a webbrowser for viewing.

Quartz

A Java programmer suggested using the open source Java scheduler Quartz for scheduling; http://terracotta.org/downloads/quartz-scheduler. Another BPEL developer said he had been experimenting with using Quartz to create BPEL instances and had a working example somewhere. This triggered me to start reading Quartz documentation. Of course, because I'm always optimistic, I first thought about possible issues such as the time required to configure initializing the scheduler at server start/deployment and the time it would require to implement this scheduler.

Then I found the following document; http://www.oracle.com/technetwork/middleware/soasuite/learnmore/soascheduler-186798.pdf and my problems were solved. The Weblogic server contains Quartz libraries which are already configured to be started at server startup saving me configuration time and the example was thorough enough to make implementation easy.

Nice to know; if you want to use the above mentioned example, don't use a 2.x version of Quartz unless you don't mind spending time to migrate the example Quartz 1.x code to 2.x (there are some major changes in the API) and on dealing with class version differences since Weblogic already has 1.x Quartz libraries.

Update

Release 11.1.1.6 of Oracle Fusion Middleware contains an Oracle Enterprise Scheduling Service
http://docs.oracle.com/cd/E23943_01/dev.1111/e24713/intro.htm#sthref10

This might be an even better alternative then using Quartz. I haven't had the time yet though to dive into this.

Even when using relative URL's in the SOAScheduler process, it is challenging to make this work in a clustered environment.