Oracle database experiences

Oracle database blog

Generating SVG graphics with SQL*Plus – Part II

Enhancement

The query described in the Part I transforms rows in a XML document. This transformation could be done with the database in another way.

XMLType

In this first step we will transform the rows in a XML document using the DBMS_XMLGEN package as shown below:

SELECT DBMS_XMLGEN.GETXMLTYPE(
   'SELECT CEIL(value) YVAL 
    FROM v$sysmetric_history
    WHERE metric_id = 2054
      AND group_id = 2 
    ORDER BY begin_time') FROM dual;

This query produces the following result:

<ROWSET>
 <ROW>
  <YVAL>0</YVAL>
 </ROW>
 <ROW>
  <YVAL>9</YVAL>
 </ROW>
 <ROW>
  <YVAL>8</YVAL>
 </ROW>
 <ROW>
  <YVAL>4</YVAL>
 </ROW>
...
 <ROW>
  <YVAL>2</YVAL>
 </ROW>
</ROWSET>

XSLT

In this second step we will transform the previous XML document in a SVG document which is XML based. For this operation we will use the XSLT language which is defined in a XML document. We will use the following XSLT document:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
       <xsl:template match="/">
         <svg xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#" xmlns="http://www.w3.org/2000/svg" width="800" height="120">
           <xsl:for-each select="ROWSET/ROW/YVAL">
             <rect x="{5*position()}" y="{100-self::node()}" width="5" height="{self::node()}" fill="lightblue" stroke="black"/>        
           </xsl:for-each>
         </svg>
       </xsl:template>
     </xsl:stylesheet>

A XML document can be described as a tree structure. The main instructions in this XSLT document are described below:

  • <xsl:template match="/">

    Indicates that we will start at the root of the document,

  • <svg xmlns:rdf="http://www.w3.org....>

    Adds this piece of document for the “SVG header”,

  • <xsl:for-each select="ROWSET/ROW/YVAL">

    Indicates that we will select each YVAL leave of the XML document e.g. the YVAL column,

  • <rect x="{5*position()}" y=...>

    “position()” is a function that gives the current position in the tree, “self:node()” is the current value of the node e.g. YVAL column,

  • </svg>

    Adds this piece of document for the “SVG footer”.

XMLTRANSFORM

Finally we will apply the transformations described in the XSLT document on the XML rows document as follow:

SET LINESIZE 300
SET LONGCHUNKSIZE 30000
SET LONG          30000


SET FEEDBACK OFF
SET PAGESIZE 0

SET HEADING OFF

SPOOL generate_svg_2.svg

SELECT XMLTRANSFORM
   (DBMS_XMLGEN.GETXMLTYPE
      ('SELECT CEIL(value) YVAL 
        FROM v$sysmetric_history
        WHERE metric_id = 2054
          AND group_id = 2 
        ORDER BY begin_time')
,      XMLTYPE.CREATEXML
   ('<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
       <xsl:template match="/">
         <svg xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#" xmlns="http://www.w3.org/2000/svg" width="800" height="120">
           <xsl:for-each select="ROWSET/ROW/YVAL">
             <rect x="{5*position()}" y="{100-self::node()}" width="5" height="{self::node()}" fill="lightblue" stroke="black"/>        
           </xsl:for-each>
         </svg>
       </xsl:template>
     </xsl:stylesheet>')
   ) 
FROM dual;

SPOOL OFF
SET HEADING ON

Result:
generate_svg_2
Remarks:

  • The XMLTRANSFROM function returns a XMLType,
  • The instructions SET LONGCHUNKSIZE 30000 and SET LONG 30000 are used to handle the XMLType output format,
  • The y-axis values ​​are between 0 and 100.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: