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
BTW I guess that the spam protection doesn’t really like my Firefox (I have to login to comment, or I am very bad in counting).
Very nice post. One remark, one question though. Remark, do not create items under the XDB account if you don’t really need to. The XDB schema is / and will be some kind of SYS schema. This means (currently) that in the case of an upgrade of the database or rebuild of the XDB schema/(XMLDB)functionality your procedure will be gone. Question: why transform XML to XML?