Once upon a time, I had something of a reputation for creating Charts in SQL. Bar Charts, Pie Charts, Gantt Charts and several other unlikely chart types, all created with SQL. And all presented on the one stage SQL has: SQL*Plus. See for example such riveting articles like https://technology.amis.nl/blog/1890/creating-a-gantt-chart-in-sql, https://technology.amis.nl/blog/1937/gauge-charts-in-sql-or-how-to-produce-the-worlds-ugliest-speedometers and https://technology.amis.nl/blog/398/pie-charts-in-sql-how-pathetic-can-you-get. Now I am back, with another look at charts produced straight out of the Oracle Database – this time using a different stage to present the chart on. With the Embedded PL/SQL Gateway – introduced in release 10g – the Oracle Database has a different stage to present information on or a different user interface to interact through: the web browser.
This article demonstrates how we can present charts – in the web browser using the CANVAS tag – based directly on a PL/SQL package in the database. No middle tier is required in between, no additional libraries are frameworks need to be configured. It’s only between the package and your browser.
Granted, you have seen prettier charts in your life time. But this of course is only the beginning. What you see here is a screenshot of a browser showing only the rendition of HTML with some embedded JavaScript, all produced by a single PL/SQL package. Making things look pretty is not my specialty I’m afraid, so I will leave that as an exercise to the reader.
Note that the code has an interesting use of the LISTAGG aggregation operator introduced in Oracle 11gR1 Database – for creating JSON-esque data structures. I’m sure more could be achieved in this fashion.
CANVAS element
The heart of the CANVAS based Pie Chart obviously is the CANVAS tag. It is stuck in the middle of a very simple HTML page:
It is initially empty. The chart will be drawn by a JavaScript function that is invoked from the onLoad event on the Body tag. This is where the real action is. More on CANVAS: http://www.canvasdemos.com/type/tutorials/ and http://www.whatwg.org/specs/web-apps/current-work/multipage/the-canvas-element.html.
JavaScript function draw_pie
This functions gets hold of the CANVAS element. It does some intializations, including those of the data array – dynamically written bij PL/SQL as we will see in a moment – and the associated labels. It then loops over all data elements to determine the total sum – needed to determine the size of each pie slice given the data value for a specific element. Next it loops over the data elements again, to create the pie slices, allocate a randomly generated color to them and write the label inside the slice. All drawing on the CANVAS is done through calls on the ctx variable that holds the Canvas Context.
PL/SQL package
The JavaScript function shown above is written to the HTML page by a PL/SQL procedure in the Dashboard package. This procedure is called with two parameters: one a string with square brackets on either side a comma delimited list of data values, the other a similar string with associated labels.
The procedure write_javascript is invoked from the main procedure departments. This procedure write the HTML for the page, determines the values for p_data and p_labels parameters and invokes thie write_javascript procedure.
procedure departments
as
l_data varchar2(4000);
l_labels varchar2(4000);
begin
htp.p('<HTML><HEAD><TITLE>Departments Dashboard</TITLE></HEAD>');
--... SQL query to select value into l_data and l_labels
write_javascript(l_data, l_labels);
htp.p('<BODY onload="draw_pie()">');
htp.p('<H1>Salary distribution across departments</H1>');
htp.p('<canvas id="canvas" width="300" height="300"></canvas>');
htp.p('</BODY></HTML>');
end departments;
An interesting part of this departments procedure is the SQL Query that constructs the lists of data and label elements, using the LISTAGG operator:
select '['||listagg( '"'||dname||'"', ',')
within group (order by sumsal desc)
||']' as data
, '['||listagg( nvl(sumsal,0), ',')
within group (order by sumsal desc)
||']' as labels
into l_labels, l_data
from ( select dname
, sum(sal) sumsal
from emp
right outer join dept
using (deptno)
group by dname
)
Resources
Download the PL/SQL package used in this article: simpleEPGwithCANVASbasedPIEchart.
Article on CANVAS and the creation of a Pie Chart that I used for inspiration – and to borrow some JavaScript code from: http://www.phpied.com/canvas-pie/.
