create or replace procedure render_chart(p_sql in varchar2) 
is 
  type cursor_t is ref cursor;
  v_cursor          cursor_t;

  v_width           pls_integer := 1000;
  v_height          pls_integer := 400;

  v_margin_left     pls_integer := 10;
  v_margin_right    pls_integer := 10;
  v_margin_top      pls_integer := 50;
  v_margin_bottom   pls_integer := 10;

  v_chart_space_top pls_integer := 50;

  v_axis_left       pls_integer := 50;
  v_axis_bottom     pls_integer := 50;

  v_chart_width     pls_integer;
  v_chart_height    pls_integer;

  v_bar_space       pls_integer := 1;
  v_bar_width       pls_integer := null;
  v_barheight       pls_integer;
  v_bar_count       pls_integer;

  v_max_value       number;

  v_image           varchar2(100);
  v_imagedata       blob;
  v_counter         pls_integer := 0;

  v_label           varchar2(4000);
  v_value           number;
begin
  v_chart_width := v_width - (v_margin_left + v_margin_right + v_axis_left);
  v_chart_height := v_height - (v_margin_top + v_margin_bottom + v_axis_bottom);

  execute immediate 
    'select round(max(value), -3) from ('||p_sql||')' 
  into v_max_value;

  execute immediate 
    'select count(*) from ('||p_sql||')' 
  into v_bar_count;

  if v_bar_width is null then 
   v_bar_width := trunc((V_chart_width - (v_bar_count * v_bar_space)) / v_bar_count); 
  end if;

  v_image := image_generator.create_image_handle(v_width, v_height);
  image_generator.set_background(
    p_img_name => v_image, 
    p_r => 200,
    p_g => 200,
    p_b => 200
  );
  image_generator.clear_image(v_image);
  image_generator.set_color(v_image, 255,255,255);
  image_generator.draw_filled_rect(
    p_img_name => v_image, 
    p_x => v_margin_left + v_axis_left + 1,
    p_y => v_margin_top + 1,
    p_width => v_chart_width - 1,
    p_height => v_chart_height - 1
  );
  image_generator.set_color(v_image, 0,0,0 );
  image_generator.draw_line(
    p_img_name => v_image, 
    p_x1 => v_margin_left + v_axis_left,
    p_y1 => v_margin_top,
    p_x2 => v_margin_left + v_axis_left,
    p_y2 => v_height - v_margin_bottom - v_axis_bottom + 2
  );
  image_generator.draw_line(
    p_img_name => v_image, 
    p_x1 => v_margin_left + v_axis_left - 2,
    p_y1 => v_height - v_margin_bottom - v_axis_bottom, 
    p_x2 => v_width - v_margin_right,
    p_y2 => v_height - v_margin_bottom - v_axis_bottom 
  );
  image_generator.set_color(v_image, 128, 128, 128);
  image_generator.set_stroke(v_image, 1, number_array(4,4));
  image_generator.draw_line(
    p_img_name => v_image, 
    p_x1 => v_margin_left + v_axis_left,
    p_y1 => v_margin_top + v_chart_space_top,
    p_x2 => v_width - v_margin_right,
    p_y2 => v_margin_top + v_chart_space_top
  );
  image_generator.set_color(v_image, 0, 0, 0);
  image_generator.set_font(v_image, 'Verdana' , null, 8);
  image_generator.draw_text(
    p_img_name => v_image, 
    p_x => v_margin_left + v_axis_left - 3,
    p_y => v_height - v_margin_bottom - v_axis_bottom + 8,
    p_text => '0',
    p_align => image_generator.TEXTALIGN_RIGHT
  );
  image_generator.draw_text(
    p_img_name => v_image, 
    p_x => v_margin_left + v_axis_left - 3,
    p_y => v_margin_top + v_chart_space_top + 8,
    p_text => v_max_value,
    p_align => image_generator.TEXTALIGN_RIGHT
  );
  open v_cursor for p_sql;
  loop
    fetch v_cursor into v_label, v_value;
    exit when v_cursor%NOTFOUND;
    v_barheight := (v_value / v_max_value) * (v_chart_height - v_chart_space_top);

    if v_value > (v_max_value / 2) then 
      image_generator.set_color(v_image, 128, 0, 255);
    else 
      image_generator.set_color(v_image, 0, 128, 255);
    end if;
    image_generator.draw_filled_rect(
      p_img_name => v_image, 
      p_x => v_margin_left + v_axis_left + v_bar_space + v_counter * (v_bar_width + v_bar_space),
      p_y => v_margin_top + (v_chart_height - v_barheight),
      p_width => v_bar_width,
      p_height => v_barheight
    );
    if v_value > (v_max_value / 2) then 
      image_generator.set_color(v_image, 128, 0, 255);
    else 
      image_generator.set_color(v_image, 0, 0, 0);
    end if;
    image_generator.set_font(v_image, 'Verdana', 'BOLD', 8);
    image_generator.draw_text(
      p_img_name => v_image, 
      p_x => v_margin_left + v_axis_left + v_bar_space + v_counter * (v_bar_width + v_bar_space), 
      p_y => v_height - v_margin_bottom - v_axis_bottom + 4,
      p_text => v_label,
      p_align => image_generator.textalign_LEFT, 
      p_degree => -90
    );
    image_generator.set_font(v_image, 'Verdana',null, 8);
    image_generator.set_color(v_image, 255, 0, 0);
    image_generator.draw_text(
      p_img_name => v_image, 
      p_x => v_margin_left + v_axis_left + v_bar_width + v_counter * (v_bar_width + v_bar_space), 
      p_y => v_margin_top + v_chart_height - v_barheight - 4,
      p_text => v_value,
      p_align => image_generator.textalign_LEFT, 
      p_degree => 90
    );
    v_counter := v_counter + 1;
  end loop;
  close v_cursor;
  v_imagedata := image_generator.get_image(v_image,'png');
  owa_util.mime_header('image/png', false);
  htp.p('Content-length: '||dbms_lob.getlength(v_imagedata));
  htp.p('Content-Disposition: inline; filename=chart.png'); 
  owa_util.http_header_close;
  wpg_docload.download_file(v_imagedata);
  image_generator.destroy_image(v_image);
end;
/  
