Oracle database experiences

Oracle database blog

Generating SVG graphics with SQL*Plus – Part III

Graph title

It might be useful to have the metric name displayed in the top of the graph. Firstly, we will have to modify the source query to get the metric name as follow:

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

Top margin

In order to display the graph title, we will define a top margin in the graph. The canvas area height will be set to “100+$margin_top”.

XSLT variables

We will add 3 variables in the XSLT document in order to be more flexible:

  • <xsl:variable name="margin_top">20</xsl:variable>

    Top margin of the SVG graph,

  • <xsl:variable name="bar_width">5</xsl:variable>

    Width of the bar,

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

    Name of the graph. The variable is set to the first value of the tree node METRIC_NAME,

  • Each variable can be used by adding a $ before the variable name e.g. “$margin_top”.

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

SELECT XMLTRANSFORM
   (DBMS_XMLGEN.GETXMLTYPE
      ('SELECT CEIL(value) YVAL
        ,      metric_name METRIC_NAME
        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="bar_width">5</xsl:variable>
     <xsl:variable name="graph_name"><xsl:value-of select="/descendant::METRIC_NAME[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="320" height="{100+$margin_top}">
           <text x="1" y="{($margin_top)-5}" style="fill: #000000; stroke: none;font-size:10px;text-anchor=start"><xsl:value-of select="$graph_name"/></text>
           <xsl:for-each select="ROWSET/ROW/YVAL">
             <rect x="{$bar_width*(position()-1)}" y="{$margin_top+100-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_3d
Remarks:

  • The first vertical bar starts at x=0 “(position()-1)”.
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: