It is possible to integrate Oracle APEX quite neatly with the EJS Charts (http://www.ejschart.com/), which personally I find one of the best charting solutions for the "Web 2.0" interactive charting era, well able to compete with the JavaScript and Flash (AnyCharts) charting used by websites like http://www.reuters.com/finance/stocks and http://uk.finance.yahoo.com/echarts. It is based entirely on JavaScript and enables you to interactively manipulate the charts on your web pages - and includes functions out-of-the-box including zooming, panning and enabling/disabling data series without resubmitting the web page. It is all very-very interactive. Just try your mouse wheel for zooms, right-click and drag for pan and double-click to reset.
In this posting I will show you how this can be achieved. If you click on the embedded picture you will get an idea of the strength of the EJS Charting sofware.
When I started out on this work I used the Personal Edition while evaluating the charting itself. When using the Personal Edition you "loose out" on a number of different charts (including OHLC). But I was so thoroughly impressed at the end of my evaluation that I purchased the Professional Edition.
There are a number of different ways in which EJS Chart can receive the data sets (series) for charting purposes, including XML and AJAX based data sources, CSV, JSON etc. However, the one I found the easiest to integrate with, is to dynamically construct JavaScript arrays from within Oracle APEX using PL/SQL - and to print the JavaScript code required in an anonymous PL/SQL block in an Oracle APEX reqion. While the array approach will not enable you to refresh the data on-the-fly without submitting the page I have not yet had the need for this functionality because all the data is readily available to be zoomed, panned, series enabled and disabled on the fly etc. anyway.
In my current use it would simply make the application more complex - and would likely require writing a servlet or equivalent to service the retrieval of data sets via AJAX on-the-fly.
So, how to integrate EJS Chart and Oracle APEX? EJS Chart requires very simple JavaScript constructs to add charts inside HTML pages. You will see how later on. So to get started, simply follow the instruction below.
1. You will need to ensure that your EJS Charting distribution has been installed and is accessible to your web browser. The easiest way to do so is set-up a web server that services the pages from a static directory on your file system. In my case I use the Apache web server as a reverse proxy to Oracle APEX and to serve the EJS Chart files (/charting is an alias to a file system directory containing the EJS Chart distribution). Please read the EJS Chart installation instructions on how to do so - you will also be able to make use of the examples provided in the instructions in order to ensure that the EJS Chart software has been properly installed, before continuing to use the instructions below to integrate EJS Chart and Oracle APEX.
2. Create a new page template called EJS Charting. Edit that page template and include the EJS Chart .js files in the header definition. If you have got the Professional Edition and wish to use the more advanced charts then you will need to include additional .js files depending on your requirements. In my case I required the following lines to be added to the EJS Charting page template header:
<script type="text/javascript" src="/charting/EJSChart.js"> </script>
<script type="text/javascript" src="/charting/EJSChart_Stock.js"> </script>
3. Create a new page using the EJS Charting template and add an anonymous PL/SQL reqion.
4. Create a function to generate JavaScript array format. In this case we want a generic function, which will render the data into JavaScript arrays (2-dimensional, e.g. [ ['1','1'], ['2','2'] ]). In my case I needed a generic function that would render up to 5 columns of data (OHLC charts: date, open, high, low and close). I'm therefore relying on the Oracle SYS_REFCURSOR for weak typing. The only prerequisite to the function is that all the columns sent to the function are implicitly convertible to VARCHAR2 - and that exactly 5 columns are returned by the REF CURSOR (ranging value1 - value5 in the code). If only 2 columns are needed for a series (e.g. time and share close value, as in my example) then hardcode the remaining three columns as null.
An example of a valid query for a REF CURSOR is provided below. Be advised that EJS Chart requires dates to be milliseconds since 01-JAN-1970 - hence the millisecond calculation in the value1 column.
select (sysdate + rownum - TO_DATE('01-JAN-70', 'DD-MON-RR')) * 86400000 value1
, rownum value2
, null value3
, null value4
, null value5
from all_objects
where rownum < 100
The function for generating the JavaScript Array data is provided below.
create or replace FUNCTION GENERATE_EJSCHART_DATASET (dataset SYS_REFCURSOR)
RETURN CLOB AS
d CLOB := '';
i NUMBER := 0;
value1 VARCHAR2(32);
value2 VARCHAR2(32);
value3 VARCHAR2(32);
value4 VARCHAR2(32);
value5 VARCHAR2(32);
BEGIN
d := d || '[';
LOOP
FETCH dataset
INTO value1, value2, value3, value4, value5;
EXIT WHEN dataset%NOTFOUND;
i := i + 1;
IF (i > 1) THEN
d := d || ',';
END IF;
d := d || '[';
-- There always have to be at least 2 values
d := d || '''' || value1 || '''';
d := d || ',''' || value2 || '''';
IF (value3 IS NOT NULL) THEN
d := d || ',''' || value3 || '''';
END IF;
IF (value4 IS NOT NULL) THEN
d := d || ',''' || value4 || '''';
END IF;
IF (value5 IS NOT NULL) THEN
d := d || ',''' || value5 || '''';
END IF;
d := d || ']';
END LOOP;
CLOSE dataset;
d := d || ']';
RETURN d;
END GENERATE_EJSCHART_DATASET;
5. We need a procedure that will "buffer print" CLOBs. The htp.prn procedure coming with the PL/SQL Web Toolkit in the Oracle database will only allow up to 8192 characters to be printed at a time, which is too small a number in my case. So this function below will split a CLOB up into to 256 character fragments and htp.prn them one at a time.
create or replace PROCEDURE HTP_BUF_PRN
( p_text IN CLOB
) AS
l_buflen INTEGER := 256;
BEGIN
FOR i IN 0 .. FLOOR(LENGTH(p_text)/l_buflen) LOOP
-- HTP.PRN will only accept strings up to 8192 characters - hence this function is needed
htp.prn(substr(p_text, i * l_buflen + 1, l_buflen));
END LOOP;
-- htp.prn(substr(p_text, LENGTH(p_text) - (LENGTH(p_text) mod l_buflen), LENGTH(p_text) mod l_buflen));
END HTP_BUF_PRN;
6. Edit the PL/SQL region, generate the data set and finally "print" the JavaScript and data series. An example of this is provided below. Be advised that this example requires a security_day_prices table to exist - but you could just as readily use the example query above (all_objects) in order to quickly plot your first EJS Chart.
declare
s_close CLOB;
rc_close SYS_REFCURSOR;
begin
open rc_close FOR
select (value_date - TO_DATE('01-JAN-70', 'DD-MON-RR')) * 86400000 value1
, round(close, 2) value2
, null value3
, null value4
, null value5
from security_day_prices
where security_id=:A_SECURITY_ID order by value_date)) d
and value_date between '01-JAN-2009' and '01-FEB-2009';
s_close := generate_ejschart_dataset(rc_close);
htp.prn('
<div id="closechart" style="width:800px; height:180px;">
</div>
<script type="text/javascript">
var chart = new EJSC.Chart(
"closechart"
, { axis_bottom: { caption: "Date" }, axis_left: { caption: "Price" }, show_titlebar: true, title: "Close chart", legend_state: "minimized" }
);
chart.addSeries(new EJSC.LineSeries(
new EJSC.ArrayDataHandler(
');
HTP_BUF_PRN(s_close);
htp.prn('
)
, { title: "Close" } ));
chart.axis_bottom.formatter = new EJSC.DateFormatter(
{
format_string: "DD-MMM-YY"
, useUTC: true
}
);
</script>
');
end;
Caveats:
- My current approach, for the sake of simplicity, only enables data sets each of up to 32kb (in clear text) to be generated because the code returns a CLOB. However, the code could be further "pipelined" to handle this by running htp.prn more often on data sets
- Be advised that htp.prn will only print out 8kb of data at a time - otherwise you'll get an ORA-6502 error, which doesn't look initially as if it's got anything to do with the problem.
No comments:
Post a Comment