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.
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.
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:
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.
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 )
Download the PL/SQL package used in this article: simpleEPGwithCANVASbasedPIEchart.
- Made to order: Eirik's Hi-Lo Chart in SQL – extending the SQL Chart Palette (Pie, Stacked Bar Chart, Gauge, Gantt,…)
- Browser-based log-monitor for database applications (alternative to dbms_output,leveraging pipe and Embedded PL/SQL Gateway with a touch of AJAX)
- Oracle RDBMS 11gR2 – LISTAGG – New aggregation operator for creating (comma) delimited strings
- Oracle SQL – Finding free adjacent seats in an airplane, using Subquery Factoring, Analytical Functions (LISTAGG and LEAD), Outer Join, PIVOT and good old INSTR
- Creating JSON document straight from SQL query – using LISTAGG and With Clause
- No JDBC based data retrieval in Java applications – RESTstyle, JSON formatted HTTP based interaction from Java to Database
- Anti-Search patterns – SQL to look for what is NOT there – Part One