Simplify Sharing Region Data

I’ve frequently found it useful to share one region’s data in another. For example, as a user narrows her results with a faceted search or smart filter, a chart, map or dynamic content region shows the same results in a different way. My colleague Carsten explains the technique in his Add a Chart to your Faceted Search Page blog post, and it has come in handy for me many times. But after the fifth pipelined table function and object types I authored to enable the data sharing, I set out to automate the process to save myself time in the future. As an APEX developer, the ability to create development productivity apps for yourself is a cool super power!

The Region Data Sharing Helper app featured in this article lets you pick a region from any app in your workspace and easily download scripts to share that region’s results in another region in the same application. After first explaining how to use the app, I highlight some interesting details of its implementation.


The app lets you select regions for which to generate data sharing artifacts. Since data sharing requires both data and a unique region identifier, the app only shows regions with a static id having a source location of Local Database, REST Enabled SQL, or REST Source. After adding a region to the selected list for artifact generation, if needed you can adjust the columns to include and the base name. Then, you can download the generated artifacts for that region. By incorporating the SQL scripts into your APEX app, you can configure additional regions in the same app to use the original one’s data using the SELECT statement provided in the accompanying README file.

The app maintains the list of selected regions to remember the subset of columns you configure and the base name of the generated artifacts you prefer for each region. If you later update one of the original regions in the App Builder in a way that affects its columns, just click the (Regenerate) button to refresh its datasource info and download the artifacts again.

Choosing a Region

As shown in the figure below, choose a region whose data you want to share with other regions in the same app. Click (Add Region) to add it to the list of selected regions below.

Region Data Sharing Helper app showing Employees region in HR Sample app

Sometimes the selected region is immediately ready for artifact generation, but other times it may take a few seconds to show a status of Ready. If you see a status of Working, as shown below, wait 10 seconds and click the refresh icon to see if it’s ready yet.

Click (Refresh) after 10 seconds if status shows as Working

Once the status of a selected region is Ready, you can adjust the base name and included columns and save any changes. If you’ve modified the original region in your app in a way that affects its data source columns, click Regenerate to refresh the set of available columns to choose from.

Adjusting the included columns and base name as necessary

Once you’ve saved any changes, the download button will reappear. Click it to get a zip file containing the generated SQL scripts and a README file explaining what they are and how to use them.

Clicking the download button to produce the data sharing artifacts

When you no longer anticipate needing to download data sharing artifacts for a selected region, you can remove it from the list of selected ones by clicking the Delete button. This simply removes it from the helper app’s list of selected regions. You can always add it again later as a selected region if the need arises.

Exploring the Downloaded Artifacts

After clicking the download button for the Employees region on page 1 of the HR Sample application shown above, since the Base Name was employees a zip file named is downloaded. Extracting the zip file reveals three generated files as shown below.

Mac finder showing the contents of the downloaded zip file

The README.html is the place to start, since it explains the other two files.

README.html file explains the generated artifacts and suggests SELECT statement to use

The employees_types.sql script defines the employees_row and employees_tab types used by the pipelined table function in the other file. The employees_function.sql defines the employees_data pipelined table function and depends on the types. You can include these scripts directly into your APEX application as Supporting Objects scripts, or add them to the set of your existing installation scripts. Due to the function’s dependency on the types, however, just ensure that the types script is sequenced before the function script.

For example, incorporating the generated SQL scripts into the HR Sample app above as supporting objects scripts would look like this:

Supporting Objects scripts after adding the two data sharing SQL files

Using Pipelined Table Function in a Region

The README file contains a suggested SELECT statement to use as the source of the region where you want to reuse the original region’s data. After running the types SQL script then running the function SQL script, you can try the suggested statement in another region in the same HR Sample application. In the example below, I’ve used it as the source of a chart region on the same page as the original faceted search region.

Using the SELECT statement suggested in the README file in another region in the same app

The query I’ve used appears below, and of course I could leave out columns that are not necessary in the new region. For simplicity, I used the statement verbatim as I found it in the README file:

select ename,
  from table(employees_data)

After also configuring a dynamic action on the After Refresh event of the Employees region to declaratively refresh the Salaries chart region, we immediately see the data sharing working in action, reflecting the filtering the current user performs in the original region, too.

HR Sample app showing generated data-sharing artifacts in use to reflect filtered data in a chart

The rest of the article explains some interesting details of the Region Data Sharing Helper app implementation. If you’re primarily interested in trying out the functionality, you’ll find the download link at the end.

Cascading Select Lists for App, Page, Region

An extremely handy consequence of Oracle APEX’s model-driven architecture is that all application metadata is queryable using SQL. By using the APEX dictionary views, it’s incredibly easy to create new APEX applications that introspect application definitions and provide new value. In the case of the Region Data Sharing Helper app, I needed three select lists to let the user choose the application, page, and region for artifact generation. The query for the P1_APPLICATION select list page item appears below. It uses appropriate where clauses to avoid showing itself in the list and to only show applications that have at least one region with a non-null static id configured and a local database, remote database, or REST service data source.

select a.application_name||' ('||a.application_id||')' as name,
from apex_applications a
where a.application_id != :APP_ID
and exists (select null
              from apex_application_page_regions r
             where r.application_id = a.application_id
               and r.static_id is not null
               and r.location in ('Local Database',
                                  'Remote Database',
                                  'Web Source'))
order by a.application_name

The query for the P1_PAGE select list page item is similar, retrieving only those pages in the selected application having some qualifying region. Notice how the value of P1_APPLICATION is referenced as a bind variable in the WHERE clause:

select page_name||' ('||page_id||')' as name, page_id
from apex_application_pages p
where p.application_id = :P1_APPLICATION
and p.page_function not in ('Global Page','Login')
and exists (select null
              from apex_application_page_regions r
             where r.application_id = p.application_id
               and r.page_id = p.page_id
               and r.static_id is not null
               and r.location in ('Local Database',
                                  'Remote Database',
                                  'Web Source'))
order by p.page_id

By simply mentioning P1_APPLICATION in the Parent Items(s) property of the P1_PAGE select list, the APEX engine automatically handles the cascading behavior. When the user changes the value of P1_APPLICATION, the value of P1_PAGE is reset to null, or its default value if it defines one. It also implicitly submits the value of any parent items to the server when the select list’s query needs refreshing on parent value change. To save the user a click, I’ve defined the default value for P1_PAGE using a SQL query to retrieve the id of the first page in the available list of pages.

The P1_REGION select list page item uses a similar query against the apex_application_page_regions view, listing P1_PAGE as its parent item and providing an appropriate query for the item’s default value to automatically choose the first region in the list whenever the list gets reset by the cascading select list interaction.

Adding Chosen Region to Selected List

When you choose a region and click the (Add Region) button to add it to the list selected for artifact generation, the Add Region to Selected List page process runs. It uses the built-in Invoke API action to call the add_region_to_selected_list() function in the eba_demo_region_data_sharing package. If it’s the first time this combination of app id, page id, and region static id has been added, it inserts a new row in the eba_demo_reg_data_requests table to remember the user’s selection. Then it proceeds to describe the “shape” of the region’s data source: the names and data types of its columns. That info will be recorded in the xml_describe column in this row by a background job. I reveal next why a background job was required…

Describing a Region’s Data Source Profile

No dictionary view provides the names and data types of a region’s datasource in a way that works for all kinds of data-backed regions, so I had to think outside the box. I applied a meta-flavored twist on Carsten’s data-sharing strategy and created a pipelined table function get_region_source_columns() to programmatically fetch the region datasource column metadata I needed using the following approach:

  1. Use apex_region.open_context() on the region in question
  2. Retrieve the column count using apex_exec.get_column_count()
  3. Loop through the columns to discover the name and data type of each
  4. For each one, call pipe row to deliver a row of region column metadata

The complication I encountered was that apex_region.open_context() only works on regions in the current application. However, when the Region Data Sharing Helper app is running, it is the current app in the APEX session. I needed a way to momentarily change the current application to the one containing the region to describe.

I first tried using an APEX automation to run the region describe process in the background. I hoped a call to apex_session.create_session() inside the job could establish the appropriate “current app” context before using apex_region.open_context() to describe the region. However, I discovered the APEX engine already establishes the APEX session for the background automation job, and my attempt to change it to another app id didn’t produce the desired effect.

Carsten suggested trying a one-time DBMS Scheduler job where my code would be the first to establish an APEX session without bumping into the built-in session initialization. Of course, his idea worked great! Things went swimmingly from there. The code I use inside add_region_to_selected_list() to run the DBMS Scheduler one-time background job looks like this:

-- Submit one-time dbms_scheduler job to process the
-- request to describe the region in some app in the
-- workspace other than the current utility app
dbms_scheduler.create_job (
    job_name        => dbms_scheduler.generate_job_name,
    job_type        => 'plsql_block',
    job_action      => replace(c_gen_xml_job_plsql,c_id_token,l_id),
    start_date      => systimestamp,
    enabled         => true,
    auto_drop => true);

The PL/SQL block submitted to the scheduler comes from the c_gen_xml_job_plsql string constant whose value appears below, after substituting the #ID# token with the primary key of the row in eba_demo_reg_data_requests representing the region that needs describing:


When the background job runs describe_region(12345), that procedure retrieves the application id, page id, and region id from the eba_demo_reg_data_requests table using the id provided, calls create_apex_session_with_nls() to establish the right application context, then calls the xml_for_sql() function in my eba_demo_transform_group package to retrieve an XML document that represents the query results from the following query against the region metadata pipelined table function:

select * 
  from eba_demo_region_data_sharing.get_region_source_columns(

It then updates the row in eba_reg_data_requests to assign this XML region column profile as the value of its xml_describe column. This XML document will have the following format:

  <!-- etc. -->

If it’s the first time we’re describing this region, it also assigns a default value to the include_columns column to reflect that all columns are selected by default. The situation when it’s not the first time we’re performing the region describe has an interesting twist I explain later when we explore regenerating the artifacts for a region.

Forms with Previous/Next Navigation

The two user-editable fields in the eba_demo_reg_data_requests row are basename and include_columns . The former represents the base name that will be used to generate the name of the object type (basename_row), the collection type (basename_tab), and the pipelined table function (basename_data). The latter is a stored as a colon-separated list of included column positions, relative to the positional order they appear in the xml_describe data profile XML document. Since you can add multiple regions to the selected list, I wanted to let the user page forward and backward through those selected entries.

To implement that row navigation, I learned a new trick by reading an article by my colleage Jeff Kemp. It revealed a neat feature of the APEX Form region that supports easy paging through an ordered set of rows. You configure it with a combination of settings on the form region itself, as well as on its Form – Initialization process in the Pre-Rendering section of the page.

On the form region, you set the data source and make sure to impose a sort order. That’s important to establish a predictable next/previous ordering for the rows the user navigates. For example, in the helper app the form region’s source is the local table eba_demo_reg_data_requests with an Order By Clause of generate_requested_on desc . This ensures the user sees the requests in most recently generated order.

The other half of the setup involves the Form – Initialization process. As shown below, after creating three page items with in-memory-only storage to hold their values, you configure the Next Primary Key Item(s), Previous Primary Key Item(s), and Current Row/Total Item with the respective names of the page items.

Form Initialization process settings for next/previous navigation

Informed by the form region’s source and sort order, along with these navigation related settings, the APEX engine automatically computes the values of these page items when the page renders. I left the P1_REQUEST_COUNT visible on the form as a display only page item so the user can see she’s on region “3 of 5”. I made the other two page items hidden, but referenced their value as appropriate in the Handle Previous and Handle Next branches I configured in my After Processing section of my page’s Processing tab.

I chose to handle the navigation buttons with a Submit Page action combined with branches so the form’s Automatic Row Processing (DML) process would save any changes the user made on the current page before proceeding to the next or previous one. If the form had been read-only, or I didn’t want to save the changes on navigation, the values of P1_NEXT_REQUEST_ID and P1_PREVIOUS_REQUEST_ID could also be referenced as page number targets in buttons that redirect to another page in the current application. Lastly, I referenced these page item values again in the server-side conditions of the NEXT and PREVIOUS buttons so that they only display when relevant.

Using Transform Group to Generate Artifacts

The artifact generation and download is handled declaratively using a transform group, a capability I explain in more detail in a previous blog post. For generating the region data sharing artifacts to be downloaded in a single zip file, I added the following generate-data-sharing-artifacts.xml static application file. It includes a single data transform whose parameterized query retrieves the region’s column names and data types, filtered by the developer’s choice of columns to include in the generated artifacts. The SELECT statement uses the xmltable() function to query the region’s data profile stored in the xml_describe column. This offered me a chance to learn about the for ordinality clause to retrieve the sequential position of the <ROW> elements that xmltable() turns into relational rows. This made it easy to combine with the apex_string.split() function to retrieve only the columns whose sequential position appears in the colon-separated list of include_columns values.

<transform-group directory="{#basename#}-region-data-sharing-artifacts">
        <query bind-var-names="id">
            select, x.ddl_name, x.data_type, x.declared_size
            from eba_demo_reg_data_requests r,
            xmltable('/ROWSET/ROW' passing r.xml_describe
                        seq           for ordinality,
                        name          varchar2(255) path 'NAME',
                        ddl_name      varchar2(80)  path 'DDL_NAME',
                        data_type     varchar2(80)  path 'DATA_TYPE',
                        declared_size number        path 'DECLARED_SIZE'
            ) x
            where = to_number(:id)
            and x.seq in (select to_number(column_value)
                        from apex_string.split(r.include_columns,':'))
            order by x.seq
        <transformation stylesheet="generate-types.xsl" 
        <transformation stylesheet="generate-function.xsl" 
        <transformation stylesheet="generate-readme.xsl" 

The transform group includes three transformations that each use an appropriate XSLT stylesheet to transform the region data profile information into a SQL script defining the object types, a SQL script defining the pipelined table function, and a README.html file.

Replacing Strings in XSLT 1.0 Stylesheets

XSLT 2.0 has a replace() function that works like Oracle’s regexp_replace(), but the Oracle database’s native XSLT processor implements only the XSLT 1.0 feature set. Therefore, we need an alternative to perform string substitution in a stylesheet that generates an artifact by replacing tokens in a template.

For example, the generate-readme.xsl stylesheet in the helper app defines a variable named query-template with an example of the SQL query you’ll use to select data from the pipelined table function. This template contains a token #COLUMNS# that we’ll replace with the comma-separated list of selected column names. It also has #FUNCNAME# token we’ll replace with the name of the pipelined table function.

<xsl:variable name="query-template">select #COLUMNS#
  from table(#FUNCNAME#)</xsl:variable>

After first computing the value of the variable columns by using an <xsl:for-each> to loop over the names of the selected columns, the stylesheet performs the double token substitution while defining the example-query variable. If we were able to use XSLT 2.0, the example-query variable definition would look like this:

<!-- XSLT 2.0 string replace example -->
<xsl:variable name="example-query"

However, as mentioned above we need to limit our stylesheets to functionality available in XSLT 1.0 to use the native Oracle database XSLT processor. Instead, we use nested calls to a named template replace-string. Think of a named XSLT template like a function that accepts parameters as input and returns an output. So, the following example-query variable declaration calls the replace-string named template to replace the token #FUNCNAME# in the value of the stylesheet variable query-template with the value of the stylesheet variable named function-name:

<!-- Partial solution, replace first #FUNCNAME# token -->
<xsl:variable name="example-query">
   <xsl:call-template name="replace-string">
     <xsl:with-param name="text" select="$query-template"/>
     <xsl:with-param name="replace">#FUNCNAME#</xsl:with-param>
     <xsl:with-param name="with" select="$function-name"/>

But the result of the above would be the query template with only the #FUNCNAME# token replaced, leaving the #COLUMNS# token intact. XSLT variables are immutable: once assigned their value cannot be updated. So we are not allowed to create multiple, consecutive <xsl:variable> statements that update the value of the same example-query variable, replacing one token at a time. Instead, XSLT relies on nested calls to the replace-string function while performing the initial (and only allowed) assignment of the example-query variable. So after calling the replace-string template once to replace #FUNCNAME# with the value of $function-name, we use that result as the value of the input text parameter in a second, outer call to replace-string to swap #COLUMNS# with the value of $columns like this:

<!-- Final solution, replace #FUNCNAME#, then #COLUMNS# -->
<xsl:variable name="example-query">
  <xsl:call-template name="replace-string">
    <xsl:with-param name="text">
      <xsl:call-template name="replace-string">
        <xsl:with-param name="text" select="$query-template"/>
        <xsl:with-param name="replace">#FUNCNAME#</xsl:with-param>
        <xsl:with-param name="with" select="$function-name"/>
    <xsl:with-param name="replace">#COLUMNS#</xsl:with-param>
    <xsl:with-param name="with" select="$columns"/>

The generate-types.xsl and generate-function.xsl stylesheets perform this same nested invocation of replace-string, but have more tokens to substitute. As expected, this results in more deeply-nested calls. However, the concept is the same as this two-token example from generate-readme.xsl.

Vertically Centering the Add Region Button

When a button appears in the same row of a form as other page items, by default its vertical alignment with respect to its “row mates” doesn’t look as eye-pleasing as it could.

A button’s default vertical alignment in a row with other page items

The trick to improve the button’s visual appeal, is to add the CSS class u-align-self-center to the Column CSS Classes property in the Page Designer like this:

Using u-align-self-center to vertically center button with page items in the same row

Show Buttons Based on Row Existence

I wanted the user to see an (Add Region) button if the region they choose is not yet in the selected list, and instead see a (Regenerate) button if the region is already in the list. And I wanted the correct button to show both when the page initially renders, as well as when the user changes the values of the select list interactively. I implemented this feature using a dynamic action with conditional hide and show action steps based on an existence check query.

As shown below, I started by using drag and drop in the Page Designer’s Layout editor to drag the (Regenerate) button into the same grid cell as the (Add Region) button. Since the user will see only one or the other at a time, they both can occupy that same grid cell just to the right of the P1_REGION select list.

Two buttons stacked in the same grid cell since the user will see only one or the other at runtime

Next, I added a dynamic action on the Change event of the P1_REGION page item. Recall that in the helper app, being in the selected list means that a row exists in the eba_demo_reg_data_requests table with the region’s unique combination of application id, page id, and region static id. The first action step in the dynamic action event handler uses Execute Server-side Code to run the following query that always returns a row with either ‘Y‘ or ‘N‘ into the hidden P1_REGION_IN_SELECTED_LIST page item. This provides the info about whether the region exists in the list or not.

with region_in_selected_list as (
    select max(id) as id 
      from eba_demo_reg_data_requests
     where app_id = :P1_APPLICATION
       and page_id = :P1_PAGE
       and region_static_id = :P1_REGION
select case 
         when is null then 'N' 
         else 'Y' end
from region_in_selected_list x;

Then I followed that action step with four conditional steps that use a client-side condition based on the value Y or N to hide the button that needs hiding and show the button that needs showing. Notice how the new action step name can be very useful in making the steps self-documenting with a more descriptive label than the old “Hide” or “Show” that appeared before 22.2.

Dynamic action on P1_REGION change event to hide/show appropriate buttons

To finish the job, I set the Fire on Initialization switch to true for the four Hide/Show action steps, and provided the same existence SQL query as the default value of the P1_REGION_IN_SELECTED_LIST hidden page item. This ensured that the correct button shows both during the initial page render, as well as after the user interactively changes the region select list.

To Defer or Not to Defer (Rendering)

With the above configuration in place, the appropriate (Add Region) or (Regenerate) button was appearing conditionally as desired. However, I noticed that when the page first rendered I would momentarily see both buttons flash before the inappropriate one for the currently selected region would get hidden by my dynamic action. The solution to avoid the user’s seeing this “behind the scenes” page setup behavior is to enable the Deferred Page Rendering template option shown below. This setting allows you to decide when faster, incremental page rendering is more appropriate, or whether APEX should wait until page-load-time dynamic behavior is complete before revealing the final state of the page to the user.

Deferred Page Rendering option hides page-load-time hide and show activity

Preserving Column Selection on Regeneration

When you click the (Regenerate) button for a region you’ve already added to the selected list, the add_region_to_selected_list() function updates the existing eba_demo_reg_data_requests row to set READY = ‘N‘ and it runs the background job to call describe_region() again. The region might have changed the set of available columns since the previous time we described it, but the user may have carefully decided which of the previous region’s columns to include and exclude. So it’s important for usability to retain the included columns across the region data profile regeneration.

At the moment the describe_region() code has produced the fresh region data profile XML document and is about to update the existing row in eba_demo_reg_data_requests, we have the following “ingredients” available to work with:

  1. The old xml_describe region profile XML document
  2. The old include_columns value with a colon-separated list of index positions relative to the old region profile XML document
  3. The new region profile XML document just produced

What we need to “bake” with these ingredients is a new list of included columns that retains any columns that were previously in the included list while ignoring any of those included columns that are no longer available to reference. Also worth considering is that the index positions of the previous column names might be different in the new region data profile XML document.

After initially writing the code using multiple loops in PL/SQL, I challenged myself to come up with a single SQL statement to accomplish the job. In words, what I needed the statement to do was, “select a colon-separated list of index positions relative to the new XML describe document where the column name is in the list of names whose whose index positions (relative to the old XML describe document) were in the colon-separated list currently stored in include_columns .” I adjusted the query to also handle the situations when the old XML document was null and when the list of include_columns was null. This let me use the same routine to calculate the default value for the include_columns list for both new and updated rows in eba_demo_reg_data_requests. The private get_default_included_columns() function in the eba_demo_region_data_sharing package has the SELECT statement I use to tackle the job.

select listagg(x.seq,':') within group (order by x.seq)
into l_ret
from xmltable('/ROWSET/ROW' passing p_new_xml_describe
            seq for ordinality,
            name varchar2(128) path 'NAME') x
    where p_old_xml_describe is null
        or in (
        from xmltable('/ROWSET/ROW' 
                passing p_old_xml_describe
                    seq for ordinality,
                    name varchar2(128) path 'NAME') y
        where p_before_list is null 
            or y.seq in (
            select to_number(column_value)
                from apex_string.split(p_before_list,':'))); 


This was a fun learning project that taught me many new things about Oracle APEX in the process of building it. Always keep in mind that Oracle APEX is built with Oracle APEX, and that you, too, can use APEX to build yourself any kind of development productivity helper app you can imagine, not to mention plug-ins of all kinds to extend the core functionality of the platform. Thanks to colleagues Carsten, Jeff, and Vlad who offered me tips on aspects of this sample.

Getting the Sample Apps

You can download the Oracle APEX 22.2 export of the Region Data Sharing Helper app from here. In case you’re interested in the HR Sample app used to show off the generated artifacts in action, you can download that from here. The latter requires that you’ve first installed the EMP/DEPT sample dataset from the Gallery. Enjoy the simplified data sharing!

Declarative Data-Driven Downloads

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:

Static conference program HTML file generated from data managed by an APEX app

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">
            select deptno, dname, loc
             from dept
        <transformation stylesheet="home-page.xsl" 

The in-memory XML document representing the results of the data transform’s query looks like this:

  <!-- etc. -->

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"
  <xsl:output method="html"/>
  <xsl:template match="/">
          <xsl:for-each select="/ROWSET/ROW">
                <xsl:value-of select="DNAME"/>
                (<xsl:value-of select="LOC"/>)

When viewed in a web browser, the index.html file it produces will looks like this:

HTML home page showing department data

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:
     p_file_name    => 'generate-hr-site.xml', 
     p_zipfile_name => '');

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 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">
            select deptno, dname, loc
             from dept
        <transformation stylesheet="home-page.xsl" 
       <transformation stylesheet="dept-page.xsl" 
            <parameter-query column-to-parameter-map
                select deptno from dept

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">
            select deptno, dname, loc, 
                   cursor( select empno, ename, job 
                            from emp 
                           where deptno = d.deptno) as staff
             from dept d
        <transformation stylesheet="home-page.xsl" 
        <transformation stylesheet="dept-page.xsl" 
            <parameter-query column-to-parameter-map
                select deptno from dept
        <transformation stylesheet="emp-page.xsl" 
            <parameter-query column-to-parameter-map
                select empno, deptno from emp

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 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.

Mac Finder showing contents of downloaded file

Exploring 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.

Screenshot of sample application that accompanies this article

Building a Mini SQL Workshop #JoelKallmanDay

Two new features in APEX 22.2 let me easily create a Mini SQL Workshop app. Users type any query in a code editor and instantly visualize the resulting data. I wired my colleague Ronny Weiss’ Monaco Code Editor region plug-in to a new CLOB-valued page item to support editing the SQL query. A dynamic action reacts to the code editor save event to refresh the new Dynamic Content region to show the query’s results. For an interesting twist, the database’s native support for XML and XSLT produces the HTML markup for the dynamic content region rather than looping over data and concatenating strings ourselves.

On this second annual Joel Kallman Day, and just past my first anniversary on the APEX dev team, this example happily reunited me with some of the ideas I loved so much back in the late 1990’s that they compelled me to write my book Building Oracle XML Applications. Thanks for the inspiration, Joel. Miss ya lots!

Since APEX makes the job easy, unsurprisingly the page looks simple, too. The code editor region has a dynamic action that refreshes the Results region, which returns its HTML contents using an html_for_sql() package procedure. The P1_SQL page item is typed as a CLOB so the SQL query can be as large as necessary.

Code Editor region, CLOB-valued P1_SQL page item & Dynamic Content region for query results

Creating a CLOB-valued Page Item

I started by creating a hidden page item named P1_SQL in my page to store the query the user will edit in the code editor. Since the query might exceed 32K, I took advantage of the new CLOB data type in the Session State section of the property editor. When you create hidden page items, as well as ones that can hold a large amount of text, you can now change the default session state data type from VARCHAR2 to use a CLOB instead.

Hidden page items and ones that can contain large amounts of text can now use a CLOB data type

Wiring the Editor to a CLOB-valued Page Item

Next, I downloaded the latest version of the Monaco Code Editor region plugin, extracted the zip file, and imported the plugin into my new application. I created the Code region and set it to have the type APEX-VS-Monaco-Editor [Plug-In]. Next I configured the source of the code editor to retrieve the value of the P1_SQL CLOB-valued page item by setting its SQL Query to:

select :p1_sql as value_edit, 
       'sql'   as language
from dual

On the Attributes tab of the code editor region, I setup the code editor to save its contents in the CLOB-valued page item by entering the following one-line call in its Execute on Save property. The plug-in sets up the :CLOB bind variable and we use the new apex_session_state package’s set_value() procedure to assign that value to the P1_SQL page item.


And with those two simple steps, the code editor for the SQL query was sorted.

Refreshing the Dynamic Content Region

To refresh the dynamic content region whenever the user clicks the (Save) button on the code editor, I added a dynamic action to react to the plug-in’s event Upload of Text finished [APEX-VS-Monaco-Editor]. It contains a single action step using the Refresh action type, and uses yet another new 22.2 feature to make the action step more self-documenting by entering a more meaningful name of “Refresh Results”.

Using a custom name for an action step to make more maintainable apps

Using XML & XSLT to Get HTML for any SQL

With the new 22.2 Dynamic Content region, you no longer use the trusty HTP package to print HTML markup into a buffer. Instead, you configure a function that returns a CLOB containing the HTML the region should render. This change was required to make the region dynamically refreshable, which was a longtime request from APEX developers in the community. You can create the CLOB full of HTML markup in any convenient way, but the way I find most elegant and declarative is using the combination of XML and XSLT stylesheets.

The Oracle database contains native functionality to produce an XML document representing the results of a query using the DBMS_XMLGEN package’s getxml() function. To produce an XML document from an arbitrary SQL query contained in a variable like p_sql, you just need the following few lines of code. The call to the SetNullHandling() procedure asks DBMS_XMLGEN to use an empty tag to represent a NULL value rather than omitting the XML element for a NULL column in the result.

ctx := dbms_xmlgen.newcontext(p_sql);
dbms_xmlgen.setnullhandling(ctx, dbms_xmlgen.empty_tag);
l_xml_clob := dbms_xmlgen.getxml(ctx);

The getxml() function produces an XML document that will have a canonical structure like this with a <ROWSET> element containing one or more <ROW> elements, each of which contains child elements named after the columns in the result set.


The XML Stylesheet Language (XSLT) is a powerful, concise, declarative way to recursively format the contents of an XML document to produce a result like HTML. If you are a fan of Oracle Reports and its approach of applying “repeating frames” to data, then you’ll understand XSLT intuitively. If not, it may take a bit longer to have its charms grow on you, but given its Swiss Army knife applicability to many jobs, it’s well worth your time to learn more about it.

If we write the XSLT stylesheet in a generic way, the same transformation can produce an HTML table from the results of any XML in the <ROWSET>/<ROW> format above. The XSLT stylesheet that gets the job done looks like the one below. Starting with the root (match="/") of the document, its templates recursively apply other matching style templates to the XML elements in the document “tree” of nested elements. The stylesheet contains templates that match a ROWSET element, a ROW element, and any child element of a row ROW/* to produce an HTML <table>. This table contains <thead> and <tbody> elements, <tr> elements for the rows of the table, and <th> and <td> elements for the table cells containing the data from the query result XML document it’s presented to format.

<xsl:stylesheet version="1.0"
  <xsl:template match="/">
  <xsl:template match="ROWSET">
    <table class="sql-results" cellspacing="0">
        <xsl:apply-templates select="ROW[1]/*" mode="ColumnHeaders"/>
  <xsl:template match="ROW">
  <xsl:template match="ROW/*">
  <xsl:template match="ROW/*" mode="ColumnHeaders">
    <th><xsl:value-of select="name(.)"/></th>

To transform the XML document produced from the query into HTML, the demo app’s html_for_sql() function in the eba_demo_xslt package uses the Oracle SQL function xmltransform(). It uses the stylesheet above to transform the result into HTML using a single SQL statement like this:

select xmlserialize(content
         xmltransform(l_xml,c_stylesheet) as clob)
into l_html
from dual;

Since the DBMS_XMLGEN package and the XMLTRANSFORM() and XMLSERIALIZE() functions are all implemented natively inside the database engine, they get the job done quickly. To tweak the formatting of the results, we can simply adjust the declarative XSLT stylesheet and sprinkle in some appropriate CSS style rules on the page.

Returning Dynamic Content Region HTML

The last step in the process is configuring the function body that will return the new Dynamic Content region’s HTML markup. To accomplish this, I set the PL/SQL Function Body returning a CLOB property of the Results region to the following to employ our XML and XSLT approach above. I simply pass in the value of the P1_SQL CLOB-valued page item into the html_for_sql() function.

return eba_demo_xslt.html_for_sql(

With all the pieces in place, we can type in any valid query of any size or complexity, click on the code editor’s (Save) button, and immediately see the query results in the page.

Query results from two APEX dictionary views

Ensuring the User-Entered Query is Valid

The sample’s eba_demo_xslt package also contains a validate_sql_statement() function that ensures the query starts with SELECT or WITH as well as guarantees that it parses correctly. The function returns NULL if the query is valid, or otherwise it returns an error message to be displayed in the page to help the user understand what’s wrong with the query.

To checkout the sample, download it from here and import it into APEX 22.2 to give it a spin.