Hands-on: Run-time datasource selection with JCA adapters in the Oracle Service Bus 11g

The Oracle JCA database adapter enables the communication between a web service and a database object, for instance a stored procedure. With the adapter configuration wizard, part of JDeveloper 11g, meta data is generated design time and used run-time to control the behaviour of the database adapter. In this hands-on we show how to set run-time the database  by overriding the design time selected database in the Oracle Service Bus 11g.

The hands-on uses a simple proxy service based on a “Hello world” example. As input the service uses a name and a number as identification of the database. The output is something like “Hello John”. To prove we really “hit” the database, the output is also written to a table.

In the message flow which connects the proxy service and business service,  we change the transport header by setting the jca.db.DataSourceName property. The business service is based on the database adapter. And the database adapter is based on the Hello procedure. This procedure builds and returns the greeting string and as a side effect inserts the greeting string into a table called log.

Seen from the Oracle Service Bus, it doesn’t matter if we use identical schemes in several databases or we use identical schemes in the same database (except the schema name, say demo1 and demo2). In both cases the connection information is hidden by the JDBC name and JNDI name. The Weblogic server and the Oracle Service Bus use logical names only.

The following YouTube movie demonstrates in soapUI the run-time datasource selection in three parts:
A. Set up: Purging log tables (step A1 and A2)
B. Do the real thing; call the service twice, once for each datasource (step B1 and B2)
C. Show the log tables (step C1 and C2)

Step by step instructions are divided in the following sections

1. Database setup
2. Weblogic server setup
3. Adapter Configuration
4. Workshop activities (Eclipse)

Database setup

Create the users and user objects in, for instance, a XE database.

• Login as system in SQL*Plus
• Create two users demo1 and demo2

create user demo1 identified by demo1 default tablespace users temporary tablespace temp;
grant connect, resource to demo1;

create user demo2 identified by demo2 default tablespace users temporary tablespace temp;
grant connect, resource to demo2;

• Create the objects for each user, login as demo1
• Create the table and the procedure

create table log(text varchar2(200));

create or replace  procedure hello(
    name IN VARCHAR2,
    greeting OUT VARCHAR2)
  as
  begin
    greeting := USER ||' says ''Hello ' || name ||'!''';
    insert into log(text )values(greeting);
  end;
/

• Create the same objects for user demo2

Weblogic server setup

Configure the datasource and adapter connection factory in the Weblogic server.

• Create a datasource jdbc/demo1 for the demo1@XE schema and an adapter connection factory eis/DB/demo1 according the instructions in Adding an Adapter Connection Factory.
• Create a datasource jdbc/demo2 for the demo2@XE schema.

Adapter configuration

Use the Adapter Configuration Wizard in JDeveloper to configure the adapter.

• Follow the instructions as described in Using Top-Level Standalone API’s as part of the Complete Walkthrough of the Adapter Configuration Wizard
  • Give the service a name: Hello
  • In the step Connecting to a database create a connection to the XE database and make sure the JNDI name is eis/DB/demo1.
  • Select the procedure hello and leave the schema name empty.
• the Adapter Configuration Wizard generates three files:
  • Hello.wsdl
  • Hello_db.jca
  • DEMO1_HELLO.xsd (in the xsd subdirectory)
• If you prefer all these files in one directory, move the DEMO1_HELLO.xsd file one level up and adjust the schemaLocation in the Hello.wsdl.

Workshop activities (Eclipse)

Define the following services in the Eclipse workshop (after creating an Oracle Service Bus Configuration Project and an Oracle Service Bus Project called Demo).

• A business service based upon the database adapter.
• A proxy service based upon the business service.

Define the business service

We use the files generated in the Adapter configuration to generate a business service.

• Copy the three files in the Eclipse project directory and refresh the project.
• Select the Hello_db.jca file. Right mouse button click and navigate to Oracle Service Bus/generate Service.
• A business service Hello_db.biz based on the Hello_db.wsdl is created.

Define the proxy service

Create a proxy service HelloService based on the following WSDL:

<wsdl:definitions name="helloService"
    targetNamespace="http://www.amis.nl/HelloService/" xmlns:tns="http://www.amis.nl/HelloService/"
    xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <wsdl:types>
        <xsd:schema targetNamespace="http://www.amis.nl/HelloService/"
            elementFormDefault="qualified">
            <xsd:element name="InputParameters">
                <xsd:complexType>
                    <xsd:sequence>
                        <xsd:element name="Name" type="xsd:string" />
                        <xsd:element name="Database" type="xsd:int" />
                    </xsd:sequence>
                </xsd:complexType>
            </xsd:element>
            <xsd:element name="OutputParameters">
                <xsd:complexType>
                    <xsd:sequence>
                        <xsd:element name="Greeting" type="xsd:string" />
                    </xsd:sequence>
                </xsd:complexType>
            </xsd:element>
        </xsd:schema>
    </wsdl:types>
    <wsdl:message name="args_in_msg">
        <wsdl:part name="InputParameters" element="tns:InputParameters" />
    </wsdl:message>
    <wsdl:message name="args_out_msg">
        <wsdl:part name="OutputParameters" element="tns:OutputParameters" />
    </wsdl:message>
    <wsdl:portType name="HelloService_ptt">
        <wsdl:operation name="HelloService">
            <wsdl:input message="tns:args_in_msg" />
            <wsdl:output message="tns:args_out_msg" />
        </wsdl:operation>
    </wsdl:portType>
    <wsdl:binding name="HelloServiceSoapBinding" type="tns:HelloService_ptt">
        <soap:binding style="document"
            transport="http://schemas.xmlsoap.org/soap/http" />
        <wsdl:operation name="HelloService">
            <soap:operation soapAction="HelloService" />
            <wsdl:input>
                <soap:body use="literal" />
            </wsdl:input>
            <wsdl:output>
                <soap:body use="literal" />
            </wsdl:output>
        </wsdl:operation>
    </wsdl:binding>
</wsdl:definitions>

Use the message flow to connect the proxy service with the business service.

• Add a Route node.
• Add a Routing action to the Route node. The service of the Routing action is Hello_db.biz.
• Add a Transport Headers action to the request pipeline of the Routing action.
  • Set the direction to Outbound Request
  • In the name column Set Defined to jca and select jca.db.XADatabaseSourceName from the select list.
  • In the action column select Set Header to and enter the following expression fn:concat('jdbc/demo',string($body/hel:InputParameters/hel:Database/text())).
• Add a Replace action after Transport Headers action in the request pipeline.
  • Set the XPath to ./*
  • Set the In Variable to body
  • Select Replace entire node
  • Set the Expression to (the dba namespace in the expression refers to the target namespace defined in DEMO1_HELLO.xsd)

<dba:InputParameters>
     <dba:NAME>{$body/hel:InputParameters/hel:Name/text()}</dba:NAME>
</dba:InputParameters>

• Add a Replace action in the response pipeline.
  • Set the XPath to ./*
  • Set the In Variable to body
  • Select Replace entire node
  • Set the Expression to

<hel:OutputParameters>
       <hel:Greeting>{$body/dba:OutputParameters/dba:GREETING/text()}</hel:Greeting>
</hel:OutputParameters>