While developing APEX apps, on multiple occasions I’ve needed to generate data-driven artifacts for external use. My app lets staff manage the data that drives their organization, but for historical reasons a related public-facing website can’t be replaced in the near term. The site is usually static files or was built years before on a different tech stack like LAMP (Linux, Apache, MySQL, PHP). This article walks through a simple example of generating all the HTML files for a static website based on database data. It uses a declarative “transform group” capability I wrote for myself to combine the declarative power of SQL, XML, and XSLT to generate and download a zip file of generated artifacts. See the README file that accompanies this article’s sample app to learn more about how transform groups can be applied in your own APEX apps.
Background Motivation
One APEX app I built as a “nerd volunteer” for a non-profit in Italy required generating:
- HTML pages for a conference schedule in a particular format
- SQL scripts to update another system’s MySQL database
- JSON files to import into an auth provider’s user management console
- PHP source code files to “drop in” to an online portal site
For example, the conference schedule data shown in the APEX app screenshot in the banner above, turns into a static HTML file to display the parallel tracks of the conference program like this:
A Favorite Technology Trio: SQL + XML + XSLT
For anyone who may have read my O’Reilly book Building Oracle XML Applications published in October 2000, it should come as no surprise that I find the combination of SQL, XML, and XSLT stylesheets very useful. Your mileage may vary, but over the intervening years I have generated many data-driven artifacts using this trio of technologies. It only made sense that I’d reach for them again as APEX became my tool of choice for building new applications over the past few years.
While each distinct task of generating data-driven artifacts is slightly different, the high-level similarities shared by all tasks I’ve had to implement involve:
- A SQL query to produce XML-formatted system-of-record data
- One or more XSLT stylesheets to transform the XML data into appropriate text-based artifact files
The Oracle database natively supports generating XML from any SQL query results and transforming XML using XSLT (version 1.0) stylesheets. After learning the APEX_ZIP package makes it easy to generate zip files, I devised a generic facility to use in my current and future APEX apps called “transform groups”.
Defining a Transform Group
I use an XML file to declaratively define the “interesting bits” that make each transform task unique, and include this file along with the XSLT stylesheets required to generate the artifacts as static application files in my APEX app. My “transform group processor” package interprets the transform group file and processes the data transforms in it to download a single zipfile containing all the results. This way, with a single click my apps can produce and download all necessary generated artifacts.
For example, consider the following basic transform group definition file. It defines a single data transform whose SQL query retrieves the rows from the familiar DEPT table and uses the home-page.xsl stylesheet to produce the index.html home page of a hypothetical company directory website.
<!-- generate-hr-site.xml: Transform Group definition file -->
<transform-group directory="hr-site">
<data-transform>
<query>
select deptno, dname, loc
from dept
</query>
<transformation stylesheet="home-page.xsl"
output-file-name="index.html"/>
</data-transform>
</transform-group>The in-memory XML document representing the results of the data transform’s query looks like this:
<ROWSET>
<ROW>
<DEPTNO>10</DEPTNO>
<DNAME>ACCOUNTING</DNAME>
<LOC>NEW YORK</LOC>
</ROW>
<!-- etc. -->
<ROW>
<DEPTNO>40</DEPTNO>
<DNAME>OPERATIONS</DNAME>
<LOC>BOSTON</LOC>
</ROW>
</ROWSET>The XSLT stylesheet referenced in the <transformation> element looks like the example below. It contains a single template that matches the root of the XML document above, then uses the <xsl:for-each> to loop over all the <ROW> elements to format a bulleted list of departments.
<!-- home-page.xsl: XSLT stylesheet to generate home page -->
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="html"/>
<xsl:template match="/">
<html>
<body>
<h1>Departments</h1>
<ul>
<xsl:for-each select="/ROWSET/ROW">
<li>
<xsl:value-of select="DNAME"/>
(<xsl:value-of select="LOC"/>)
</li>
</xsl:for-each>
</ul>
</body>
</html>
</xsl:template>
</xsl:stylesheet>When viewed in a web browser, the index.html file it produces will looks like this:
After saving the transform group XML file and corresponding XSLT stylesheet to our static application files, we’re ready to wire up the transform group download.
Wiring Up a Transform Group Download Button
You’ll typically call the transform group’s PL/SQL API from an APEX application process with a process point of “Ajax Callback”. For example, if we create an application process named Download_Static_Website, its code will invoke the download() procedure like this:
eba_demo_transform_group.download(
p_file_name => 'generate-hr-site.xml',
p_zipfile_name => 'hr-website.zip');A button or link in your APEX app can initiate the transform group zipfile download. Its link target will redirect to the current page and have the Request parameter in the Advanced section of the link definition set to APPLICATION_PROCESS=Download_Static_Website
With these elements in place, clicking on the button will download an hr-website.zip file containing a single hr-site/index.html file.
Enhancing the Transform Group Definition
A data transform can process the same SQL query results using multiple, different stylesheets. XSLT stylesheets can accept parameters to influence their output, and the data transform supports an optional <parameter-query> element whose SQL query can return rows to provide values of these XSLT parameters. The column-to-parameter-map attribute defines how a column in the parameter query’s value should map to the name of an XSLT parameter. The transform group processor runs the stylesheet once for each row in the parameter query’s results. On each iteration it maps the XSLT parameter to the corresponding column value in the current parameter query row and runs the transformation to produce an output file. Notice that the output-file-name attribute can also reference parameter names as part of the file name.
<!-- generate-hr-site.xml: Transform Group definition file -->
<transform-group directory="hr-site">
<data-transform>
<query>
select deptno, dname, loc
from dept
</query>
<transformation stylesheet="home-page.xsl"
output-file-name="index.html"/>
<transformation stylesheet="dept-page.xsl"
output-file-name="dept_{#depid#}.html">
<parameter-query column-to-parameter-map
="DEPTNO:depid">
select deptno from dept
</parameter-query>
</transformation>
</data-transform>
</transform-group>To generate a page for each employee in each department, we can further enhance the transform group to include a nested set of EMP table rows for each DEPT table row, and add a third <transformation> element to generate the employee pages.
<!-- generate-hr-site.xml: Transform Group definition file -->
<transform-group directory="hr-site">
<data-transform>
<query>
select deptno, dname, loc,
cursor( select empno, ename, job
from emp
where deptno = d.deptno) as staff
from dept d
</query>
<transformation stylesheet="home-page.xsl"
output-file-name="index.html"/>
<transformation stylesheet="dept-page.xsl"
output-file-name="dept_{#depid#}.html">
<parameter-query column-to-parameter-map
="DEPTNO:depid">
select deptno from dept
</parameter-query>
</transformation>
<transformation stylesheet="emp-page.xsl"
output-file-name="emp_{#empid#}.html">
<parameter-query column-to-parameter-map
="DEPTNO:depid,EMPNO:empid">
select empno, deptno from emp
</parameter-query>
</transformation>
</data-transform>
</transform-group>Next we make appropriate updates to home-page.xsl to generate hyperlinked department names and upload the dept-page.xsl and emp-page.xsl stylesheets to our static application files. After this, clicking on the button now downloads the entire company directory static website in the hr-website.zip file. It contains an hr-site top-level directory with all the generated HTML pages as shown in the Finder screenshot below after extracting the zip file contents.
hr-website.zip fileExploring the Sample App
The APEX 22.2 sample application you can download from here installs the eba_demo_transform_group package and includes a working example of the declaratively-generated website download explained in this article. After importing the sample and ensuring you have the EMP/DEPT sample dataset installed, just click the Generate and Download Static Site button. The sample has tabs to easily explore the syntax of the transform group XML file and accompanying XSLT stylesheets, too. Its README page provides more tips on how transform groups might be useful to your own APEX apps in the future.
Since XSLT transformations are great at declaratively generating text-based artifacts of any kind, hopefully I won’t be the only APEX developer to benefit from the productivity the transform group functionality offers.
Dive Into APEX 