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.
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.
- Creating an XMLType based on Object Types
- Oracle & JDBC – Passing a 'table of custom object type' to a stored procedure – implementing efficient single round trip data exchange (part two of an ADF application on a PL/SQL API)
- Oracle SQL and PL/SQL: juggling with user defined types (ADT) and XMLType – for creating an ADT based XML Service API
- XMLType and DBMS_OUTPUT
- Creating XML Data Services for Reporting and ESB integration using DBMS_EPG (the internal HTTP/PLSQL Gateway) and XMLType
This entry was posted by Alex Nuijten on December 23, 2010 at 12:34 pm, and is filed under Database, Devel. + PL/SQL tools, Oracle, Oracle Development Tools, SOA & Oracle Fusion Middleware. Follow any responses to this post through RSS 2.0.You can skip to the end and leave a response. Pinging is currently not allowed.