Handling request-response message in ESB Routing Services

Ever wondered how to define routing services based on soap services or database stored procedures where you could define routing rules on the request-and response-message.

Let's say you want to define the following scenario:
- Call an existing stored procedure that takes as input parameter 'employee.id' and as output parameter the employee-information (firstname, lastname, address, ...)
- Define routing rules on this stored procedure so you can send the response-message, the employee information to a 3-party application when the response-message isn't empty. In other words, when employee information is found for the given employee.id you will send this xml-message to the 3-party application.

This isn't rocket science right ... this should be a piece of cake ...

Well let's start building up the scenario in ESB, using our IDE Jdeveloper:
1) Create a new esb project
2) Create a new esb system
3) Create a DB Adapter calling the stored procedure we've defined to extract employee information using the employee.id as input parameter
4) Create a routing service based on the DB Adapter

=> We stumble upon the first problem => we're not able to define a routing rule on the response message, only the request-message (which is the input-parameter employee.id) can be used in our routing service.

Well let's try to create an empty routing service and use the xsd's generated by the db adapter, use the request-parameter as request-message and the response-parameter as response-message.

=> Nope, still not the right path to follow, we still can't define a routing rule on our response message.

Having a look at OTN doesn't provide me any answers, using the ESQREQUEST parameter is much to complicated for my basic scenario.

Now let's have a look at how this could be accomplished in a much more straigth-forward manner:
1) Define DB Adapter (as in the scenario defined above)
2) Define a routing service using the request-parameter of the xsd (don't fill in the response-message)
3) Define a routing service using the response-parameter of the xsd as the request-parameter of the routing service

In other words :
Define 2 routing services in which you will define the first to handle the request message and the second one to handle the response-message.

4) Link the 1st routing service, handling the request message, to the second routing service and now you can start using your services as needed.

I will provide screenshots for this scenario ASAP.