Oracle 11g SOA Suite Service-enable a Query Using the Database Adapter – Execute Pure SQL Option

The Database Adapter is a powerful tool to expose data and PL/SQL code in your service applications. If you want to quickly service-enable a custom-made query, you definitely may want to explore the “Execute Pure SQL” option of the Database Adapter.

This tutorial will show how to service-enable a query step by step using the Database Adapter with Pure SQL.

Business Case: Retrieving Department Information

Assume you have written a query that retrieves information for departments in your database and you want to service-enable this query. The query optionally accepts two arguments: a department ID and a department name. Selection on a part of the department name is possible and selection is case-insensitive. If the arguments are both empty, all departments will be returned by the query.

The query we are talking about here does not only contain columns that are mapped 1-on-1 on database columns but also a concatenation expression and calculated columns using analytical functions. Therefore the “Execute Pure SQL” option of the Database Adapter is a logical choice to expose this custom-made query.

The query we are going to use in this tutorial can be found below:

select distinct
d.department_id as department_id
, d.department_name as department_name
, d.manager_id as manager_id
, m.first_name || ' ' || m.last_name as manager_name
, count(e.employee_id) over (partition by d.department_id) as employee_count
, round(avg(e.salary) over (partition by d.department_id)) as avg_salary
, l.location_id as location_id
, l.street_address as street_address
, l.postal_code as postal_code
, l.city as city
, l.state_province as state_province
, l.country_id as country_code
from departments d
, locations l
, employees m
, employees e
, ( select :p_deptno as deptno
, :p_deptname as deptname
from dual
) p
where d.location_id = l.location_id(+)
and d.manager_id = m.employee_id(+)
and d.department_id = e.department_id(+)
and ( ( p.deptno is not null
and d.department_id = p.deptno )
or p.deptno is null
)
and ( ( p.deptname is not null
and upper(d.department_name) like upper('%'|| p.deptname ||'%') )
or p.deptname is null
)
;

Prerequisites

This tutorial uses the HR demo schema. The HR schema is locked by default. Make sure it is unlocked before you start this tutorial.

Tutorial

1.       Start JDeveloper 11g.

2.       Create a new SOA Application by choosing menu option “File” > “New…”.

3.       From category “General” > “Applications”, choose “SOA Application” and click “OK”.

4.       When prompted for the application name, enter “DeptDemoServices”. Press “Next”.

5.       When prompted for the project name, enter “DeptDemoProject”. Press “Next”.

6.       When prompted for a composite name, enter “DeptDemoServices” again.
Make sure that the “Empty Composite” is selected. Press “Finish”.

7.       Drag an instance of the Database Adapter to the External References swimlane.
This will launch the Adapter Configuration Wizard.

8.       When prompted for the Service Name, enter “SelectDepartments”. Press Next.

9.       Page “Service Connection” lets you define the connection that will be used by JDeveloper during design-time and the JNDI name for the database that will be used when the service is deployed. Click the green plus icon to define the design-time connection.

10.   For “Connection Name”, enter “HR”.
Fill out the username and password for the HR schema.
Fill out the Oracle JDBC Settings for the database connection. In my case, the database is running on localhost with “XE” as SID and 1521 as JDBC Port.
Make sure you test your connection before pressing “OK”.

11.   Our Service Connection is now defined as you can see in the screenshot below.
Note: in our case the JNDI Name is correct after defining the design-time connection. You may have to change this value into the JNDI Name that is defined on your WebLogic server that has access to the HR schema before pressing “Next”.

12.   Select “Execute Pure SQL” as the operation type. This will give us the opportunity to enter our self-written SQL statement for which we want the results to be published as a service.
Press “Next” to continue configuration of the Database Adapter.

13.   Copy the SQL statement below and paste it into upper text box labeled “SQL:”.
Notice that the binds are prefixed with a pound sign (#) instead of a colon (:).

select distinct
d.department_id as department_id
,      d.department_name as department_name
,      d.manager_id as manager_id
,      m.first_name || ' ' || m.last_name as manager_name
,      count(e.employee_id) over (partition by d.department_id) as employee_count
,      round(avg(e.salary) over (partition by d.department_id)) as avg_salary
,      l.location_id as location_id
,      l.street_address as street_address
,      l.postal_code as postal_code
,      l.city as city
,      l.state_province as state_province
,      l.country_id as country_code
from   departments d
,      locations   l
,      employees   m
,      employees   e
,      ( select #p_deptno as deptno
,      #p_deptname as deptname
from   dual
)  p
where  d.location_id = l.location_id(+)
and    d.manager_id = m.employee_id(+)
and    d.department_id = e.department_id(+)
and    ( (     p.deptno is not null
and d.department_id = p.deptno )
or p.deptno is null
)
and    ( (     p.deptname is not null
and upper(d.department_name) like upper('%'|| p.deptname ||'%') )
or p.deptname is null
)

14.   As a result of the previous action, an XSD is generated in the lower text box. Unfortunately there are a couple of mistakes in this XSD. For some reason the number of output elements is not correct. Furthermore, we want the input elements to be optional, so we add attribute minOccurs=”0” to the input elements.
To correct the XSD, copy the XSD from below and replace the generated XSD.
Press “Next” when you are done.

<?xml version = '1.0' encoding = 'UTF-8'?>
<xs:schema targetNamespace="http://xmlns.oracle.com/pcbpel/adapter/db/SelectDepartments"
xmlns="http://xmlns.oracle.com/pcbpel/adapter/db/SelectDepartments"
elementFormDefault="qualified" attributeFormDefault="qualified"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="SelectDepartmentsInput" type="SelectDepartmentsInput"/>
<xs:complexType name="SelectDepartmentsInput">
<xs:sequence>
<xs:element name="p_deptno" type="xs:decimal" minOccurs="0" nillable="true"/>
<xs:element name="p_deptname" type="xs:string" minOccurs="0" nillable="true"/>
</xs:sequence>
</xs:complexType>
<xs:element name="SelectDepartmentsOutputCollection" type="SelectDepartmentsOutputCollection"/>
<xs:complexType name="SelectDepartmentsOutputCollection">
<xs:sequence>
<xs:element name="SelectDepartmentsOutput" type="SelectDepartmentsOutput" minOccurs="0" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="SelectDepartmentsOutput">
<xs:sequence>
<xs:element name="department_id" type="xs:decimal" nillable="true"/>
<xs:element name="department_name" type="xs:string" nillable="true"/>
<xs:element name="manager_id" type="xs:decimal" nillable="true"/>
<xs:element name="manager_name" type="xs:string" nillable="true"/>
<xs:element name="employee_count" type="xs:decimal" nillable="true"/>
<xs:element name="avg_salary" type="xs:decimal" nillable="true"/>
<xs:element name="location_id" type="xs:decimal" nillable="true"/>
<xs:element name="street_address" type="xs:string" nillable="true"/>
<xs:element name="postal_code" type="xs:string" nillable="true"/>
<xs:element name="city" type="xs:string" nillable="true"/>
<xs:element name="state_province" type="xs:string" nillable="true"/>
<xs:element name="country_code" type="xs:string" nillable="true"/>
</xs:sequence>
</xs:complexType>
</xs:schema>

15.   Accept the defaults on page “Advanced Options” and press “Next”.
You will arrive at the last page of the Database Adapter Configuration Wizard.
Press “Finish”.

16.   The Database Adapter has been added to the composite. You will also see three new files in your project in the project tree on the left of your screen:

• SelectDepartments.xsd: contains the XSD you corrected earlier
• SelectDepartments_db.jca: contains the SQL statement you entered earlier and the connection information
• SelectDepartments.wsdl: the WSDL that publishes the Database Adapter as a service

17. The next step will be to add a Web Service component to the composite to be able to publish the Database Adapter to the outside world. You could reuse the XSD that was created for the Database Adapter for the Web Service component. That would be a quick-and-dirty solution though. The names used in the XSD may be too technical for the consumer of our web service or may not be allowed because they violate the naming conventions for the project.

The easy way to create a new XSD is to open the XSD that was created for the Database Adapter and change the names and types of the elements and type. Let’s do this and save the XSD under a new name: “DepartmentInfo”.

To make life easier, we have done this already for you.
Copy the XSD below and replace the XSD that you just saved as “DepartmentInfo”. If you see a diagram instead of the source of the XSD, press the “Source” tab at the bottom of the diagram.

<?xml version = '1.0' encoding = 'UTF-8'?>
<xs:schema targetNamespace="http://xmlns.oracle.com/pcbpel/adapter/db/DepartmentsInfo"
xmlns="http://xmlns.oracle.com/pcbpel/adapter/db/DepartmentsInfo"
elementFormDefault="qualified" attributeFormDefault="qualified"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="departmentsQuery" type="departmentsQuery"/>
<xs:complexType name="departmentsQuery">
<xs:sequence>
<xs:element name="departmentID" type="xs:decimal" minOccurs="0" nillable="true"/>
<xs:element name="departmentName" type="xs:string" minOccurs="0" nillable="true"/>
</xs:sequence>
</xs:complexType>
<xs:element name="departments" type="departments"/>
<xs:complexType name="departments">
<xs:sequence>
<xs:element name="department" type="department" minOccurs="0" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="department">
<xs:sequence>
<xs:element name="departmentID" type="xs:decimal" nillable="true"/>
<xs:element name="departmentName" type="xs:string" nillable="true"/>
<xs:element name="managerID" type="xs:decimal" nillable="true"/>
<xs:element name="managerName" type="xs:string" nillable="true"/>
<xs:element name="employeeCount" type="xs:decimal" nillable="true"/>
<xs:element name="averageSalary" type="xs:decimal" nillable="true"/>
<xs:element name="locationID" type="xs:decimal" nillable="true"/>
<xs:element name="streetAddress" type="xs:string" nillable="true"/>
<xs:element name="postalCode" type="xs:string" nillable="true"/>
<xs:element name="city" type="xs:string" nillable="true"/>
<xs:element name="stateProvince" type="xs:string" nillable="true"/>
<xs:element name="countryCode" type="xs:string" nillable="true"/>
</xs:sequence>
</xs:complexType>
</xs:schema>

18.   Save and close the XSDs.

19.   Drag a Web Service from the Component Palette to the Exposed Services swimlane of the composite. With this component we will define how the consumer can interact with the service.

20.   Enter “DepartmentInfo” as the name of the Web Service.

21.   The next step is generating a WSDL that will be associated with the Web Service component.
The generated WSDL will also be the WSDL that a consumer will use to interact with the service. We will generate the WSDL from XSD “DeparmentInfo.xsd” we created earlier.
Click on the gear icon that can be found to the right of the field labeled WSDL URL.

22.   First we will define the schema element on which the request message will be based.
Click on the magnifying glass icon next the field labeled “URL”.

23.   Select the “departmentsQuery” type from the “DepartmentInfo.xsd” and click “OK”.

24.   Click on tab page “Reply” and again click on the magnifying glass icon to define the schema element on which the reply message will be based.

25.   This time, select the “departments” type from the “DepartmentInfo.xsd” and click “OK”.

26.   Click “OK” to dismiss the “Create WSDL” dialog. The file “DepartmentInfo.wsdl” has been added to your project and can be found on the left in the Application Navigator.
Again, click “OK” to create the Web Service component for your composite.
Save all changes to your project.

27.   The next step in this tutorial is too connect the Web Service component with the Database Adapter using a Mediator. Let’s add a Mediator to the composite by dragging one from the Component Palette between the Web Service and the Database Adapter.

28.   Accept the default name and template and press “OK”.

29.   Connect the Web Service with the Mediator.
Do the same with the Mediator and the Database Adapter.

30.   The end result of the previous step will look like this:

31.   Now it is time to define the routing rules for the Mediator.
Double click on the Mediator to start editing.

32.   Press the icon next to the first “Transform Using” drop-down list to define the mapping from the Web Service to the Database Adapter.

33.   Click the radio button labelled “Create New Mapper File”.
Accept the default filename for the new XSL file and press “OK”.

34.   Define the mapping by connecting “inp1:departmentID” with “db:p_deptno” and “inp1:departmentName” with “db:p_deptname”. Do this by clicking the element on the left and dragging to the element on the right of the screen.
When the “Auto Map Preferences” dialog appears, make sure “Mode” is set to “Basic” , and click “OK”.
The end result looks like this when you are done.

35.   Close the mapper file and save your files when prompted.

36.   Now click on the icon next to the second “Transform Using” drop-down list so we can define the mapping for the synchronous reply.

37.   Click the radio button labelled “Create New Mapper File”.
Accept the default filename for the new XSL file and press “OK”.

38.   Unfortunately the Auto Mapper cannot be trusted to connect the child nodes in one go by connecting the parent nodes from left to right. Therefore we are going to create the mapping for the nodes ourselves.

First, right-click node “inp1:department” to open the context menu and click on
“Add XSL Node” > “for-each”.

39.   Connect the “for-each” node to node “db:SelectDepartmentOutput” by clicking and dragging from right to left.

40.   Expand node “db:SelectDepartmentsOutput” and connect the elements that represent the output column from left to right. When the “Auto Map Preferences” dialog appears, make sure “Mode” is set to “Basic” , and click “OK”.
The end result looks like this when you are done.

41.   Close the mapper file and save your changes when prompted.

42.   Close the Mediator definition as well.

43.   The definition of the composite is now complete.
Save all files and deploy the composite application.

44.   Now it is time to test the service. We will do this from using Fusion Middleware Enterprise Manager. If you have it installed locally, the URL will look something like this:
http://localhost:7001/em

45.   Lookup the composite application you deployed earlier in the Enterprise Manager.
Click on the application and press the “Test” button.

46.   You have arrived on the “Test Web Service” page.
Notice the URL to the WSDL here in case you want to create test suites with other tools like soapUI.

47.   Scroll down to the bottom of the page. Here you can enter input arguments for the service.
Try a few scenarios here:

• Leaving both arguments empty select info for every department in the database
• Enter only a departmentID and leave departmentName empty. For example: 20
• Enter only a (part of a) department name and leave departmentID empty.
  For example: sales
  An example of the response for this scenario is displayed below

This concludes the tutorial for the Database Adapter using the “Execute Pure SQL” option.