Search notes:

Oracle SQL example: Bar charts

The following example uses the Unicode characters FULL BLOCK, LEFT SEVEN EIGHTHS BLOCK, LEFT THREE QUARTERS BLOCK etc., (found in the Block Elements code block) to create a bar chart from a series of numbers.

The with clause defines a local function (bar) that uses the mentioned Unicode characters to create a bar whose length corresonds to val.

The «magic» value 14849680 is the UTF-8 value for the character that follows the 8 characters with which the bar is drawn. So, by subtracting a value between 1 and 8 from 14849680, I get the block character with the desired width.

with
   function bar(val number) return varchar2 is
      val_8 pls_integer;
      val_r pls_integer;
   begin
      val_8 := trunc(val/8);
      val_r := val - 8*val_8;
      return substr(
         lpad(
           chr(14849680-8), val_8 , chr(14849680-8)
         ) || case when val_r > 0 then chr(14849680-val_r) end,
         1, 150
       );
   end;
rnd as (
   select
      100 + 100* sin(2*3.14156 / 48 * (level-13)) + dbms_random.value(0,40)  val
   from
      dual connect by level <= 48
)
select
   to_char(val, '990.00') val,
   bar(val)               bar
from
   rnd
/

The result of the statement, when executed in SQLcl, is shown below. (I tried the same thing with SQL*Plus and setting NLS_LANG to American_America.UTF8, but the output was not rendered as nicely).

Oracle SQL example: Bar charts

See also