Oracle SOA 11g DB Adapter

High level Requirement : Every Job has a particular minimum and max salary offered , if the min salary for any Job is more than 15000 , then that needs to be approved by the finance officer , these approved Jobs are written in  a database table, in case when the salary is less than the specified 15000 then this is simply written into a file system . Technical Requirement : A Mediator Driven Process invokes 2 Services based on the Min salary, if its greater than 15000 , it invokes a particular BPEL process which would insert Job Data into HR Database after approval, if min salary is less than 15000 then mediator invokes another BPEL process that writes the Job data into file system, Yes there can be many other designs including using Rules Engine to decide on salary and routing it to different processes but that’s not considered in this example for simplicity sake. 
Design : BPEL Process 1 with Database Adapter and Human Task (This will be responsible for Sending Jobs Data to Database if Approved by Jobs Officer)

Design 2 : BPEL Process 2 with File Adapter and Rules Engine (This will be responsible for writing Jobs Data to a file based on some rules set by Rules Engine)

Design 3 : Mediator Driven Process Connecting BPEL Process 1 and 2 (This is the main mediator process that would be responsible for connecting Process 1 and Process 2 , routing and transforming data based on certain conditions set at Mediator)

Initial Setup Db Adapter Configuration on Console

Login to SOA WLS Admin Console at http://localhost:7001/console ,


Create a new dataa source by name “localhost-hr” and JNDI name as “jndi/localhost-hr”



Since our SOA is running on Admin Server , let the target be admin server



Its assumed that you are running Oracle XE database , and you have HR Schema installed , incase you dont have this schema installed you can aswell create the simple table as shown below

CREATE TABLE “HR”.”JOBS”

(
    "JOB_ID"     VARCHAR2(10 BYTE),
    "JOB_TITLE"  VARCHAR2(35 BYTE) CONSTRAINT "JOB_TITLE_NN" NOT NULL ENABLE,
    "MIN_SALARY" NUMBER(6,0),
    "MAX_SALARY" NUMBER(6,0),
    CONSTRAINT "JOB_ID_PK" PRIMARY KEY ("JOB_ID")
  )

Give the required username password , test connection

Under deployments , go to DB adapter, create a Outbound connection pool. in the xADatasource give the value as “jndi/localhost-hr” which is our JNDI name , save and Update the DB Adapter plan . 

Seeding Users such as Jcooper, Cdoyle etc

We would need to have few users like jcooper, cdoyle , jstein etc including their management chain  , refer the download link here  http://download.oracle.com/docs/cd/E12839_01/integration.1111/e10226/appx_users.htm , download file is DemoCommunitySeedApp.zip , extract and deploy SOATestDemoApp.ear file from the WLS Admin Console, this is easier than running Ant script for the same file , once deployed in the browser type in the following URL and hit Submit
http://localhost:7001/integration/SOADemoCommunity/DemoCommunitySeedServlet

Design 1 Details : Human Task

Its important to get the XSD Schema right , Create a new SOA Application by Name “Grade1App” , choose the option composite with Bpel process, modify the schema as shown in the figure , that is change input to job_id, job_title, max_sal, min_sal


On the components create a Human Task , by name “GradeHT”



Enter General info , task owner is weblogic,  

Data needs to be Process element of Grade1Proc.xsd , 

Approver , can be choosen as jcooper or can be picked up from the list

from the “Create Form” , Auto generate task flow ADF form , name it as “Grade1HTForm”

It would take some time to generate necessary ADF UI , you can modify this if required.

Edit the BPEL Process, add Human Task after receive input activity

Task flow parameters needs to be selected from XSD Process element

Click OK

Deploy n Test Human Task

Right click on the Grade1Proc Project, select Deploy to Server option

Select overwrite option , version number

Select the check boxes to include the ADF Human Task form, this can be one time operation , on sub-subsequent updation of BPEL you can deploy only that with out ADF HT UI

Choose Admin Server

Login to SOA EM Enterprise Manager http://localhost:7001/em Test the Process with proper input values

The Human task is invoked , login to BPM Worklist application as jcooper  , http://localhost:7001/integration/worklistapp , you can go to Settings and select Developer mode to view  XML details and process flow

Process flow can be viewed under Flow Tab

NOTE : It is important that do need to link up worklist app with Human Task ADF UI , to do this perform following steps go to “Component Matrix” under the BPEL Process, click on Human task , add URI
Application Name is : worklist
Host Name is : localhost
HTTP Port : 7001
URI : /workflow/Grade1HTForm/faces/adf.task-flow?_id=Grade1HT_TaskFlow&_document=WEB-INF/Grade1HT_TaskFlow.xml
 /workflow/<your task form application name>/faces/adf.task-flow?_id=

<human task component name in your composite>_TaskFlow&_document=WEB-INF/
<the .xml file you see in your task application's WEB-INF directory>


Click on GradeHT


refer :

http://soaprofessional.blogspot.com/2011/06/details-not-available-for-this-task.html

DB Adapter

On the composite , under External references add Database adapter

Give the name as “Hrjobsadapter”

choose a DB connection , ensure that JNDI name is “eis/DB/localhost-hr” this is same as what we had defined as a DB Adapter Outbound connection under WLS Admin console

Choose insert only

Select Jobs table

No need to select Sequence

Wire HR DB Adapter to Grad1Proc in the composite

Edit BPEL Process, Add invoke activity to connect to JobsDBAdapter

Just Ensure that this Invoke is under Accept Switch case after approval , so that only after approval , we invoke the DB adapter to write into table.

Add an assign activity before invoke jobs adapter , map the input parameters to database table inputs in the invoke 

Save all and deploy the BPEL process

Testing Application

Login to EM

Send the required parameters 

Login to worklist app as jcooper, Approve

Conclusion : The Jobs Data is sent for Approval , if Approved then the data is written into Database using DB Adapter