DB Adapter returning more than just results: status and messages

When working with the DB Adapter within the Oracle SOA Suite (or Oracle Service Bus) you may feel the need to return more than just the result of your call to the database. When calling you web service normally you get a normal SOAP response or a SOAP fault. But even when you get an normal SOAP response, it is possible that this is not the expected behavior and you need to know if, for instance, you supplied enough input arguments in your web service request.

In that case you need more than just the results, you need status (OK/NOT OK) and one or more messages indicating what went wrong if the result was not ok.

In this tutorial I will show you an example of how how can implement this within the Oracle SOA Suite (and because the DB Adapter can also be used within the Oracle Service Bus, the principles also apply to implementing it within the OSB).

First of all we will use object types within the database for communicating with the SOA Suite. Using object types is quite easy in PL/SQL and is natively supported by the DB Adapter (converting it to XML automatically).

Prerequisite

the tutorial is done using the HR demo schema and I assume that the DB Adapter Connection Pools and JDBC Data Source within the Weblogic Server are already in place for connecting to the HR database schema in your database.

The service we will create will provide a list of employees within a selected department.

PL/SQL

We start by creating the object types for this service:

Because we like to return some status information, we create some more object types which will hold the information we need:

Next we create the package with the function which will be called by the DB Adapter (HRM_EMP_PCK):

Package Specification:

Package Body:

And the supporting package HRM_SERVICE_MSG_PCK:

Package Specification:

Package Body:

It may seem at lot at first, but the service object types and hrm_services_msg_pck package will be reused for every new service function you write in PL/SQL.

Let look at the hrm_emp_pck.get_employee_list function.

You’ll see two argument: the first is the department id to look for and the second is the result of the search (if all goes well the list of employees within the selected department). And as return type: hrm_service_status_obj_t. This object type will hold the additional information you want, being the status (OK or NOT OK) and any messages describing a functional error or technical database error (the latter being ORA-messages).

This status object will, when called using the DB Adapter (and after transformation to the right format), results in a XML structure like this:

The hrm_service_msg_pck package now contains only some basic functions, but you can extend it with, for instance, support for returning Headstart messages (if you use this in the application the services are for) or for more default error handling. Within this tutorial the service objects and package are created within the application schema, but if you plan to use it for real, then I suggest you implement this within a dedicated schema for handling (common) service functionality.

SOA Suite

To finish the tutorial and make it actually work, we start JDeveloper 11g and create a new SOA Application and project.

Enter a name for your application, e.g. HRServices

Enter a name for your project, e.g. GetEmployeeList

Select as Composite Template: Empty Composite.

Press Finish.

Before we continue, we first need to add (create) some files to the project: the WSDL file which defines the service we want to create and the accompanying XML Schema (XSD) files.

The creating of the WSDL and XML Schema files (mostly done in the reverse order) is something you will do for every new service you built. You start with either the declaration of the objecttypes and PL/SQL package functions or with the XML Schema and WSDL. By the way, you do not need to have actual PL/SQL code when creating the DB Adapter (you only need the correct objecttypes and function specification). But for making it work you need the code, of course.

Add the WSDL file to the project root folder and the XML Schema (XSD) files to the xsd subfolder.

The WSDL (web service description) (employeelistbydepartment.wsdl) for our service:

And the XML Schema files:

XML Schema for service request and response types (employeelistbydepartment.xsd)

XML Schema for the result (list of employees within a department) (hrm.xsd)

XML Schema for the service message (service.xsd)

Now drag and drop a DB Adapter service to the External References lanes inside the composite.xml.

Enter a name for the DB Adapter Service, e.g. hrmGetEmployeeList

In step 3 select or add a database connection to the HR Schema. This database connection is only used at design time and provide the means to select the appropriate database package function. Enter at JNDI Name the name of the JNDI Data Source as recorded in the Weblogic server, e.g. eis/DB/HR

Press Next.

Because we want to call a database package function, we select as operation type: Call a Stored Procedure or Function.

Press Next.

In step 5 press the Browse button to select the HRM_EMP_PCK.GET_EMPLOYEE_LIST function.

Press OK.

Press Next.

Press Next.

Press Finish.

You will see that 3 new files are added to the project: a WSDL file and two XML Schema files.

Next add a Mediator component by drag and drop it on the Components lane.

Enter a name for the Mediator, e.g. GetEmployeeListMediator

Select as Template: Interface Definition from WSDL.

And select the WSDL file we created earlier: employeelistbydepartment.wsdl

Check the check box ‘Create Composite Service with Bindings’. This will ensure that the service is exposed to the outside world by the Mediator.

Press OK.

Connect the Mediator with the DB Adapter:

Double click on the Mediator component and click on the transformation icon in the first routing rule (to).

Select Create New Mapper File, accept the default name and press OK.

Create the mapping and save the file.

Return to the Mediator editor and click on the transformation icon in the second routing rule (from).

Select Create New Mapper File, accept the default name and press OK.

Create the mapping and save the file.

Save all files.

Now you are ready (assuming you have configured Weblogic).

Deploy and test

Deploy the service to the application server and test if it works.

Call the web service with:

• No department id
• An existing department id (e.g. 10 or 100)
• A not existing department id (e.g. 77)

You can use the SOA console for testing or soapui.

Note that the first test can not be performed within the SOA console (only in soapUI) because it marks the request parameter as mandatory

The results in the SOA console:

Department id = 100:

Department id = 77:

With no department id (in soapUI):

I hope this tutorial has given you some insight how you can return status and messages back from the database to the web service. And if needed you can add functionality in your web service when a certain status and/or messages are returned from the DB Adapter, e.g. for raising a predefined SOAP Fault or call other services depending on the result. For this you need to use BPEL or, when in the Oracle Service Bus, within a proxy service.