0 
0 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.
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.
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,
Lucas
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…