Producing simple Pie Chart straight out of the Oracle Database – leveraging dbms_epg, CANVAS, LISTAGG and some JavaScript

0

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 http://technology.amis.nl/blog/1890/creating-a-gantt-chart-in-sql, http://technology.amis.nl/blog/1937/gauge-charts-in-sql-or-how-to-produce-the-worlds-ugliest-speedometers and http://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.

Image

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:

Image

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.

Image

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.

Image

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/.

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

Leave a Reply