Should you expose a Stored Procedure via XMLType?

Alex Nuijten 2

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
   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.

2 thoughts on “Should you expose a Stored Procedure via XMLType?

  1. Hi Alex,

    Thanks for this clear example. I have one additional question: in the introduction to this article, you write ‘it depends’. Having read your article, I still wonder what it depends on. From your article it would seem that using the Object Type approach would always give me a nicer, clearer structure to work with in the mapper. Is there a downside to this? When would using the XMLType return type (and the anyType in XML) be more appropriate?

    kind regards,


  2. Be aware that using XSLT (transformation) and using object types (mapping structure) are two different objectives/methods… The object type structure is in this example the equivalent of what you achieve while using an XML Schema…

Comments are closed.

Next Post

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 […]
%d bloggers like this: