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:

create or replace type hrm_emp_obj_t as object
( empno number(6,0)
, name  varchar2(50)
)
/

create or replace type hrm_emp_tab_t as table of hrm_emp_obj_t
/

create or replace type hrm_dept_emp_obj_t as object
( deptno     number(4,0)
, name       varchar2(30)
, location   varchar2(30)
, employees  hrm_emp_tab_t
)
/

create or replace type hrm_dept_emp_tab_t as table of hrm_dept_emp_obj_t
/

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

create or replace type hrm_service_msg_obj_t as object
 (code        varchar2(15)
 ,description varchar2(4000)
 ,type        varchar2(1)
 ,constructor function hrm_service_msg_obj_t
  return self as result
 )
/

create or replace type body hrm_service_msg_obj_t
is

  constructor hrm_service_msg_obj_t
    return self is result
  is
  begin
    null;
  end;
end;
/

create or replace type hrm_service_messages_tab_t as table of hrm_service_msg_obj_t
/

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

Package Specification:

create or replace package hrm_emp_pck
is
  --
  function get_employee_list( p_deptno        in  departments.department_id%type default null
                            , p_dept_emp_list out hrm_dept_emp_tab_t)
    return hrm_service_status_obj_t;
  --
end hrm_emp_pck;

Package Body:

create or replace package body hrm_emp_pck
is
--
  e_not_exists_dept exception;

  function get_employee_list( p_deptno        in  departments.department_id%type default null
                            , p_dept_emp_list out hrm_dept_emp_tab_t)
    return hrm_service_status_obj_t
  is
    cursor c_dept(b_deptno in departments.department_id%type)
    is
      select d.department_id
           , d.department_name
           , l.city
      from   departments d
           , locations l
      where  d.department_id = b_deptno
      and    l.location_id = d.location_id
      order by d.department_id;
    r_dept          c_dept%rowtype;
    l_dept_emp_list hrm_dept_emp_tab_t;
    l_dept_emp_rec  hrm_dept_emp_obj_t;
    l_emp_list      hrm_emp_tab_t;
    l_dept_exists   boolean;
    l_program       varchar2(30) := 'hrm_emp_pck.get_employee_list';
  begin
    hrm_service_msg_pck.set_pu_context(l_program);

    -- check for mandatory arguments
    if p_deptno is null
    then
      raise hrm_service_msg_pck.e_no_input_args;
    end if;

    -- check if supplied department number exists
    open c_dept(p_deptno);
    fetch c_dept into r_dept;
    l_dept_exists := c_dept%found;
    close c_dept;
    if not l_dept_exists
    then
      raise e_not_exists_dept;
    end if;

    l_dept_emp_list := hrm_dept_emp_tab_t();
    for r_dept in c_dept(p_deptno)
    loop
      l_emp_list := hrm_emp_tab_t();
      select hrm_emp_obj_t(emp.employee_id
                          ,emp.last_name||', '||emp.first_name) employee
      bulk collect into l_emp_list
      from  employees emp
      where emp.department_id = r_dept.department_id
      order by emp.employee_id;

      l_dept_emp_rec := hrm_dept_emp_obj_t(r_dept.department_id
                                          ,r_dept.department_name
                                          ,r_dept.city
                                          ,l_emp_list);
      l_dept_emp_list.extend;
      l_dept_emp_list(l_dept_emp_list.last) := l_dept_emp_rec;
    end loop;

    p_dept_emp_list := l_dept_emp_list;

    hrm_service_msg_pck.set_status_ok;
    return hrm_service_msg_pck.g_status;
  exception
    when e_not_exists_dept
    then
      hrm_service_msg_pck.set_status_to_error('HRM-00001', 'Department number does not exists');
      return hrm_service_msg_pck.g_status;
    when hrm_service_msg_pck.e_no_input_args
    then
      hrm_service_msg_pck.set_status_no_input_args;
      return hrm_service_msg_pck.g_status;
    when others
    then
      hrm_service_msg_pck.set_status_errors;
      return hrm_service_msg_pck.g_status;
  end get_employee_list;
  --
end hrm_emp_pck;

And the supporting package HRM_SERVICE_MSG_PCK:

Package Specification:

create or replace package hrm_service_msg_pck is
   -- user-defined exceptions
   e_no_input_args    exception;
    -- Public variables
   g_status        hrm_service_status_obj_t;
   g_error         varchar2(32000);
   g_program_unit  varchar2(100);
    -- Public program units
   procedure set_pu_context(p_program_unit in varchar2);
   procedure set_status_ok;
   procedure set_status_errors;
   procedure set_status_no_input_args;
   procedure set_status_to_error ( p_msg_code in varchar2
                                 , p_msg_text in varchar2
                                 );
  end hrm_service_msg_pck;

Package Body:

create or replace package body hrm_service_msg_pck
is

  procedure set_pu_context(p_program_unit in varchar2)
  is
  begin
    g_program_unit := p_program_unit;
  end set_pu_context;

  procedure determine_errors (p_messages in out nocopy hrm_service_messages_tab_t)
  is
  begin
    p_messages.extend;
    p_messages(1) := hrm_service_msg_obj_t ( code        => 'SRV-99999'
                                           , description => substr('Unexpected error: '||
                                                                   dbms_utility.format_error_stack,1,4000)
                                           , type        => 'F'
                                           );
  end determine_errors;

  procedure set_status_ok
  is
  begin
    g_status := hrm_service_status_obj_t ();
    g_status.result := 'OK';
    g_status.program_unit := hrm_service_msg_pck.g_program_unit;
    g_status.messages := hrm_service_messages_tab_t
                         ( hrm_service_msg_obj_t ( code        => 'SRV-00000'
                                                 , description => 'Succesfully executed'
                                                 , type        => 'I'
                                                 ));
  end set_status_ok;

  procedure set_status_errors
  is
  begin
    g_status.result := 'NOT OK';
    g_status.program_unit := hrm_service_msg_pck.g_program_unit;
    determine_errors(g_status.messages);
  end set_status_errors;

  procedure set_status_no_input_args
  is
  begin
    g_status := hrm_service_status_obj_t ();
    g_status.result := 'NOT OK';
    g_status.program_unit := hrm_service_msg_pck.g_program_unit;
    g_status.messages := hrm_service_messages_tab_t
                         ( hrm_service_msg_obj_t ( code        => 'SRV-00001'
                                                 , description => 'One or more mandatory arguments missing'
                                                 , type        => 'F'
                                                 ));
  end set_status_no_input_args;

  procedure set_status_to_error ( p_msg_code in varchar2
                                , p_msg_text in varchar2
                                )
  is
  begin
    g_status.result := 'NOT OK';
    g_status.program_unit := hrm_service_msg_pck.g_program_unit;
    g_status.messages := hrm_service_messages_tab_t
                         ( hrm_service_msg_obj_t ( code        => p_msg_code
                                                 , description => p_msg_text
                                                 , type        => 'F'
                                                 ));
  end set_status_to_error;

end hrm_service_msg_pck;

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:

<status>
  <result>NOT OK</result>
  <programUnit>hrm_emp_pck.get_employee_list</programUnit>
  <messages>
    <message>
      <code>SRV-00001</code>
      <description>One or more mandatory arguments missing</description>
      <type>F</type>
    </message>
  </messages>
</status>

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:

<?xml version= '1.0' encoding= 'UTF-8' ?>
<wsdl:definitions
     name="employeelistbydepartment"
     targetNamespace="www.amis.nl/services/hrm"
     xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/"
     xmlns:hrm="www.amis.nl/services/hrm"
     xmlns:tns="www.amis.nl/services/hrm"
    >
    <wsdl:types>
      <schema xmlns="http://www.w3.org/2001/XMLSchema" >
        <import namespace="www.amis.nl/services/hrm" schemaLocation="xsd/employeelistbydepartment.xsd" />
      </schema>
    </wsdl:types>
    <wsdl:message name="getEmployeeListByDepartmentRequest">
        <wsdl:part name="request" element="hrm:employeeListRequest"/>
    </wsdl:message>
    <wsdl:message name="getEmployeeListByDepartmentResponse">
        <wsdl:part name="reply" element="hrm:employeeListResponse"/>
    </wsdl:message>
    <wsdl:portType name="employeelistbydepartment_ptt">
        <wsdl:operation name="getEmployeeListByDepartment">
            <wsdl:input message="tns:getEmployeeListByDepartmentRequest"/>
            <wsdl:output message="tns:getEmployeeListByDepartmentResponse"/>
        </wsdl:operation>
    </wsdl:portType>
</wsdl:definitions>

And the XML Schema files:

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

<?xml version= '1.0' encoding= 'UTF-8' ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns="www.amis.nl/services/hrm"
            targetNamespace="www.amis.nl/services/hrm"
            elementFormDefault="qualified" xmlns:hrm="www.amis.nl/hrm">
    <xsd:import namespace="www.amis.nl/hrm" schemaLocation="hrm.xsd"/>
    <xsd:element name="employeeListRequest">
        <xsd:complexType>
            <xsd:sequence>
                <xsd:element name="departmentIdentifier" type="xsd:int"/>
            </xsd:sequence>
        </xsd:complexType>
    </xsd:element>
    <xsd:element name="employeeListResponse">
        <xsd:complexType>
            <xsd:sequence>
                <xsd:element name="status" type="hrm:statusType" minOccurs="1" maxOccurs="1"/>
                <xsd:element name="departments" type="hrm:departmentsType" minOccurs="0" maxOccurs="1"/>
            </xsd:sequence>
        </xsd:complexType>
    </xsd:element>
</xsd:schema>

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

<?xml version= '1.0' encoding= 'UTF-8' ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns="www.amis.nl/services/hrm"
            targetNamespace="www.amis.nl/hrm"
            elementFormDefault="qualified"
            xmlns:hrm="www.amis.nl/hrm">
    <xsd:complexType name="departmentsType">
        <xsd:sequence minOccurs="1" maxOccurs="unbounded">
            <xsd:element name="department" type="hrm:departmentType"/>
        </xsd:sequence>
    </xsd:complexType>
    <xsd:complexType name="departmentType">
        <xsd:sequence>
            <xsd:element name="id" type="xsd:integer"/>
            <xsd:element name="name" type="xsd:string"/>
            <xsd:element name="location" type="xsd:string"/>
            <xsd:element name="employees">
                <xsd:complexType>
                    <xsd:sequence>
                        <xsd:element name="employee" type="hrm:employeeType"
                                     minOccurs="0" maxOccurs="unbounded"/>
                    </xsd:sequence>
                </xsd:complexType>
            </xsd:element>
        </xsd:sequence>
    </xsd:complexType>
    <xsd:complexType name="employeeType">
        <xsd:sequence>
            <xsd:element name="personelNo" type="xsd:integer"/>
            <xsd:element name="name" type="xsd:string"/>
        </xsd:sequence>
    </xsd:complexType>
</xsd:schema>

XML Schema for the service message (service.xsd)

<?xml version= '1.0' encoding= 'UTF-8' ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns="www.amis.nl/services/hrm" targetNamespace="www.amis.nl/hrm"
            elementFormDefault="qualified" xmlns:hrm="www.amis.nl/hrm">
    <xsd:complexType name="statusType">
        <xsd:sequence>
            <xsd:element name="result" type="xsd:string" minOccurs="1" maxOccurs="1"/>
            <xsd:element name="programUnit" type="xsd:string" minOccurs="1" maxOccurs="1"/>
            <xsd:element name="messages" type="tns:messagesType" minOccurs="1" maxOccurs="1"/>
        </xsd:sequence>
    </xsd:complexType>
    <xsd:complexType name="messagesType">
        <xsd:sequence minOccurs="1" maxOccurs="unbounded">
            <xsd:element name="message" type="tns:messageType"/>
        </xsd:sequence>
    </xsd:complexType>
    <xsd:complexType name="messageType">
        <xsd:sequence>
            <xsd:element name="code" type="xsd:string"/>
            <xsd:element name="description" type="xsd:string"/>
            <xsd:element name="type" type="xsd:string"/>
        </xsd:sequence>
    </xsd:complexType>
</xsd:schema>

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.