I have written a number of posts on alternative, far fetched usages of Table Functions. My all-time high thusfar was a post titled Table FUNctions: select a graph in SQL . Now, I think I have reached another level in irrelevancy – though I have to admit it gives me great personal satisfaction: this post tells you how to create pie-charts using SQL queries. Be warned: it is utterly useless. But great fun for the twisted minds I know some of you have too! Think Table Functions, some basic maths and too much fantasy.

The story is really quite simple: you pass a query that selects labels and values as a cursor to a table function -`table( cursor (select label, value from table))`
In SQL*Plus, we execute these commands:

```set linesize 150
set pagesize 500
set long 200

select substr(column_value,1,150) "Piechart Salaries per Job"
from table( pie_graph
( cursor(select job, sum(sal) from emp group by job)
, 'Salaries per Job'
,30
, 0.15
)
)
/```

The function pie_graph that is invoked here looks as follows:

```function pie_graph ( p_src in sys_refcursor -- a ref-cursor for a query that returns labels in the first column and values in the second columns
, p_title  in varchar2 default null
, p_radius in integer default 25 -- specifies the size of the piechart;
, p_threshold in number default 0.05 -- smallest percentage that warrants it owns pie-segment; all values below this threshold are taken together as Others
) return fixed_string_table
```

It relies on three Types being defined in the database:

```  create type string_table is table of varchar2(2000);
create type values_tbl is table of number;
create type fixed_string_table as table of char(150);
```

Another example of using this function to create pie-charts:

```select substr(column_value,1,150) "Piechart Objects per User"
from table( pie_graph
( cursor(select owner, count(*) from all_objects where owner not in ('PUBLIC','SYS') group by owner)
, 'Objects per user'
,25
, 0.15
)
)
```

The complete source code can be downloaded at the end of this article. It looks as follows (suggestions for improvements are very welcome!):

```function pie_graph ( p_src in sys_refcursor -- a ref-cursor for a query that returns labels in the first column and values in the second columns
, p_title  in varchar2 default null
, p_radius in integer default 25 -- specifies the size of the piechart;
, p_threshold in number default 0.05 -- smallest percentage that warrants it owns pie-segment; all values below this threshold are taken together as Others
) return fixed_string_table
is
/* required collection types

create type string_table is table of varchar2(2000);
create type values_tbl is table of number;
create type fixed_string_table as table of char(150);

select substr(column_value,1,150) "Piechart Salaries per Job"
from table( pie_graph
( cursor(select job, sum(sal) from emp group by job)
, 'Salaries per Job'
,30
, 0.2
)
)
/

*/

c_full_circle float := 2*3.14159;

retval fixed_string_table:= fixed_string_table();
l_labels string_table;
l_values values_tbl;
l_string char(1200);
l_sum number:=0;
l_dial float:=0;
l_percentage float;
l_index integer;
l_x integer;
l_others integer:=0;
l_others_label varchar2(1000):= ': ';
l_others_value number:=0;

procedure xy(x integer, y integer, marker char default '*')
is
l_string char(1200);
begin

l_string :=  substr( l_string, 1 ,2*(l_radius + x))
||marker
;
end xy;

procedure create_piesegment
( p_percentage in float
, p_marker in varchar2
, p_label in varchar2)
is
begin
-- move the dial half the distance (or angle) for this value
-- to first print the Indicator (A, B, C etc.) for this value
l_dial:= l_dial + 0.5 *p_percentage * c_full_circle;

-- display the indicator in this pie-segment
, p_marker);

-- move the dial the other half of the distance (or angle) for this value
l_dial:= l_dial + 0.5*p_percentage * c_full_circle;
-- in 10 steps, draw the border for this segment
for i in 0..10 loop
-- print a dot at i/10 of the line from the center to the perimeter
,'.');
end loop;
retval.extend;
-- write the legend line for the current value (something like: A - 500 (23 %))
retval(retval.last):= p_label;
end;

begin
-- retrieve all values and labels from the refcursor into local collections
fetch p_src bulk collect into l_labels, l_values;
close p_src;
-- iterate through the values to calculate the sum of all values
l_index:= l_values.first;
loop
l_sum:= l_sum + l_values(l_index);
l_index:= l_values.next(l_index);
if l_index is null then exit; end if;
end loop;

-- initialize the collection of fixed length strings to return
retval.extend;
end loop;
retval(1):= p_title;
-- draw center
xy(0,0,'x');
-- draw the outline of the chart
if (y mod 2 = 0)  -- to get a stepsize of 2 : every other line a marker
then
xy(l_x,y); -- noon-3 o'clock
xy(-l_x,y); -- 9 to 12
xy(l_x,-y); -- 3 to 6
xy(-l_x,-y); -- 6 to 9
end if;
end loop;

-- draw the initial line, from the center horizontally to the right (3 o'clock)
for i in 0..10 loop
,'.');
end loop;

-- iterate through the values and labels to draw all pie-segments
l_index:= l_values.first;
loop
-- calculate the percentage for the current value
l_percentage:= l_values(l_index)/l_sum;
if l_percentage > p_threshold
then
create_piesegment
( p_percentage => l_percentage
, p_marker => chr(ascii('A')+l_index-1-l_others)
, p_label => chr(ascii('A')+l_index-1-l_others)||' = '||l_labels(l_index)||' - '||l_values(l_index)||' ('||round(l_percentage*1000)/10||' %)'
);
else -- under threshold, update others
l_others:= l_others +1;
l_others_value:= l_others_value + l_values(l_index);
l_others_label:= l_others_label||l_labels(l_index)||' - '||l_values(l_index)||' ('||round(l_percentage*1000)/10||' %);';
end if;
l_index:= l_values.next(l_index);
if l_index is null
then
exit;
end if;
end loop; -- over all values and labels
if l_others_value <> 0
then
-- process the others
l_percentage:= l_others_value/l_sum;
create_piesegment
( p_percentage => l_percentage
, p_marker => '#'
, p_label => '# = Others '||l_others_value||' ('||round(l_percentage*1000)/10||' %)'||l_others_label||' - '
);
end if; -- l_others_value <>0
return retval;
end;```

Share.

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.

1. I am not sure whether this is divine or devilish! Hats off to you

2. He, couldn’t you also build in some nice text adventures, underneath those nice graphics

3. Have you seen and appreciated the strict MVC architecture? The query itself is the Controller that instructs the Model to produce some data (that is the inner query passed in the first parameter as CURSOR expression while the VIEW, completely shielded from MODEL specifics is only concerned with producing a pretty picture.

4. Wow! That beats the ol’ bargraph any day…
SQL> column Bar format a20
SQL> select rownum, lpad(‘>’, rownum, ‘-‘) Bar from all_objects where rownum < 7
2 /

ROWNUM BAR
---------- --------------------
1 >
2 ->
3 –>
4 —>
5 —->
6 —–>

6 rows selected.

5. Something like poke 781,X:poke 782,Y:poke 783,0:sys 65520:Print”*” you mean? Ok ok i admit, that’s for the Commodore, never did any ZX81 coding