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

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 (10.2.0.4), 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

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

3. create a new, empty project ReceiveNewEmployee 

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

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

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

5. Choose the Composite with  Mediator Service

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

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

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

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

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

 8. Specify the name for the service:

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

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

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

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

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

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.

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

Press Next again. Specify the file filtering details.

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

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

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

 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.

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

Press the "Define Schema for Native Format"  button.

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

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.

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

Choose the file to sample and press next.

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

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

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

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

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

Indicate the type of delimiter. Press Next.

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

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

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

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

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

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

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

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.

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

Press next and press Finish.

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

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

 

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

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

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

Specify a name for this service:

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

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).

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

Specify the Operation: Insert into a table.

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

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

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

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

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

Press Next and press Finish. 

9. The composite now looks like this:

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

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.

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

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

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

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.

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

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

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

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).

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

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:

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

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

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

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

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

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

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

Summary

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.

6 Comments

  1. Yaretzi December 16, 2011
  2. Girish November 20, 2011
  3. Lucas Jellema November 13, 2011
  4. sailesh November 11, 2011
  5. John July 5, 2009
  6. Sudeep December 8, 2008