Oracle database experiences

Oracle database blog

Generating SVG graphics with SQL*Plus – Part V

X-axis labels

We will enhance the previous graph defined in the part IV by adding x-axis labels and by displaying vertical grid lines. We will display the begin_time column in the format HH24:MI every five rows. We will modify the source query as follow:

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

Bottom margin

In order to display the x-axis labels, we will define a bottom margin in the graph. The canvas area height will be set to “100+$margin_top+$margin_bottom”.

XSLT variables

We will add one additional variable in the XSLT document:

  • <xsl:variable name="margin_bottom">30</xsl:variable>

    Bottom margin of the SVG graph.

X-axis areas

Every five rows:

  • An x-axis label will be displayed:
    <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>
  • A vertical grid line will be displayed:
    <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:cornflowerblue;stroke-width:1" />

ORA-01704: string literal too long

As the XSLT document becomes more and more long, the string literal limit of 4000 characters has been exceeded. The function XMLTYPE.CREATEXML is also defined for a CLOB type. We will split the XSLT document in several parts not exceeding 4000 characters using the TO_CLOB function and the concatenation (||) operator.

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

SELECT XMLTRANSFORM
   (DBMS_XMLGEN.GETXMLTYPE
      ('SELECT CEIL(value)                     YVAL
        ,      metric_name                     METRIC_NAME
        ,      metric_unit                     METRIC_UNIT
        ,      TO_CHAR(begin_time,''HH24:MI'') BEGIN_TIME
        FROM v$sysmetric_history
        WHERE metric_id = 2054
          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: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:cornflowerblue;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:cornflowerblue;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:cornflowerblue;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:cornflowerblue;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:cornflowerblue;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">0</text>
           <text x="{($margin_left)-20}" y="{($graph_height)-($margin_bottom)-25}" style="fill: #000000; stroke: none;font-size:8px;text-anchor=start">25</text>
           <text x="{($margin_left)-20}" y="{($graph_height)-($margin_bottom)-50}" style="fill: #000000; stroke: none;font-size:8px;text-anchor=start">50</text>
           <text x="{($margin_left)-20}" y="{($graph_height)-($margin_bottom)-75}" style="fill: #000000; stroke: none;font-size:8px;text-anchor=start">75</text>
           <text x="{($margin_left)-20}" y="{($graph_height)-($margin_bottom)-100}" style="fill: #000000; stroke: none;font-size:8px;text-anchor=start">100</text>
           <line x1="{$margin_left}" y1="{($graph_height)-($margin_bottom)}" x2="{$margin_left}" y2="{($graph_height)-($margin_bottom)-100}" style="stroke:cornflowerblue;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:cornflowerblue;stroke-width:1" />
               </xsl:when>
             </xsl:choose>    
           </xsl:for-each>
           <xsl:for-each select="ROWSET/ROW/YVAL">
             <rect x="{$margin_left+$bar_width*(position()-1)}" y="{($graph_height)-($margin_bottom)-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_5e
Remarks:

  • We have used a “for-each” loop in order to display the x-axis labels and the vertical grid lines,
  • We have also used the conditional processing instruction “choose” in order to display the x-axis labels and the vertical grid lines every five rows.
Advertisements

One response to “Generating SVG graphics with SQL*Plus – Part V

  1. felipe May 7, 2014 at 3:33 pm

    Friend, amazing, very useful

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: