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.

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.

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.

begin
  apex_session_state.set_value('P1_SQL',:clob);
end;

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 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);
dbms_xmlgen.closecontext(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.

<ROWSET>
   <ROW>
     <DEPTNO>10</DEPTNO>
     <DNAME>ACCOUNTING</DNAME>
   </ROW>
   <ROW>
     <DEPTNO>20</DEPTNO>
     <DNAME>RESEARCH</DNAME>
   </ROW>
</ROWSET>

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"
   xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:template match="/">
      <xsl:apply-templates/>
  </xsl:template>
  <xsl:template match="ROWSET">
    <table class="sql-results" cellspacing="0">
      <thead>
        <xsl:apply-templates select="ROW[1]/*" mode="ColumnHeaders"/>
      </thead>
      <tbody>
        <xsl:apply-templates/>
      </tbody>
    </table>
  </xsl:template>
  <xsl:template match="ROW">
    <tr><xsl:apply-templates/></tr>
  </xsl:template>
  <xsl:template match="ROW/*">
    <td><xsl:apply-templates/></td>
  </xsl:template>
  <xsl:template match="ROW/*" mode="ColumnHeaders">
    <th><xsl:value-of select="name(.)"/></th>
  </xsl:template>
</xsl:stylesheet>

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(
           apex_session_state.get_clob('P1_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.

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.