Oracle database experiences

Oracle database blog

Generating SVG graphics with SQL*Plus – Part IV

Y-axis labels

We will enhance the previous graph defined in the part III by adding y-axis labels, the metric unit of the system metric and by displaying horizontal grid lines. We will modify the source query as follow:

SELECT CEIL(value) YVAL
,      metric_name METRIC_NAME
,      metric_unit METRIC_UNIT
FROM v$sysmetric_history
WHERE metric_id = 2054
  AND group_id = 2 
ORDER BY begin_time

Left margin

In order to display the y-axis labels, we will define a left margin in the graph. The canvas area width will be set to “315+$margin_left”.

XSLT variables

We will add 4 additional variables in the XSLT document:

  • <xsl:variable name="margin_left">40</xsl:variable>

    Left margin of the SVG graph,

  • <xsl:variable name="graph_height"><xsl:value-of select="100+$margin_top"/></xsl:variable>

    Height of the graph,

  • <xsl:variable name="graph_width"><xsl:value-of select="315+$margin_left"/></xsl:variable>

    Width of the graph,

  • <xsl:variable name="graph_unit"><xsl:value-of select="/descendant::METRIC_UNIT[position()=1]"/></xsl:variable>

    Metric unit of the graph. The variable is set to the first value of the column METRIC_UNIT.

Y-axis areas

We will divide the y-axis in 4 areas: [0,25],[25,50],[50,75] and [75,100]. At each boundary area:

  • An y-axis label will be displayed:
    <text x="{($margin_left)-20}" y="{($graph_height)-25}" style="fill: #000000; stroke: none;font-size:8px;text-anchor=start">25</text>
  • An horizontal grid line will be displayed:
    <line x1="{($margin_left)-5}" y1="{($graph_height)-25}" x2="{($graph_width)-1}" y2="{($graph_height)-25}" style="stroke:cornflowerblue;stroke-width:1" />

Modified query

You will find below the modified query:

SET LINESIZE      300
SET LONGCHUNKSIZE 30000
SET LONG          30000

SET FEEDBACK OFF
SET PAGESIZE 0

SET HEADING OFF

SPOOL generate_svg_4f.svg

SELECT XMLTRANSFORM
   (DBMS_XMLGEN.GETXMLTYPE
      ('SELECT CEIL(value) YVAL
        ,      metric_name METRIC_NAME
        ,      metric_unit METRIC_UNIT
        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:variable name="margin_top">20</xsl:variable>
     <xsl:variable name="margin_left">40</xsl:variable>
     <xsl:variable name="bar_width">5</xsl:variable>
     <xsl:variable name="graph_height"><xsl:value-of select="100+$margin_top"/></xsl:variable>
     <xsl:variable name="graph_width"><xsl:value-of select="315+$margin_left"/></xsl:variable>
     <xsl:variable name="graph_name"><xsl:value-of select="/descendant::METRIC_NAME[position()=1]"/></xsl:variable>
     <xsl:variable name="graph_unit"><xsl:value-of select="/descendant::METRIC_UNIT[position()=1]"/></xsl:variable>
       <xsl:template match="/">
         <svg xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#" xmlns="http://www.w3.org/2000/svg" width="{$graph_width}" height="{$graph_height}">
           <text x="{$margin_left+1}" y="{($margin_top)-5}" style="fill: #000000; stroke: none;font-size:10px;text-anchor=start"><xsl:value-of select="$graph_name"/></text>
           <text x="{0-($graph_height)}" y="10" transform="rotate(-90)" style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="$graph_unit"/></text>
           <line x1="{($margin_left)-5}" y1="{($graph_height)-0}" x2="{($graph_width)-1}" y2="{($graph_height)-0}" style="stroke:cornflowerblue;stroke-width:1" />
           <line x1="{($margin_left)-5}" y1="{($graph_height)-25}" x2="{($graph_width)-1}" y2="{($graph_height)-25}" style="stroke:cornflowerblue;stroke-width:1" />
           <line x1="{($margin_left)-5}" y1="{($graph_height)-50}" x2="{($graph_width)-1}" y2="{($graph_height)-50}" style="stroke:cornflowerblue;stroke-width:1" />
           <line x1="{($margin_left)-5}" y1="{($graph_height)-75}" x2="{($graph_width)-1}" y2="{($graph_height)-75}" style="stroke:cornflowerblue;stroke-width:1" />
           <line x1="{($margin_left)-5}" y1="{($graph_height)-100}" x2="{($graph_width)-1}" y2="{($graph_height)-100}" style="stroke:cornflowerblue;stroke-width:1" />
           <text x="{($margin_left)-20}" y="{($graph_height)-2}" style="fill: #000000; stroke: none;font-size:8px;text-anchor=start">0</text>
           <text x="{($margin_left)-20}" y="{($graph_height)-25}" style="fill: #000000; stroke: none;font-size:8px;text-anchor=start">25</text>
           <text x="{($margin_left)-20}" y="{($graph_height)-50}" style="fill: #000000; stroke: none;font-size:8px;text-anchor=start">50</text>
           <text x="{($margin_left)-20}" y="{($graph_height)-75}" style="fill: #000000; stroke: none;font-size:8px;text-anchor=start">75</text>
           <text x="{($margin_left)-20}" y="{($graph_height)-100}" style="fill: #000000; stroke: none;font-size:8px;text-anchor=start">100</text>
           <line x1="{$margin_left}" y1="{$graph_height}" x2="{$margin_left}" y2="{($graph_height)-100}" style="stroke:cornflowerblue;stroke-width:1" />
           <xsl:for-each select="ROWSET/ROW/YVAL">
             <rect x="{$margin_left+$bar_width*(position()-1)}" y="{($graph_height)-self::node()}" width="{$bar_width}" 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_4f
Remarks:

  • The metric unit label is displayed vertically using the rotation transformation “rotate(-90)”,
  • A vertical grid line is displayed at x=0.
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: