Oracle database experiences

Oracle database blog

Generating SVG graphics with SQL*Plus – Part I

Introduction

In this series of posts, we will generate SVG graphics using only SQL*Plus, a SELECT query and out of the box features of Oracle 12cR1. SVG stands for Scalable Vector Graphics; the graphics are described in a XML document, which can be displayed in a compatible browser.

SVG graphic area

The graphics are displayed in a graphic area called a “canvas”. By default, the x axis (in blue) is oriented from the left to the right of the window meanwhile the y axis (in red) is oriented from the top to the bottom of the windows as shown in the picture below:

Graphic_area

Vertical bar graph

We will start with a simple approach. The following script generates the history system metrics for the metric “Execute Without Parse Ratio” using a SVG vertical bar graph:

SET LONGCHUNKSIZE 30000
SET LONG          30000

SET FEEDBACK OFF
SET PAGESIZE 0

SET HEADING OFF
SPOOL generate_svg_0b.svg

SELECT svg_statement 
FROM (
   SELECT 0 x_val
   ,      '<svg xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#" xmlns="http://www.w3.org/2000/svg" height="120" width="800" version="1.1" xmlns:cc="http://creativecommons.org/ns#" xmlns:dc="http://purl.org/dc/elements/1.1/">' svg_statement 
  FROM dual
UNION ALL
   SELECT RANK() OVER (PARTITION BY 1 ORDER BY begin_time) x_val
   ,      '<rect x="' || TO_CHAR(5*RANK() OVER (PARTITION BY 1 ORDER BY begin_time)) 
       || '" y="' || TO_CHAR(100-CEIL(value)) ||'" width="5" height="' || TO_CHAR(CEIL(value))
       || '" fill="lightblue" stroke="black"/>' svg_statement 
   FROM v$sysmetric_history
   WHERE metric_id =2054
     AND group_id = 2 
UNION ALL
   SELECT 9999999999
   ,       '</svg>' 
   FROM dual)
ORDER BY x_val;

SPOOL OFF
SET HEADING ON

SVG generated file

You will find below an excerpt of the SVG generated file:

<svg xmlns:rdf="http://www.w3.org/1999/02/22-rdf-syntax-ns#" xmlns="http://www.w3.org/2000/svg" height="120" width="800" version="1.1" xmlns:cc="http://creativecommons.org/ns#" xmlns:dc="http://purl.org/dc/elements/1.1/">
<rect x="5" y="90" width="5" height="10" fill="lightblue" stroke="black"/>
<rect x="10" y="91" width="5" height="9" fill="lightblue" stroke="black"/>
<rect x="15" y="96" width="5" height="4" fill="lightblue" stroke="black"/>
...
<rect x="300" y="91" width="5" height="9" fill="lightblue" stroke="black"/>
<rect x="305" y="89" width="5" height="11" fill="lightblue" stroke="black"/>
</svg>

Result:
generate_svg_0b

Remarks:

  • The second SELECT of the query is the “SVG header”,
  • The fourth SELECT of the query is the “SVG footer”,
  • The third SELECT of the query is the “SVG body” which contains the different SVG elements. In our case it is composed of rectangle shapes,
  • The query has been tested on Oracle 11gR2 and Oracle 12cR1.
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: