Using SQL Developer with the Google Charts API

Submitted by dave on Sat, 07/28/2012 - 10:37

For a while Google has offered a service where you can send it a URL and get back a chart. It seems all good ideas at Google get squashed so this will only be guaranteed to be available until April 2015. But until then it can be a quick/easy way to get a chart into a SQL Developer User Defined Report using the PL/SQL DBMS_OUTPUT type.

SQL Developer has a few simple charts and gauges available already but this API can give you ability to create Line, Bar, Pie Charts, Map, Scatter, Venn Diagrams, Radar Charts, QR Codes and Meters.

How it works
Build a url and submit it as an IMG tag in your web page. Google will generate a PNG image and return it as a response to the http image request. Your SQL Developer installation must be able to hit the chart.googleapis.com URL so internet access is required when you generate your report.
The general form is:

https://chart.googleapis.com/chart?cht=<chart_type>&chd=<chart_data>&chs=<chart_size>&...additional_parameters...

So when I added this line to the source of this page:

<img src="http://chart.googleapis.com/chart?chs=250x100&chd=t:60,40&cht=p3&chl=Hello|World">

We get this returned as a result:

Pretty cool huh? It is an easy way to dress up your boring lists of facts and figures, and free to boot.
So how do we use this in PL/SQL?
Well if we're outputting HTML in a PL/SQL User Defined Report, we just have to take some time to build the URL. Here is some code to do something silly, but it will illustrate the point. We are going to create a pie graph to show the percentage of users that are using each tablespace as default. I am using my handy Developer Days VM as a source.

An example of how to create a PL/SQL DBMS_OUTPUT report is here. Use the following source code for this example: 

-- David Mann
-- http://ba6.us
-- 28-JUL-2012

-- Simple Google Chart Example
-- Requires access to DBA_USERS
-- Spool output to file and view with browser 
-- or use SQL Developer PL/SQL DBMS_OUTPUT report type

--SET SERVEROUTPUT ON
DECLARE
  -- Vars we will use to build the URL
  chart_url   VARCHAR2(2048);
  chart_data  VARCHAR2(2048);
  chart_label VARCHAR2(2048);
  -- Skirt around SQLPLUS/SQLDev substitution variable issues by using this
  amper CHAR(1) := CHR(38);
BEGIN
  FOR cur IN
  (SELECT
      DEFAULT_TABLESPACE,
      COUNT(*) AS CNT
    FROM
      DBA_USERS
    GROUP BY
      DEFAULT_TABLESPACE
    ORDER BY
      2 DESC)
  LOOP
    -- Prepare Label list for URL
    chart_label := chart_label || cur.DEFAULT_TABLESPACE || '%20('||cur.CNT||')|';
    -- Prepare Data list for URL
    chart_data := chart_data || cur.CNT || ',';
  END LOOP;
  -- Remove the last delimeter from the string
  chart_label := SUBSTR(chart_label,1,LENGTH(CHART_LABEL)-1);
  chart_data  := SUBSTR(chart_data,1,LENGTH(CHART_DATA)  -1);

  -- Build the URL
  chart_url := chart_url || 'http://chart.apis.google.com/chart?chs=400x300';
  chart_url := chart_url || amper || 'cht=p3';
  chart_url := chart_url || amper || 'chd=t:' || CHART_DATA;
  chart_url := chart_url || amper || 'chds=a';
  chart_url := chart_url || amper || 'chdl=' || CHART_LABEL;
  chart_url := chart_url || amper || 'chtt=Default+Tablespace+Usage+Counts';
  chart_url := chart_url || amper || 'chf=a,s,000000|bg,lg,0,EFEFEF,0,BBBBBB,1';
  chart_url := chart_url || amper || 'chp=3.14';
  chart_url := chart_url || amper || 'chdlp=b';

  
  DBMS_OUTPUT.PUT_LINE('URL Detail');
  
  DBMS_OUTPUT.PUT_LINE('Chart Data: '||chart_data||'');
  
  DBMS_OUTPUT.PUT_LINE('Chart Labels: '||chart_label||'');
  
  DBMS_OUTPUT.PUT_LINE('Chart URL: '||chart_url||'');

  DBMS_OUTPUT.PUT_LINE('Our Graph!');
  DBMS_OUTPUT.PUT_LINE('');
END;

And here is what it will look like when you run it in SQL Developer (Click to enlarge):

Tips

  • Make sure your ampersands are handled so SQL Developer doesn't pick them up as substitution variables
  • Make sure spaces in the URL are escaped as %20. This will keep you from submitting part of a URL and getting back a broken image icon result from the API
  • Get close to the look you want using the Chart Wizard or Live Chart Playground, then use the parameters it shows you to work into your PL/SQL code

References