Implementing Web Services backed by a Database PL/SQL API using the Oracle Service Bus

This article accompanies an article on the Architecture section of Oracle Technology Network (OTN): Implementing the Enterprise Service Bus Pattern to Expose Database Backed Services. It provides a detailed description of the implementation of the ESB architecture design pattern – the same that is introduced in the article on OTN – using Oracle Service Bus. This OSB based implementation is in terms of structure, architecture design and functionality the replica of the pure Java based implementation described in the OTN-article. OSB adds productivity, agility and many run time administrative benefits over the pure Java solution.

The challenge

The challenge the organization – let us call them Stuff Inc. – is facing is simple and common: external parties have requested access to information through Web Services. This information is held in the enterprise (Oracle) database at Stuff Inc.

The architecture team decides that the implementation of the service should be done according to the ESB architecture pattern.

Their initial implementation is based on custom Java Classes, XSLT stylesheets and JDBC calls.

After some time having worked with this initial set up, they decide to introduce the Oracle Service Bus to replace much of their generic classes and ESB plumbing. This article tells the story of how the OSB is used to implement a simple Web Service based on a PL/SQL API in an Oracle Database, using two transformations and a JCA Database Adapter service. It demonstrates the essential steps in implementing any OSB service.

The Oracle Service Bus to make life easier

The architecture thought up by the team at Stuff Inc. works. It has proven itself through the implementation discussed above. It provides flexibility, scalability, encapsulation and decoupling and makes maximal use of the database development skills available at Stuff. However, it requires quite a bit of manual activity. And this architecture and tooling provide very little in terms of monitoring and administration.

When Stuff Inc. learned that they would be rapidly expanding their Web Services portfolio, adding both services and service consumers, they reassessed the implementation they had started out with. And they decided to bring in the Oracle Service Bus. This decision was primarily inspired by a desire for higher productivity and less dependence on Java programming skills. Additionally, Stuff’s administrators were a little overwhelmed by the operational responsibility for the web services they had been given. They were looking for tools to monitor service activity and help analyze issues.

It turned out that the architecture initially designed for the data services is still very much valid when the OSB is used. The VETRO-stages are still present, though implemented in a slightly difference way. The proxy service – helped by the standard facilities in the OSB – does most of the validation, enrichment, transformation and routing while the business service leverages the Database Adapter to invoke the PL/SQL API. Note that this adapter is quite capable to speaking in user defined types which means that the XMLType wrapper in the database is no longer required. The PL/SQL API based on user defined types can be used as is.

The OSB proxy service and business service replace the coordinator, mediator and router Java Classes of the earlier custom Java implementation. However, the XSL-T transformations used by the mediator class in the original implementation can be reused in the proxy service in the OSB. Coordination and routing is configured declaratively and performed by the OSB infrastructure.

The Routing Node makes the call to the Business Service – the JCA Database Adapter that invokes the PL/SQL API on behalf of the Proxy Service JukeboxServicePS. The Replace action in the Request pipeline transforms the CDQuery request into the XML Structure required by the Business Service. The Replace action in the Response pipeline (right side of the Routing node) transforms the response from the Business Service into the CDCollection structure defined in the WSDL and XSD contract that the Proxy Service should adhere to.

The overall implementation using the OSB can also be depicted like this:

The steps for implementing the JukeboxService using the OSB are described below.

Prerequisites

In order to proceed with steps below, you need to have the Oracle Service Bus run time and design time installed. The design time is based on Eclipse, complemented with OEPE – the Oracle Enterprise Pack for Eclipse – and the OSB IDE. The design time is installed in two steps – first the OEPE pack, for example using the generic WebLogic Server installer, followed by the OSB IDE from the Oracle Service Bus installer. The run time is installed, either on the same machine or on a remote machine, using these same installers.

Getting started with the OSB IDE

Start Eclipse with the OSB installed.

See for instructions on installing the Eclipse IDE for OSB for example this blog-article: https://blogs.oracle.com/middleware/entry/installing_eclipse_for_osb_development and this article on taking your first steps: https://blogs.oracle.com/christomkins/entry/oracle_service_bus_v30_getting.

Creating the OSB Project

Open the Eclipse New Wizard and select the option Oracle Service Bus Project:

Enter the name for the new project – for example JukeboxService. Accept the default setting for the OSB Configuration.

Press Finish.

The project is created, along with the OSB Configuration project if that does not already exist.

The project is empty at this point.

Create the ProxyService

The ProxyService is the public interface for an OSB Service. It is based on the contract – either a Web Service definition or some other form of contract, which can be EJB, POJO (Java) API, RESTful and many other formats.

In our case, the ProxyService is based on the WSDL and XSD documents that form the public contract for the JukeboxService. These two documents should be imported into the OSB project.

From the File Menu, choose the option Import.

In the Import dialog, choose the option Oracle Service Bus – Resources from URL. Note: importing from General | File System would also work in this case given the fact that the resources are present on the local file system.

Click Next.

Browse for the JukeboxService.wsdl document that describes the Web Service that the ProxyService should implement. Select WSDL as Resource Type. Press Next.

The Review Resources page should list the WSDL file and the associated XSD file as well. Both should be imported. Press Finish to start [and complete] the import operation.

The imported resources are copied to the project directories and show up in the Project Explorer:

You can inspect the JukeboxService.wsdl document, to get a visual overview of the Service and its operation(s):

The WSDL that defines the contract that this OSB project will have to fulfill is available. You can now create the Proxy Service that is to be the public interface of the OSB Project JukeboxService. From the New menu, select the option Proxy Service.

The New Oracle Service Bus Proxy Service dialog appears. Select the root folder in project as the parent folder to create the Proxy Service definition in. Type the name of the Proxy Service: JukeboxServicePS and click on the Finish button.

The Proxy Service editor appears, with a number of tabs to configure various aspects of the Proxy Service.

On the General Configuration tab, select WSDL Web Service as the Service Type. Click on the Browse button to select the WSDL that defines this Proxy Service. Obviously, this should be the JukeboxService.wsdl document that was imported a short while ago. In this WSDL document, select the binding or the port and click on OK.

The General Configuration is now done.

Go to the Transport tab. Specify the End Point URI for this web service. The Web Service will be exposed on an end point determined by http://host:port/<End Point URI>/<ProxyService Name> (for example http://superhost:8001/JukeboxService/JukeboxServicePS).

Navigate through the other tabs, to get a feeling for the configuration that can be done for a Proxy Service.

HTTP Transport configuration to deal with HTTP specific settings such as the HTTP transport authentication and the encoding. The defaults are fine for the JukeboxService. The Operation Selection Configuration – to determine how in case of multiple operations in a web service the Proxy Service should find out which operation to invoke. The Message Handling Configuration to configure special treatments for messages – for example for very large messages or messages with attachments. Service Policy Configuration to configure Web Service Policies – for example for security – on the Proxy Service and Message Level Security Configuration.

And finally the interesting part where the actual work will take place in terms of processing, validating, enriching, transforming, routing and forwarding the message: the Message Flow:

At this moment, the message flow is empty because no flow has yet been defined for the Proxy Service.

Before you will do so, let’s first create the Database Adapter configuration that will subsequently provide the foundation for the OSB Business Service that this Proxy Service will call out to.

Configuring the Database Adapter – in SOA Composite Application in JDeveloper

The configuration of Database Adapter services is currently not supported by wizards or editors in the OSB IDE – either in Eclipse or in the Console. The easiest way of adding a JCA Adapter service to an OSB project, is by configuring the adapter in a SOA Application in JDeveloper and then importing the relevant files into the OSB project.

Start up JDeveloper – with the SOA Composite Editor extension installed – and create a new SOA Application. Specify a name for the application, for the project and for the composite. Select the Empty Composite template.

In the Composite Editor, drag the Database Adapter to the References (outbound) swimlane.

Specify the name for the database adapter service: MusicApiSearchForCDs. Press Next.

Select – and if it does not yet exist first create – a [design time] database connection to the database schema that contains the PL/SQL package MUSIC_API. Also specify the JNDI name for the Database Adapter Configuration that you will configure in the target WebLogic Server for connecting through a Data Source to the database schema.

Press Next.

Select Call a Stored Procedure or Function as the Operation Type. Press Next.

Select through browsing the procedure SEARCH_FOR_CDS in the MUSIC_API package.

The wizard identifies the input and output parameters and recognizes the user defined types that describe these parameters.

Accept the defaults in the subsequent pages of the Adapter Configuration wizard and press Finish when that button is first enabled.

The Composite Editor will now display with the outbound Database Adapter service.

More importantly, the adapter configuration wizard has created a number of files that together constitute the definition of the service. These files are the MusicApiSearchForCDs.wsdl, MusicApiSearchForCDs_db.jca and MUSIC_API_SEARCH_FOR_CDs.xsd files.

These files will have to be imported into the OSB project.

Import the Database Adapter configuration into the OSB Project in Eclipse

Back in the Eclipse IDE for OSB, select the option Import on the context menu on the JukeboxService node and select Import from the detail menu.

In the Import dialog window, open the General node and select its File System detail node. Press Next.

Select on the file system the three files named in the previous section that form the definition of the outbound database adapter service MusicApiSearchForCDs.

Make sure that the checkbox Create complete folder structure is unchecked.

Press Finish. The Project Explorer should now list the three files – xsd, jca and wsdl.

Turn JCA adapter configuration into an OSB Business Service

Based on the JCA configuration files, you can now create the OSB Business Service. Open the context menu on the .jca file, open the Oracle Service Bus submenu and select Generate Service.

The Generate JCA Business Service dialog appears. Provide the name for the business service – MusicApiSearchForCDs_db is the perfectly acceptable default, for service name and WSDL name.

Press OK.

The Business Service is now created and its configuration pages are presented to you:

The tab JCA Transport shows the configuration details for the JCA adapter.

At this point, the OSB project can be visualized as follows – with a Business Service that is primed to invoke the PL/SQL API in the Music Database and a Proxy Service that implements the WSDL contract of the JukeboxService. Note that the Proxy Service is not yet connected in any way to the Business Service.

Extending the Message Flow with the Route action to Business Service

The Message Flow in the Proxy Service should be extended to invoke the Business Service MusicApiSearchForCDs that was just configured. Open the Message Flow tab in the Proxy Service editor.

Drag a Route node from the design palette and add it to the message flow. Subsequently, drag a Routing Action and drop it inside the Route node.

Configure the Routing action in the properties palette. Click on Browse button and select the MusicApiSearchForCDs_db.biz Business Service based on the database adapter configuration. Thus you specify that when the message flow executes the Routing action, it invokes the Business Service – that in turn activates the database adapter to invoke the PL/SQL API.

After the Service is selected, you also need to select the specific operation on service that will be invoked. Even though there is only a single operation available in the Business Service, you still need to explicitly select that operation:

Now the connection has been made between the Proxy Service and the Business Service – and indirectly the database procedure. The next figure illustrates the current state of the OSB Service. Note that without transformation, the service will simply route the message that is received from an external consumer directly to the Business Service. Because this message is not in the XML structure that the Database Adapter expects and knows how to map to the user defined input type for the PL/SQL procedure – execution of the service as it stands at this point will fail.

Creating the Transformations

The message that external consumers submit are in the XML structure specified in the service contract – more specifically: these message will contain a CDQueryType structure as defined in the XSD document.

Because the Business Service has stipulated a different type for its input – an InputParameters type as defined in the MusicApiSearchForCDs.xsd file – there needs to be a transformation in the message flow before the Business Service is invoked.

OSB can do transformation in several ways. You can use multiple XPath based manipulation steps for fine grained message manipulation, use XSLT transformation or work with XQuery transformations. OSB more or less favors XQuery to do transformations.

To create the transformation for the request message, select the XQuery Transformation option in the New menu.

Enter the name for the transformation and the folder in which to create the transformation. The name used here is CDQuery_public2private.xq.

Press Next.

Select the Source Type for this transformation. This is the CDQueryType defined in MusicalTypes.xsd contract document. Select the type and press the Add button.

Press Next.

Select the Target Type for this transformation – which is the P_CD_QUERY type in the InputParameters element in the MUSIC_API_SEARCH_FOR_CDS.xsd document – the input for the Business Service. Select the type and click on the Add button.

Press Finish.

A popup may appear, asking you whether you want to open the XQuery Transformation perspective. If that happens, click Yes to open the XQuery editor.

In the XQuery editor, connect the source with the corresponding target elements.

Note: you may need to go into the source tab to manually enter the mapping for the yearSearchValue. The type for this element is xsd:string in the source and xsd:integer in the target structure. The XQuery Mapping tool is strict about these things. You can add the following snippet to the XQuery source:

When done with this transformation, create the transformation for the Response Pipeline. Again, use the New menu to create an XQuery Transformation. Enter the name for the xq file – for example CDCollection_private2public.xq.

Press Next.

Select the Source type for the transformation, which is the OutputParameters element with the P_CD_COLLECTION type that is returned by the Database Adapter, containing the CD records that were retrieved.

Press Next.

Select the CDCollection element (or the CDCollectionType) as the Target type.

Press Finish.

Create the mapping in the visual editor. Again, you may have to use the source tab of the editor to create the mapping for the elements that have mismatching types – using cast as to ensure matching types.

Save all files.

Adding the transformations to the Message Flow

The transformations have been defined. They can now be used in the message flow to connect the outside world to the internal environment.

Open the message flow editor for the Proxy Service. Drag a Replace action to the Request (Action) pipeline inside the Routing action. That is the appropriate place to manipulate the $body variable that contains the request message’s body as sent in by the service consumer and turn it into the structure that the Routing action will forward to the Business Service.

Configure the Replace action in the property palette. You need to indicate that you want the body variable to be manipulated. The contents of the body element should be replaced. The replacing content is created by applying an XQuery transformation to the current contents of the body element.

The steps to go through: enter body in the field In Variable to indicate this variable will be manipulated. Select the radio button Replace node contents to indicate that only the content of the body variable are to be touched. Click on the hyperlink in the Expression field.

The editor that appears allows various types of expressions for message manipulation to be defined. Open the XQuery Resources tab. Press Browse and select the CDQuery_public2private.xq resource – because the body contains the externally published CDQueryType and needs to be transformed to the internal INPUT_PARAMETERS type.

The editor concludes that this transformation resource takes a single input parameter – cDQueryType1 – and lists it as Bind Variable. You now need to specify the expression to get hold of the value for this Bind Variable. The expression used for Binding here is $body/mus:CDQuery – which specifies the node called mus:CDQuery (where mus is a prefix for the namespace stuff.inc/music) in the [predefined OSB] $body variable.

Press OK to close the editor.

Next, click on the expression in the XPath field. You need to specify the expression used to identify the node in the target variable [body in this case] whose content should be replaced. In this case, the entire content of the SOAP Body should be replaced. Therefore: the XPath expression must be defined as . (that is: a single period to indicate the current node).

This completes the configuration for the transformation of the incoming message. Next up is the transformation for the response as received from the business service.

Drag a Replace action from the palette to the Response Action pipeline.

Configure this Replace action is a similar way as the Replace for the Request pipeline. This configuration is little bit more complex because the editor needs to be made aware of the namespace of the element that needs to be transformed.

The steps to go through, again, are: enter body in the field In Variable to indicate this variable will be manipulated. Select the radio button Replace node contents to indicate that only the content of the body variable are to be touched. Click on the hyperlink in the Expression field.

Open the XQuery Resources tab. Press Browse and select the CDCollection_private2public.xq resource. The Bind Variable outputParameters1 is associated with this XQuery transformation. Enter $body/dbmus:OutputParameters as the value for the Binding.

The XPath expression used for the Binding of this variable refers to the OutputParameters element in the $body variable – where this time the body is the response received from the Business Service. However, this element lives in the namespace http://xmlns.oracle.com/pcbpel/adapter/db/MUSIC/MUSIC_API/SEARCH_FOR_CDS/ that is not yet known in this project. In order to make this namespace part of the project, open the Namespace Definitions tab on the right side of the editor. Click on the Add button. Enter dbmus as prefix for this new namespace and enter the URL of the namespace. The press OK. Now the Binding expression will be recognized and validated.

Press OK to close the editor.

Next, click on the expression in the XPath field. Again, the entire content of the SOAP Body should be replaced with the result of the transformation. Therefore: the XPath expression must be defined as . (that is: a single period to indicate the current node).

This completes the definition of the OSB Service JukeboxService. The figure below depicts the service as it is now configured:

Exporting the OSB Project from the IDE and Importing into the OSB Run Time

The JukeboxService has been developed and is ready for execution. The run time for OSB services is the OSB 11g engine that runs on WebLogic Server. OSB services can be deployed directly from the Eclipse IDE, when the run time has been defined as a Server. Alternatively, the deployment can be done via a JAR file that is created in Eclipse and uploaded into the OSB Console. You will use that second route in this case.

Open the context menu on the JukeboxService project node. Open the detail menu Export. Select the option Oracle Service Bus – Configuration Jar.

The Export dialog opens. Select the resources to export: the entire JukeboxService project. Specify the name and location for the jar-file that will be created.

Press Finish.

The jar-file is now created. It is just an archive with all the resources of the service.

Now open the OSB Console in a browser window (http://host:port/sbconsole) and connect with the administrator username and password (for example weblogic/weblogic1). Create a new Change Session in the Change Center (click on the Edit button).

Open the System Administration panel in the menu on the left hand side of the page. Click on the link Import Resources.

Click on the Browse button. Select the jar file that was generated from the Eclipse IDE.

Press Next.

An overview is presented in the console of the contents of the jar-file.

Press the Import button to go ahead with the import of these resources.

When the message is shown that imported was completed successfully, you need to Activate the change session to ‘commit the changes’.

Type a description for the changes wrought by this change and press Submit.

Now the service is deployed to the run time environment and ready for some action.

Testing the JukeboxService from the OSB Console

The OSB Test Console can be used to invoke the Proxy Service and get some information about the internal processing in the message flow.

Open the Resource Browser in the console. Click on the link Proxy Services. Locate the JukeboxServicePS proxy service. Click on the icon Launch Test Console.

The console appears. Provide values for the request message – according to the CDQueryType definition.

Press Execute to submit the request and execute the proxy service.

The result appears – with the list of CDs returned from the MusicAPI:

Very interesting in this test console is the lower part of the page, which contains the Invocation Trace:

The Invocation Trace provides insight in what occurred during the execution of the Proxy Service’s Message Flow. It shows the message as it was received and after it was manipulated. It also shows the message that was returned by the business service, before it was transformed and after transformation. The trace gives access to the transport and message headers as well.

The message flow that the message went through is :

What happened of course involved more than just the message flow; the business service was engaged which means that the database adapter was executed, calling out to the MusicAPI package in the database.

Resources

Download the sources – the JDeveloper application with the Database Adapter service and the Eclipse application with the OSB services as well as the DDL files to create the MusicAPI – from this link: Implementing the Enterprise Service Bus pattern to expose Database backed services_SupportingResources.