Optimizing your BPEL process - inserting multiple records into a database in one go.

Oftentimes you may want to insert multiple rows of records into a database in one single call instead of calling the database adapter multiple times (one call for inserting one row). This is the recommended way as it entails significant performance boost. Unfortunately the BPEL PM does not have an out of box functionality to implement this. But there is a workaround. It involves some tweaking at the database level which is not very difficult as I am going to show.

Assume you have the following file whose data you need to insert into the database. Your first step will be to create a file adapter to read this file which is relatively simple.

The next step is to create a database type whose structure is equivalent to the structure of the records in the file. More specifically, the fields of the record (name,age,superior) are reflected in the fields of the type. Here is how you got to do it.

CREATE OR REPLACE type file_record as object(name varchar2(20), age number, superior varchar2(20));

Then you need to create a nested table for the above type.

CREATE OR REPLACE file_record_tbl as table of file_record;

Now, you need to have a procedure in place for inserting data into the database. This procedure takes file_record_tbl as input parameter (unlike one that takes simple data types as input). Inside the procedure you can use this file_record_tbl to insert data for inserting data.
...
create or replace procedure insert_data(p_file_record_tbl in file_record_tbl) as
begin
FOR x IN 1 .. p_file_record_tbl.COUNT
loop
insert into <table_name> values
(
p_file_record_tbl.name,
p_file_record_tbl.age,
p_file_record_tbl.superior
);
end loop;
end insert_data;
...

Now all you need to do is create a database adapter and call this procedure from JDeveloper. Also, use a Transform activity to populate this type with the data from the file. That is all.