Creating XML Data Services for Reporting and ESB integration using DBMS_EPG (the internal HTTP/PLSQL Gateway) and XMLType

Today I all of a sudden found myself investigating the creation op PL/SQL based Data Services, returning XML documents with data from SQL queries to HTTP Clients. The direct motivation behind this investigation was my initial introduction to Oracle BI EE, the use of HTTP Data Sources and my brainstorming on SOA in general and ESB Services in particular. Although the BI EE can directly access Oracle databases, executing its own SQL queries, it feels much better architecturally speaking to have separate, decoupled services query the data and publishing them as service, for example through Http.  In this article I will create such a data service, with minimal effort: leveraging SQL XML and DBMS_EPG, the Oracle 10gR2 embedded PL/SQL Gateway, part of XML DB.

The steps are:

1) create the SQLXML queries that create the desired XML document

2) create a package that returns the XML data through the PL/SQL Web Toolkit (the htp package)

3) Publish the package through the embedded PL/SQL gateway (EPG)

1. create the SQLXML queries that create the desired XML document

The first step is the creation of the actual XML document we want to base the data service on. We will be using Oracle XDB features – XMLType, SQLXML (XMLElement, XMLAgg, XMLForest) and later on Transformation – to get exactly what we want.

Let’s start with the base query: selecting Departments and Employees from the SCOTT schema. My SQLXML query is like this:

select xmlelement( "Departments"
                   , (select xmlagg
                             ( xmlElement
                               ( "Department"
                               , xmlattributes
                                 ( deptno as "departmentNumber"
                                 , dname  as "name"
                                 )
                              , (select xmlagg
                                        ( xmlelement
                                          ("Employee"
                                          , xmlforest(ename as "Name", job as "Function", sal as "Salary")
                                          )
                                        )
                                 from emp
                                 where emp.deptno= dept.deptno
                                )
                              )
                           )
                      from   dept
                     )
                 )  xml
from dual                

The result of running this query:

XML
----------------------------------------------------------------------------------------------------
<Departments><Department departmentNumber="10" name="ACCOUNTING"><Employee><Name>CLARK</Name><Functi
><Salary>2450</Salary></Employee><Employee><Name>KING</Name><Function>PRESIDENT</Function><Salary>50
e><Employee><Name>MILLER</Name><Function>CLERK</Function><Salary>1300</Salary></Employee></Departmen
mentNumber="20" name="RESEARCH"><Employee><Name>SMITH</Name><Function>CLERK</Function><Salary>800</S
ployee><Name>JONES</Name><Function>MANAGER</Function><Salary>2975</Salary></Employee><Employee><Name
on>ANALYST</Function><Salary>3000</Salary></Employee><Employee><Name>ADAMS</Name><Function>CLERK</Fu
/Salary></Employee><Employee><Name>FORD</Name><Function>ANALYST</Function><Salary>3000</Salary></Emp
Department departmentNumber="30" name="SALES"><Employee><Name>ALLEN</Name><Function>SALESMAN</Functi
ary></Employee><Employee><Name>WARD</Name><Function>SALESMAN</Function><Salary>1250</Salary></Employ
ARTIN</Name><Function>SALESMAN</Function><Salary>1250</Salary></Employee><Employee><Name>BLAKE</Name
Function><Salary>2850</Salary></Employee><Employee><Name>TURNER</Name><Function>SALESMAN</Function><
</Employee><Employee><Name>JAMES</Name><Function>CLERK</Function><Salary>950</Salary></Employee></De
 departmentNumber="40" name="OPERATIONS"></Department></Departments>

The next step is that we perform a transformation on this data, to turn the almost raw database structure to the content we want to return from our service. We can perform such an XSLT transformation in the same query that we use to retrieve the base XML document in the first place. The bare bones syntax is:

with document as
( select xmlElement( ....) xml
  ,      xmlType(' <?xml version="1.0" ?>
                   <xsl:stylesheet v.....
  from   dual
)
select xmltype.getstringval( xmltype.transform( xml,xslt))  transformation_outcome
from   document

The first stylesheet I use to transform the XML data is one that produces an HTML document, that presents the data in a more or less marked up format:

<?xml version="1.0" ?>
<xsl:stylesheet version="1.0"
                xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:template match="/Departments">
    <table>
    <xsl:for-each select="Department">
    <tr>
    <td valign="top">
       <xsl:value-of select="@name"/>
    </td>
    <td valign="top">
       <xsl:value-of select="@departmentNumber"/>
    </td>
    <td valign="top">
      <h3>Employees</h3>
      <table>
         <xsl:for-each select="Employee">
           <tr>
             <td><xsl:value-of select="Name"/></td>
             <td><xsl:value-of select="Salary"/></td>
             <td><xsl:value-of select="Function"/></td>
           </tr>
         </xsl:for-each>
      </table>
    </td>
    </tr>
    </xsl:for-each>
    </table>
  </xsl:template>
</xsl:stylesheet>

The result in SQL*Plus looks uneventful:

TRANSFORMATION_OUTCOME
-------------------------------------------------------
<table>
  <tr>
    <td valign="top">ACCOUNTING</td>
    <td valign="top">10</td>
    <td valign="top">
      <h3>Employees</h3>
      <table>
        <tr>
          <td>CLARK</td>
          <td>2450</td>
          <td>MANAGER</td>
        </tr>
        <tr>
          <td>KING</td>
          <td>5000</td>
          <td>PRESIDENT</td>
        </tr>
        <tr>
          <td>MILLER</td>
          <td>1300</td>
          <td>CLERK</td>
        </tr>
      </table>
    </td>
  </tr>
  <tr>
    <td valign="top">RESEARCH</td>
    <td valign="top">20</td>
    <td valign="top">
      <h3>Employees</h3>
      <table>
        <tr>
          <td>SMITH</td>
          <td>800</td>
          <td>CLERK</td>
        </tr>
        <tr>
          <td>JONES</td>
          <td>2975</td>

What we need now is a way to take our XML to the world.

2. Create a package that returns the XML data through the PL/SQL Web Toolkit (the htp package)

The package we need to create to return the transformation result is extremely simple. The important thing to deal with is the fact that the PL/SQL gateway uses the htp buffer to collect the data to be returned in the Http Response, so that is where we have to have our package write the data to. The simples package I can some up with:

create or replace
package hrm_reports
is
procedure dept_emps;

end;
/

create or replace
package body hrm_reports
is
procedure dept_emps
is
  l_html varchar2(32000);
begin
  with document as
  ( select xmlelement( "Departments"
                     , (select xmlagg
                               ( xmlElement
                                 ( "Department"
                                 , xmlattributes
                                   ( deptno as "departmentNumber"
                                   , dname  as "name"
                                   )
                                , (select xmlagg
                                          ( xmlelement
                                            ("Employee"
                                            , xmlforest(ename as "Name", job as "Function", sal as "Salary")
                                            )
                                          )
                                   from emp
                                   where emp.deptno= dept.deptno
                                  )
                                )
                              )
                      from   dept
                      )
                    ) xml
  , XMLType(
            '<?xml version="1.0" ?>
             <xsl:stylesheet version="1.0"
                             xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
               <xsl:template match="/Departments">
                 <table>
                 <xsl:for-each select="Department">
                 <tr>
                 <td valign="top">
                    <xsl:value-of select="@name"/>
                 </td>
                 <td valign="top">
                    <xsl:value-of select="@departmentNumber"/>
                 </td>
                 <td valign="top">
                   <h3>Employees</h3>
                   <table>
                      <xsl:for-each select="Employee">
                        <tr>
                          <td><xsl:value-of select="Name"/></td>
                          <td><xsl:value-of select="Salary"/></td>
                          <td><xsl:value-of select="Function"/></td>
                        </tr>
                      </xsl:for-each>
                   </table>
                 </td>
                 </tr>
                 </xsl:for-each>
                 </table>
               </xsl:template>
             </xsl:stylesheet>') xslt
  from dual
  )
  select xmltype.getstringval( xmltype.transform(xml,xslt))  transformation_outcome
  into l_html
  from document
  ;
  htp.p(' <html>
            <head><title>Demonstration of XMLTYPE, XSLT Transformation, DBMS_EPG internal gateway, XDB</title></head>
            <body>
              <h1>The tables content</h1>'
       ||l_html
       ||'</body></html>');
end;

end;
/

And this is all we need. Of course we can make this parametrized (only return data for certain departments for example). I leave that as an exercise for the reader (I hate it when I read that in a book!)…

3. Publish the package through the embedded PL/SQL gateway (EPG)

Initially the XDB configuration has the FTP and HTTP port disabled (set to 0). To activate a real port, I use the Enterprise Manager Database Control to modify the XDB configuration. Here I have set the HTTP port to 2100. That means that my calls to the EPG will be directed at that port on the host running the database.

The next step is creating a DAD (Database Access Descriptor). This must be done as a user with the XDBADMIN role, for example XDB himself:

BEGIN
  DBMS_EPG.create_dad
  ( dad_name => 'hrm'
  , path => '/hrm/*'
  );
END;
/

Next is authorizing this DAD for the package that in my case resides in the SCOTT schema. I connect as SCOTT and authorize the DAD:

begin
  DBMS_EPG.AUTHORIZE_DAD('hrm');
end;
/

At this point I should be able to access my package through the following url: http://localhost:2100/hrm/scott.hrm_reports.dept_emps.

I can also create a procedure that returns proper XML data instead of markup language, as we have seen here. Using the same SQL XML query, all I need is a different stylesheet. This stylesheet looks like this:

<?xml version="1.0" ?>
<xsl:stylesheet version="1.0"
                xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:template match="/Departments">
    <Divisions>
    <xsl:for-each select="Department">
      <Division>
        <Name>
          <xsl:value-of select="@name"/>
        </Name>
        <DivId>
         <xsl:value-of select="@departmentNumber"/>
        </DivId>
        <Employees>
          <xsl:for-each select="Employee">
            <Employee>
              <Name><xsl:value-of select="Name"/></Name>
              <Salary><xsl:value-of select="Salary"/></Salary>
              <Position><xsl:value-of select="Function"/></Position>
            </Employee>
          </xsl:for-each>
        </Employees>
      </Division>
    </xsl:for-each>
    </Divisions>
  </xsl:template>
</xsl:stylesheet>

I have created a new procedure dept_emps_xml that is almost identical to dept_emps, except that it uses this stylesheet. Accessing this procedure from the browser returns a proper XML document:

Resources

Roland Bouman on Webenabling OracleXE – You can, Free of cost http://rpbouman.blogspot.com/2006/02/webenabling-oraclexe-you-can-free-of.html

Building Database-Driven PHP Applications on Oracle XML DB, by Yuli Vasiliev http://www.oracle.com/technology/pub/articles/vasiliev_xmldb_php.html

Oracle 10gR2 Types and packages documentation: DBMS_EPG http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_epg.htm#sthref2715