Oracle 11g SOA Suite – Straightforward Mediator example (employees from file to database)

Lucas Jellema 6
0 0
Read Time:6 Minute, 30 Second

It’s SCA based, it has the Event Driven Network (EDN), a much better integration between the components, an AJAX powered console and a better design time. It’s the all new – yet on many accounts familiar – Oracle 11g SOA Suite. During the Christmas break I started work with the Technology Preview for the 11g release. In the last few months I have not had as much time with the 11g edition – I had to focus on the 10g that is actually used in the real world. Thanks to this early Easter-weekend and the accompanying bleak weather, I have found some more time to look into it. I installed a Virtual Machine with Oracle 10gR2 EE (, JDeveloper 11gTP3 with the SOA Suite preview and the February upgrade. After configuring SOA on the Integrated OC4J I could quickly get the HelloWorld of SOA running (synchronous BPEL process, input is a simple String, output is that same string, concatenated with Hello. I then tried the somewhat more ambitious PO Processing sample (#105) and almost got it to work. 

Then, after having concluded that the stuff was – by and large – working, including the Worklist Application and the Human Workflow  service, I was ready to start some free format trials. One of things I wanted to look into was the EDN – the Event Delivery Network that allows an EDA-style loose coupling of processes: an event occurring in one process can be published (in a fire-and-forget manner, as the publisher does not care who consumes the event, if anyone at all) and can be consumed by any interested party listening in on the EDN. Such parties will typically be Medidator services – as we will see in a subsequent blog article.....

The process I will implement in this article will use the File Adapter service to read Employee details from a file, receive them in a Mediator service that transforms the data and routes them to the Database Adapter service that inserts them into the EMP table in the database. It is nothing very special – in that we can implement this same process in the 10g release of the SOA Suite. It is nice to see what it looks like in the 11g world. And it provides the foundation for the EDN try-out that follows: the arrival of a new Employee is the event we will publish. Consumers may each deal in their own individual way with that event.

The steps for implementing this process:

1. start JDeveloper 11g (I assume here that the Configure SOA step has been performed and the Integration Server is running)

2. create a new application NewEmployeeEvent


3. create a new, empty project ReceiveNewEmployee 


4.  Create a SOA Composite from the New Gallery, SOA Tier category

5. Choose the Composite with  Mediator Service

6. Set the name of the Mediator Component – ReadNewEmployees. Define the Interface of the Mediator at a later moment


7. Drag the File Adapter Service to the left swimlane – where the Services are configured.


 8. Specify the name for the service:

Define the interface (WSDL) later from the operation and schema defined in the wizard:

Select the Read File operation. Define a meaningful name for the operation:

Press Next and specify the directory where the files will appear and should be read from. Also indicate the archive directory where files are stored after having been processed. The processed files themselves are removed from the input directory.


Press Next again. Specify the file filtering details.

Press Next again. Specify the polling frequency. In this case – demo environment – choose frequent polling.

 Press Next. On this page we can specify the Schema (XSD) for the file we will read. Or we can specify (Schema is opaque) that we will process the file as is. Or we can use the Native Format wizard for creating an XSD that we can use as representation for the file that will be read.

Press the "Define Schema for Native Format"  button.

I have the file Edith.txt as an example of what the New Employee files will look like. They contain in a CSV format the main properties for a single employee.

Choose the file to sample and press next.


Accept the default – a single record in a file. Press Next.

Name the element we will extract from the file: Employee. Press Next.

Indicate the type of delimiter. Press Next.

Specify the names of the columns – also used as the names for the child elements in the Schema that will be created.

Press Next. Set the name of the XSD file that gets created.


A new feature in 11g is the option to test the Schema file we have just created. Press the Test button. This window appears:

We can select a file with the expected input format and press the Play (Run) button. The XML document that results from the read operation using our configuration details is presented on the right side. Press OK to return to the File Adapter Wizard.

Press next and press Finish.

The File Adapter Service is show
n in Exposed Services swimlane for our (SCA) composite.


8. Configure the Database Adapter for inserting records into the EMP table

Drag the Database Adapter and drop in the External References swimlane.

Specify a name for this service:

and choose the Database Connection (here we will simply use a connection defined in JDeveloper, for which the details will be deployed to the Application Server).

Specify the Operation: Insert into a table.

And select the EMP table as the object of the operation:


Specify which of the Table Attributes (columns) will be included in the insert operation:


Press Next and press Finish. 

9. The composite now looks like this:

Time to wire the Service to the Mediator component. And also to wire the External Reference (database adapter service) into the Mediator component.

10. Create a wire from ReadNewEmployeesFile to the ReadNewEmployees Mediator component.

Also create a wire from InsertNewEmployee back to the ReadNewEmployees Mediator component

10.  Create the Routing Rule and Transformation mapping in the Mediator Component.

Double click the ReadNewEmployees component. Expand the Routing Rule already shown – that connects the File Adapter Service input via the Mediator to the Database Adapter Service Reference. Click on the Transformation Map icon.

Create New Mapper File. Set a nice looking name for the XSL file. Then press OK. The XSL Mapper window opens.

Connect the input elements to the appropriate output elements as shown in the picture.

11. Now we can deploy our SCA Composite to the Bundled OC4J server – and then see it in action.

12. Copy a file with new employee details to the configured directory (c:\temp\hr).

Within 5 seconds, this file disappears from the directory – being processed by the Mediator Service we just deployed. The file reappears in the archive directory:


The effect of the process is clear when we query table EMP:

We can look in the 11g SOA Suite SOA Console that lists all instances of all SOA Services and processes.

Downdrilling on the most recent instance, we can see its Audit Trail:


In this article, we have seen how we can implement an end-to-end service that reads files, transforms and routes them and delivers them into a database adapter service that creates records in a database table. Using the Adapter Services and single Mediator component, packaged together in an SCA composite, it took much more time to compose this article than it did to implement the actual process.

In two subsequent articles I will show how to define and publish a Business Event into the Event Delivery Network and how to Subscribe to such events and process them as they occur.

About Post Author

Lucas Jellema

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.
0 %
0 %
0 %
0 %
0 %
0 %

Average Rating

5 Star
4 Star
3 Star
2 Star
1 Star

6 thoughts on “Oracle 11g SOA Suite – Straightforward Mediator example (employees from file to database)

  1. Hi Lucas,
    How do you install a Virtual Machine with Oracle 10gR2 EE ( I am currently taking the COmposite Application Self-paced but it doesn’t come with a DB. I would like a VM to try the exercises.




  2. Hi Lucas,
    Is it possible for the mediator/BPEL to accept multiple inputs (other than through subscribing to events) from a Queue.
    My requirement: I have to listen to two Queues, data coming in these are the same.  For these two I have to query the DB and then do the transformation (these two activities are same for the data in both the Queues). After transformation need to send the data to a Webservice.
    As Invoking DB, doing transformation and sending to webservice is common for the Data from both the Queues I don’t want to create 2 bpel / mediators.

  3. do you have step by step of calling xml and then inserting into db.. please its an urgent requirement .. if so please send to my mail id…

  4. I have couple of questions as i was trying this with the new release oof Fusion middleware 11g. I have downloaded JDev how do i test it with the Integrated Weblogic Server. Is there any configuration required to test with Integrated WLS in JDeveloper rather than installing the WLS.

    I also installed the WLS 11g but when i test it gives me the following error and don’t know how to modify weblogic-ra.xml and where exactly is it located

    Exception occured when binding was invoked. Exception occured during invocation of JCA binding: “JCA Binding execute of Reference operation ‘InsertNewEmployee’ failed due to: JCA Binding Component connection issue. JCA Binding Component is unable to create an outbound JCA (CCI) connection. myFirstComposite:InsertNewEmployee [ InsertNewEmployee_ptt::InsertNewEmployee(InsertNewEmployee_inputParameters,emp) ] : The JCA Binding Component was unable to establish an outbound JCA CCI connection due to the following issue: BINDING.JCA-12510 JCA Resource Adapter location error. Unable to locate the JCA Resource Adapter via .jca binding file element The JCA Binding Component is unable to startup the Resource Adapter specified in the element: location=’jdbc/soaDataSource’. The reason for this is most likely that either 1) the Resource Adapters RAR file has not been deployed successfully to the WebLogic Application server or 2) the ” element in weblogic-ra.xml has not been set to jdbc/soaDataSource. In the last case you will have to add a new WebLogic JCA connection factory (deploy a RAR). Please correct this and then restart the Application Server “. The invoked JCA adapter raised a resource exception. Please examine the above error message carefully to determine a resolution.


  5. I tried this project. It works for a single record, but when I have multiple rows in the same file, I get a unique constraint error for the first row. It seems like the first record is being inserted multiple times. In the file adapter I have specified “File contains multiple records of the same type”, but that doesn’t resolve this error. However, when I load many files containing one record each, I am able to insert many records in the database.

    In any case, thanks for your blog. It proved quite helpful.

Comments are closed.

Next Post

Oracle 11g SOA Suite - The Event Delivery Network - Publishing Business Events

In a previous post – Oracle 11g SOA Suite – Straightforward Mediator example (employees from file to database) – I demonstrated how we can quickly implemented a Service Composite that reads files with Employee data and turns them into new HR database records (well, simply creates records in the SCOTT.EMP […]
%d bloggers like this: