0 
0 You may seen some enthousiastic ramblings on Table Functions in earlier posts I have written. This is another one. With approximately 50 lines of code, you can select crude graphs for any query. For example, a graph that indicates the total salary sum for the three departments in the SCOTT schema:
The underlying query to get this ‘graph’ is the following. Graph is a Table Function that is invoked with a CURSOR expression. Any query will do, as long as it returns two columns: the first a varchar2 – that is the label that is printed along the horizontal axis; the second column must be a numer – that is the value positioned against the vertical axis.
Other parameters allow us to specify the range for the vertical axis (the maximum and minimum value to display), the legends for both axes, the width of the horizontal labels and the height of the graph (in number of rows).
Another example is the following graph that displays all weekdays and for each weekday the number of employees we hired on that day (taken from hiredate in the emp table):
Here again we see the query underpinning the graph, returning a label (the weekday) and the value (the count). We also specify the graph range (between 0 and 5), the legend: axis-titles , the height of the graph (=5) and the width of each cell (=10),
Of course we can make this function much more fancy – to display other markers than just *, to attempt to draw a line, to allow multiple values per label, to use a second vertical axis, etc. For now we have enough to demonstrate the concept and once again the power of table functions. The code for the function graph is listed below:
create type string_table is table of varchar2(2000)
/
create type values_tbl is table of number
/
create or replace function graph
( p_src in sys_refcursor
, p_title in varchar2 default null
, p_labels_legend in varchar2 default null
, p_values_legend in varchar2 default null
, p_min in number default null
, p_max in number default null
, p_integer_values_only in varchar2 default 'N'
, p_graph_height in number default null
, p_label_width in number default null
) return string_table
is
l_rows integer:=nvl(p_graph_height, 20);
l_cell_width integer:=nvl(p_label_width, 5);
l_graph_width integer:=200;
retval string_table:= string_table();
l_labels string_table;
l_values values_tbl;
l_min number;
l_max number;
l_row integer;
begin
fetch p_src bulk collect into l_labels, l_values;
-- get max and min
select min(column_value)
, max(column_value)
into l_min
, l_max
from table( cast(l_values as values_tbl))
;
l_min:= nvl(p_min, l_min);
l_max:= nvl(p_max, l_max);
retval.extend;
retval(retval.last):= p_values_legend;
for rows in 1..l_rows+1 loop
retval.extend;
if p_integer_values_only='N' or ((l_max - (rows-1)*(l_max-l_min)/l_rows) = round(l_max - (rows-1)*(l_max-l_min)/l_rows))
then
retval(retval.last):= rpad(to_char(l_max - (rows-1)*(l_max-l_min)/l_rows),10) ||rpad(' |',l_graph_width);
else
retval(retval.last):= rpad('. ',10) ||rpad(' |',l_graph_width);
end if;
end loop;
-- calculate row for a value: l_rows - (value - l_min)/(l_max - l_min)*l_rows + 1 (e.g. max = 4500, min = 500, l_rows = 20; value = 1000 )
for value in 1..l_values.count loop
l_row:= l_rows - round((l_values(value) - l_min)/(l_max - l_min)*l_rows) + 2; -- the first rows holds the values legend
retval(l_row):= rpad( substr(retval(l_row), 1,13 + l_cell_width* (value -1))||rpad('*',l_cell_width), l_graph_width);
end loop;
retval.extend;
retval(retval.last):= rpad('-',10+l_graph_width,'-');
retval.extend;
retval(retval.last):= rpad(p_labels_legend,10+l_graph_width,' ');
for label in 1..l_labels.count loop
retval(retval.last):= substr(retval(retval.last), 1,13 + l_cell_width* (label -1))||rpad(substr(l_labels(label),1,l_cell_width),l_cell_width);
end loop;
return retval;
end;
/
Download the source for table function graph
Related posts:
Poor man's VPD – Virtual Private Database before 8i and in Standard Edition Databases Oracle 10gR2 – Conditional PL/SQL Compilation 
Dynamic SQL Pivoting – Stealing Anton’s Thunder Putting Analytical Functions to good use – find tables with multiple foreign keys to the same referenced table 
Oracle Open World / Java One Preview bij AMIS
[…] Lucas Jellema von amis technology corner beschreibt, wie man in SQL*Plus den Output ASCII-graphisch darstellen lassen kann. Absolut verrückt! Ich denke da an unsere alten Sharp PC-1403H zurück, auf deren Pixeldisplay die ganz Verwegenen auch Graphen ausgeben konnten. […]
[…] rnative, 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 […]