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.
- Create a custom table for scheduling.
- Create a BPEL process with Database Adapter.
- Configure DB Adapter to listen to custom table for new records.
- Invoke Email activity if new record is found.
- Save the BPEL process and deploy on BPEL server.
- Finally using DBMS_SCHEDULER insert records into a custom table.
- Verify the instance in BPEL console.
Environment used for this article
- SOA Suite 10.1.3.1 Basic Installation. So it uses Oracle Lite database.
- JDeveloper 10.1.3.3.0
- 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
START WITH 1
MAXVALUE 999999999999999999999999999
MINVALUE 0;
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'.
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.
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.
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.
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.
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.
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);
commit;
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.
begin
dbms_scheduler.create_job(
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');
end;
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.
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.
begin
dbms_scheduler.drop_job('XX_SCHED_MAIL',TRUE);
end;
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.
Apps2Fusion Articles 
Comments
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!
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?
Thanks,
Bhakta
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
RSS feed for comments to this post