How to use the BPEL file adapter to load data into Oracle

This post shows a simple example about how to use Oracle’s BPEL server to load and parse a datafile. The data in the file is then inserted into a database table. We use the file adapter and the database adapter to accomplish this task. The nice thing of this BPEL process is that we don’t have to instantiate the BPEL process ourselves. A BPEL process is started as soon as the file holding the data is detected by the BPEL engine.

The following pictures shows the BPEL process in jDeveloper:


Note that the process has no user interaction. The process has no manual input. A BPEL process is instantiated as soon as a file is detected. This is accomplished by entering the “Receive instance” flag on the file receiving activity:

The input file is detected and parsed by a file adapter. This is a special add-on to the Oracle BPEL engine which is based on WSIF. The file adapter acts like a partner link in the BPEL diagram. Note that the actual adapter is running in the BPEL engine, it’s not an external partner link. Creating a file adapter is very simple. The following shows some screenshots of the wizzard:

The file adapter holds it’s own parser. This parser is XSD based. There is even a wizzard that helps you create a XSD file:

The previous steps result in a XSD file which can be used by the file parser of the file adapter.

I finally created a (very very simple) PL/SQL procedure. The data found in the file is passed to this procedure and inserted in the database.

create or replace procedure fillAndre(a number, b number, c number, d number)
is
begin
insert into andre values(a,b,c,d);
commit;
end;

The invoke activity in the diagram invokes this procedure and that finishes up the BPEL process.