Oracle database experiences

Oracle database blog

Generating SVG graphics with SQL*Plus – Part VII

Line graph

In some cases it might be convenient to have a line graph instead of a bar graph. We will therefore modify the query described in the part VI.

Polyline

We will use the SVG polyline element in order to create the line. Each base point is represented by a pair of x-axis value and y-axis value separated by a comma e.g. “12,34”. You will find below an example of a polyline element:

<polyline points="40,52 45,77 ... 70,27 280,96 " style="fill:none;stroke:blue;stroke-width:1"/>

Polyline generation

The generation of the point pairs is made through the variable “v_graph” as follow:

  • Read through all the YVAL values:
    <xsl:for-each select="ROWSET/ROW/YVAL">
  • Set the “x_val” variable to the x-axis value:
    <xsl:variable name="x_val"><xsl:value-of select="$margin_left+..."/>
  • Set the “y_val” variable to the y-axis value:
    <xsl:variable name="y_val"><xsl:value-of select="round(($graph_height)..."/>
  • Concatenate “x_val” and “y_val” variables:
    <xsl:value-of select="concat($x_val,'','',$y_val,'' '')"/>

Modified query

You will find below the modified query:

DEFINE METRIC_ID_IN=2155;

SET LINESIZE      1000
SET LONGCHUNKSIZE 30000
SET LONG          30000

SET FEEDBACK OFF
SET VERIFY   OFF
SET PAGESIZE 0

SET HEADING OFF

SPOOL generate_svg_7b.svg

SELECT XMLTRANSFORM
   (DBMS_XMLGEN.GETXMLTYPE
      ('SELECT value YVAL
        ,      (SELECT MAX(value)+DECODE(SIGN(MAX(value)-MIN(value)),0,1+MIN(value),0) FROM v$sysmetric_history WHERE metric_id = &METRIC_ID_IN AND group_id = 2) YVAL_MAX
        ,      (SELECT MIN(value) FROM v$sysmetric_history WHERE metric_id = &METRIC_ID_IN AND group_id = 2) YVAL_MIN
        ,      metric_name METRIC_NAME
        ,      metric_unit METRIC_UNIT
        ,      TO_CHAR(begin_time,''HH24:MI'') BEGIN_TIME
        FROM v$sysmetric_history
        WHERE metric_id = &METRIC_ID_IN
          AND group_id = 2 
        ORDER BY begin_time')
,      XMLTYPE.CREATEXML
   (TO_CLOB(
    '<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_bottom">30</xsl:variable>
     <xsl:variable name="margin_left">40</xsl:variable>
     <xsl:variable name="bar_width">5</xsl:variable>
     <xsl:variable name="graph_width"><xsl:value-of select="315+$margin_left"/></xsl:variable>
     <xsl:variable name="graph_height"><xsl:value-of select="100+$margin_top+$margin_bottom"/></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:variable name="yval_max"><xsl:value-of select="/descendant::YVAL_MAX[position()=1]"/></xsl:variable>
     <xsl:variable name="yval_min"><xsl:value-of select="/descendant::YVAL_MIN[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="{($margin_bottom)-($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)-($margin_bottom)-0}"   x2="{($graph_width)-1}" y2="{($graph_height)-($margin_bottom)-0}"  style="stroke:lavender;stroke-width:1" />
           <line x1="{($margin_left)-5}" y1="{($graph_height)-($margin_bottom)-25}"  x2="{($graph_width)-1}" y2="{($graph_height)-($margin_bottom)-25}" style="stroke:lavender;stroke-width:1" />
           <line x1="{($margin_left)-5}" y1="{($graph_height)-($margin_bottom)-50}"  x2="{($graph_width)-1}" y2="{($graph_height)-($margin_bottom)-50}" style="stroke:lavender;stroke-width:1" />
           <line x1="{($margin_left)-5}" y1="{($graph_height)-($margin_bottom)-75}"  x2="{($graph_width)-1}" y2="{($graph_height)-($margin_bottom)-75}" style="stroke:lavender;stroke-width:1" />
           <line x1="{($margin_left)-5}" y1="{($graph_height)-($margin_bottom)-100}" x2="{($graph_width)-1}" y2="{($graph_height)-($margin_bottom)-100}" style="stroke:lavender;stroke-width:1" />
           <text x="{($margin_left)-20}" y="{($graph_height)-($margin_bottom)-2}"   style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="round($yval_min)"/></text>
           <text x="{($margin_left)-20}" y="{($graph_height)-($margin_bottom)-25}"  style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="round(($yval_min)+(1*(($yval_max)-($yval_min)) div 4))"/></text>
           <text x="{($margin_left)-20}" y="{($graph_height)-($margin_bottom)-50}"  style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="round(($yval_min)+((($yval_max)-($yval_min)) div 2))"/></text>
           <text x="{($margin_left)-20}" y="{($graph_height)-($margin_bottom)-75}"  style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="round(($yval_min)+(3*(($yval_max)-($yval_min)) div 4))"/></text>
           <text x="{($margin_left)-20}" y="{($graph_height)-($margin_bottom)-100}" style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="round($yval_max)"/></text>
           <line x1="{$margin_left}" y1="{($graph_height)-($margin_bottom)}" x2="{$margin_left}" y2="{($graph_height)-($margin_bottom)-100}" style="stroke:lavender;stroke-width:1" />'
           ) 
    || 
    TO_CLOB(
          '<xsl:for-each select="ROWSET/ROW/BEGIN_TIME">
             <xsl:choose>
               <xsl:when test="(position()-1) mod 5=0">
                 <text x="{($margin_left)-9+($bar_width*(position()-1))}" y="{($graph_height)-($margin_bottom)+12}" style="fill: #000000; stroke: none;font-size:8px;text-anchor=start"><xsl:value-of select="self::node()"/></text>
                 <line x1="{($margin_left)+($bar_width*(position()-1))}" y1="{($graph_height)-($margin_bottom)+4}" x2="{($margin_left)+($bar_width*(position()-1))}" y2="{($graph_height)-($margin_bottom)-100}" style="stroke:lavender;stroke-width:1" /> 
               </xsl:when>
             </xsl:choose>    
           </xsl:for-each>
           <xsl:variable name="v_path">
             <xsl:for-each select="ROWSET/ROW/YVAL">
               <xsl:variable name="x_val">
                 <xsl:value-of select="$margin_left+$bar_width*(position()-1)"/>
               </xsl:variable>
               <xsl:variable name="y_val">
                 <xsl:value-of select="round(($graph_height)-($margin_bottom)-((($yval_min)-(self::node()))*(100 div (($yval_min)-($yval_max)))))"/>
               </xsl:variable>
               <xsl:value-of select="concat($x_val,'','',$y_val,'' '')"/>
             </xsl:for-each>
           </xsl:variable>
           <polyline points="{$v_path}" style="fill:none;stroke:blue;stroke-width:1" />
         </svg>
       </xsl:template>
     </xsl:stylesheet>'
           )
   )
   ) 
FROM dual;

SPOOL OFF
SET HEADING ON

SET VERIFY   ON
SET FEEDBACK ON

Result:

generate_svg_7b

Remark:

The system variable LINESIZE has been increased from 300 to 1000 in order to handle the polyline element.

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: