23 December 2010

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

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.

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.

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


  1. I agree that use of XML type for exposing your stored procedure to outside world is always depend upon situation. In some situation it works well while in other it didn't. You explained this concept very well and make the task of developers easy.

  2. i can't feel situations where i must prefer type instead of xml =/
    why you can not cast "any" to xml and parse as need ?

    1. Of course that's possible, but when you're working with a Mediator (like in the examples above) it is easier to create a mapping when the full structure is visible.
      If you have to work in the mapper and only this (3.bp.blogspot.com/_tdqazgf19_s/TRNFDXN8bdI/AAAAAAAAA7E/3o4Wb5UsLU0/s1600/XmlTypeMapping.png) is visible, you have no idea what the structure looks like.
      Then you would have to write a lot of XSLT expressions by hand, instead of using Drag-and-Drop

    2. so it's all about visibility ? ( btw you can investigate xml structure by db code )
      but that about performance ? did you check it ?
      imho it's interesting comparison

    3. Yes, all about visibility.Not sure what you mean by "investigate xml structure by db code"

    4. i meant that you can open database package/function/procedure and analyze possible xml structure

      imho interesting point is extensibility
      if you have type then you have hard coded structure and some modification ( add element in type ) lead to regenerate code of dbadapter ( in particular xsd of dbadapter)
      but if you have returned xml ( may be clob ) then you can modify ( as example add element ) xml as needed without regenerating dbadapter
      it's like parseXml(returnedClobAfterInvokeDBAdapter)/someElement

      looks like i prefer flexibility instead of visibility

      thanks for the post and time for rethink of reterned type

    5. True. Downside of this flexibility is that you need to write a lot more code by hand instead of drag-and-drop. The complete transformation needs to be hand-written. Of course the choice is yours :)