Should you expose a Stored Procedure via XMLType?

When you want to expose your Stored Procedure to the “outside world”, should you do this using XMLType? As always, the correct answer is “it depends”.
For this example I will use a Package containing two functions, the signature of the package is as follows:

create or replace package emps_pkg
is
   function get_department_xml (p_department_no in number)
      return xmltype;

   function get_departement_ot (p_department_no in number)
      return all_departments_ot;

end emps_pkg;

One function returns an XMLType (named get_department_xml) and the other returns an Object Type (named get_department_ot). They both return data from the EMPLOYEES and DEPARTEMENTS tables in the HR schema.
The implementation of the package body is based on this blogpost.

With a database adapter in the SOA Suite you can expose each of these functions. To be really useful you will need to add a mediator.

Two Mediators each with a connection to the database adapter

On the right hand side, in the above image, the database adapters are shown. The purple things are the mediators where the actual mapping takes place (XSLT).

The biggest difference between the two implementations can be found in the Mapper file.

Transformation based on XMLType

As you can see in the image above, the functions returns something. That “something” could basically be anything. Not a whole lot of information.

Using the Object Type as a returntype from the function makes a big difference.

Transformation based on Object Type

Here you can see the complete structure of the Object Type (which is a quite complex Object Type). Makes Mapping a lot easier.