12c SOA - DB Adapter - Perform select operation on a table

Here I will do:

• Create a table Employee and insert some employee rows.
• Create a DB Adapter and fetch the employee data based on employee id.
• Create a synchronous BPEL service and call the DB adapter

Create a table:

create table employee

(

emp_id number(10) not null,

emp_fname varchar2(50),

emp_lname varchar2(50),

dept_id varchar2(50),

dept_name varchar2(50),

emp_type varchar2(50),

CONSTRAINT customers_pk PRIMARY KEY(emp_id)

)

Insert Data:
insert into employee values (1,'Sri','Das','123','OSP','P')

insert into employee values (2,'Dip','Cha','123','OSP','S')

 Create a SOA project

 Right click on the External References lane ⇾Insert⇾Databse

 Provide the adapter name

 Create the DB connection

 Provide the JNDI Name and if you don't have any JNDI then create it in admin console.

 Select Perform an Operation on a table

 Import Tables

 Give the table name in the filter and select it.

 Next

 Next

 Add a parameter

 Name the Parameter.

 Click Edit

 Add

 Select Parameter

 Select created EmpId as parameter

 Next

 Next

 Next

 DB Adapter created.
Now Right click on components and insert BPEL Process

 Select Synchronous service

 Modify the XSD

 Wire the BPEL with the adapter

 Drag and drop an invoke activity and call the adapter partnerlink

 create the invoke input and output

 Assign the Input

 Assign the output

 Deploy and test