Fusion Blog

EBS Blog


Contact Us

Kishore Ryali
  • Register

Oracle Gold Partners, our very popular training packages, training schedule is listed here
Designed by Five Star Rated Oracle Press Authors & Oracle ACE's.

webinar new Click here to raise Support Ticket. Get reply within 48 hours.

Search Courses

Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive
This article is continuation of Part I of Scheduling Email notifications using BPEL. In the earlier article, I created a bpel process which uses email activity and manually invoked the bpel process from BPEL Console to send email notification. Now I will improvise it by scheduling the bpel process without manual intervention.

Scheduling a bpel process can be done in many ways and few of them are:

  • Use a life-time running bpel process whose only purpose is invoke other bpel processes. It can be implemented by using WHILE, WAIT and INVOKE activities.

  • Use Java Timer (Scheduled bean in a container) or Quartz Scheduler to create Cron type schedules for bpel processes.

  • Use DBMS_SCHEDULER Oracle 10g functionality. BPEL Designer has many predefined event listener activities like File Adapter, FTP Adapter, Database Adapter which constantly watches for any event  to happen. You can use one of those activities to trigger bpel process when DBMS_SCHEDULER say creates a file in a location, insert a record in table etc.
For this article, I will use the third option to demonstrate the scheduling. The following are the steps in a nutshell:

  1. Create a custom table for scheduling.
  2. Create a BPEL process with Database Adapter.
  3. Configure DB Adapter to listen to custom table for new records.
  4. Invoke Email activity if new record is found.
  5. Save the BPEL process and deploy on BPEL server.
  6. Finally using DBMS_SCHEDULER insert records into a custom table.
  7. Verify the instance in BPEL console.

Environment used for this article

  • SOA Suite Basic Installation. So it uses Oracle Lite database.
  • JDeveloper
  • Oracle 10g Express Edition (XE)

Create custom table

In this step, I will create a custom table say 'XX_SCHEDULE_MAIL' and the script used is below.

CREATE TABLE xx_schedule_mail (id number, mail_date DATE, rflag NUMBER DEFAULT 1)

CREATE SEQUENCE xx_sched_mail_s
  MAXVALUE 999999999999999999999999999

Id column is populated with a sequence generated from XX_SCHED_MAIL_S, mail_date with sysdate and rflag with '1' by default. When DB Adapter listens to this table, it checks if the new record has rflag as '1' then it invokes the email activity and updates rflag to '0'. That means if the email is sent the record gets updated with rflag as '0'.

Create BPEL Process using Database Adapter

I assume you have followed first article of scheduling, to configure default email account in ns_emails.xml and polling frequency in wf_config.xml. If you havent done it before, please follow Part I of Scheduling Email notifications using BPEL.

Start SOA Suite and Open JDeveloper to create a empty BPEL process. As my BPEL process is not invoked manually from BPEL console, I chose empty BPEL process rather Asynchronous like  in first article.My BPEL process looks like below with no activities and services on right and left swim lanes.

Select Database Adapter from Component Palette > Services. Drag it to the right swim lane.
It pops up the Database Adapter wizard. It gave it a name and continued to the second step to choose database connection.

As I havent setup my XE data connection, I chose 'New'. If you have database connection already setup, choose the connection and skip the below step.
So Im back to Step 2 of Database Adapter configuration. Now I've XE connection.
In Step 3, I selected "Poll for new record in Table" as operation type as I will use the above custom table for triggering the DB Adapter. You can also use Db Adapter to execute a stored procedure or custom sql. The next step shows no tables are imported. Click Import Tables to select the table.

Select the schema and Query for name 'XX%'. Select XX_SCHEDULE_MAIL table to the left pane and click Finish. Now the Select Table step shows the table name. Click Next.

Step 5 asks you to identify a virtual primary key for the table. This step is required but it doesnt change the physical database structure. I chose Id as primary key and skipped the relationship creation step.

Step 7 I unchecked both of the columns because I dont want to exclude them from database query from this service.


The following steps are very crucial for how the database adapter processes the record once it reads. As I talked before, I will update my rflag column from '1' to '0' if the record is read/processed. So when DB Adapter polls to check for new records, it doesnt re-read the processed records.

Step 8 I chose to update the record column. Other option is to delete the record from the table once it is read. Delete is also a good option as the records in the custom table doesnt grow et al. But I wanted to keep the records so I know at what time the mail was sent.

In Step 9, I selected 'RFLAG' column for update field, Unread value as '1' and Read Value as '0'. So when the record is read, rflag is updated from '1' to '0'.

Step 10 I changed the polling frequency to 2sec. It also shows the query built using the values I specified on step 9.

Step 11 summarizes the query built on using the configuration done so far. Click Finish. It pops up the partner link window.
I named partner link as 'InboundPL'. Click Ok to return to BPEL Designer. My BPEL process looks like in the below screenshot.
Now I will add a Receive activity to bpel process. Receive activity waits on database adapter to take action on the custom table. You can see the warning icon on the receive activity, it is shown when the activity is not configured.

Now I will link receive activity to 'InboundPL' database adapter by dragging the left arrow of receive activity to InboundPL. This pops up the configuration window of receive activity.

I named the activity as 'EmailRun'. You see the partner link name and the operation.

Now create a variable by clicking a create variable icon as shown in the screenshot. Leave the default name and click ok.

Check the create instance checkbox to complete the configuration of receive activity. Click ok to return to BPEL Designer.
Now my bpel process looks like below.
Now I will add the Email activity after receive and configure it like in the first article.

Below is the screenshot of my bpel process.
This completes my BPEL process. Save All the work and deploy the process to Integration Server.
Create Scheduling using DBMS_SCHEDULER

DBMS_SCHEDULER package is introduced from Oracle 10g release onwards, to schedule jobs in Oracle. Prior to 10g, DBMS_JOB was used for the same purpose.

Rules for using DBMS_SCHEDULER package:
  • Only SYS can do it in SYS schema. (or)
  • Assign SCHEDULER_ADMIN role to database user.

Normally SYS login credentials is only with the DBAs and only SYS or SYSTEM user can assign the above role to your user. In both ways, you are tied to work with your DBA to get the privilege to schedule any job. If the user is not SYS, and you try to run DBMS_SCHEDULER package you will the below lack of privileges error.

ORA-27486: insufficient privileges
ORA-06512: at "SYS.DBMS_ISCHED", line 99
ORA-06512: at "SYS.DBMS_SCHEDULER", line 262
ORA-06512: at line 2

If you are not able to get SYS user or SCHEDULER_ADMIN role, you can always test the article just by inserting a record with INSERT statement in sqlplus, even without scheduling the job.

insert into xx_schedule_mail(id,mail_date) values(xx_sched_mail_s.nextval,sysdate);

To show the scheduling feature, I have installed Oracle XE to get SYS login.  With Oracle XE, HR user comes by default, but it is locked. I have logged in Oracle XE User Administration page, and unlocked HR user. Now I login as SYS user and assign SCHEDULER_ADMIN role to HR user.

I will use 'CREATE_JOB' procedure in DBMS_SCHEDULER package to create job to insert a record into XX_SCHEDULE_MAIL periodically every 30 sec. For documentation to create_job, go to CREATE_JOB procedures.

Below is my script I executed from HR user.

      job_name => 'XX_SCHED_MAIL'
     ,job_type => 'PLSQL_BLOCK'
     ,job_action => 'begin insert into xx_schedule_mail(id,mail_date) values(xx_sched_mail_s.nextval,sysdate); end;'
     ,start_date => sysdate
     ,repeat_interval => 'FREQ=SECONDLY;INTERVAL=30'
     ,enabled => TRUE
     ,comments => 'BPEL Schedule Email');

Job_Name - Unique name for the job
Job_Action  - PL/SQL block code.
Start_Date  - It gave it as current date
Repeat_Interval - Frequency is in Seconds and Interval is 30. That is the job runs every 30 sec.

You can play different values with Repeat_Interval parameter values.

Once the job is created, I can query from DBA_SCHEDULER_JOBS table to check for the job name. My job is running and it is inserting records into XX_SCHEDULER_MAIL table with rflag as '1' default value.

As you see, some of the records have rflag as '0' which means those records are processed by Database Adapter in bpel process. I will go to BPEL console, to check the instances of my bpel process.
I went to Bpel process flow diagram and clicked on receive activity. It shows me the Id that is currently used. Now I will go to my email to check the notification.

Here you go, I got bunch of emails sitting in my inbox waiting for me to open.

Disabling the scheduled job

After I've played enough with my scheduled job and bpel process, I dropped the job otherwise my inbox will be flooded. So I used the below script to drop my job. The second parameter is FORCE which means the script will drop the job though it is currently running.


As a recap of the whole article, the things I will done to schedule email notification using bpel:
  • Configure default email account in ns_emails.xml and polling frequency in wf_config.xml
  • Start your SOA Suite and JDeveloper.
  • Create a custom table to use it as a driver for DB Adapter.
  • Create a empty bpel process.
  • Create DB Adapter and configure it to read custom table XX_SCHEDULE_MAIL.
  • Configure DB Adapter to update the RFLAG column from '1' to '0' after read.
  • Create Receive activity linked to DB Adapter.
  • Create and configure email activity with email address, subject and body.
  • Save bpel process and deploy to bpel process manager.
  • Use SYS user or SCHEDULER_ADMIN role assigned user to schedule job
  • Use DBMS_SCHEDULER package to create job to insert a record into table every 30 sec.
  • Login to BPEL console to check the instances initiated.
  • Drop the scheduled job after the article is completed.

Kishore Ryali


0 #1 Guest 2008-10-14 05:35
I personally feel there is too much activity around this to make the scheduling work and it's going to consume lot of DB/Polling resources needed.
One option I could think after reading this is to create a simple cron job in unix environment and call that service to invoke the BPEL WSDL file. The program can be a simple java calling a webservice ..... Let me know what do you think about it ! I haven't tried this yet but just a thought!
0 #2 kishore Ryali 2008-10-14 08:52
There are number ways to schedule email like creating ever-waiting bpel procress which kicks off your scheduled bpel processes, java program using Quartz scheduler etc., I've mentioned them in the starting of the article. I do reckon the approach I took in the article does create lot of db polling and network traffic. Yes it is not the optimized method, I'm yet to try the quartz scheduler. I will do an article on that too.

0 #3 bhakta 2009-01-06 14:56
HI Kishore,

I am designing a BPEL process which polls for DB changes and runs a concurrent program after it finds changes.But I need this process to run concurrent program
only once even though multiple records are polled. when I tried process is submitting the concurrent program which si basically a stored procedure once per each record
.But I need this program to be submitted obnly once for multiple records also.

Is is possible to set the polling options which meets the above scenario.Can u help?

0 #4 bhakta 2009-01-06 15:04
I tried with updating sequence table.
0 #5 saibotta 2010-02-23 02:41
Thanks for the articles.could anyone pls detail me as how to schedule email in bpel or in short set an interval once bpel process starts as to retrieve the records from DB table and send emails to admin with a regular interval so tht the same file may not be sent repeatedly as email

Add comment

Security code

Search Trainings

Fully verifiable testimonials

Apps2Fusion - Event List

<<  Aug 2021  >>
 Mon  Tue  Wed  Thu  Fri  Sat  Sun 
  2  3  4  5  6  7  8

Enquire For Training

Related Items

Fusion Training Packages

Get Email Updates

Powered by Google FeedBurner