I’m in the process of developing a customized reporting solution using interactive reports for an existing customers application developed in APEX 3.2. Whilst they were pleased with the functionality offered by IR, they required the ability to grant access on certain reports to selected users. Now this is available in 4.0 but they are not in a position to upgrade so my hands are basically tied. To facilitate this I’ve followed the details in the following APEX forum thread and there’s also a blog post which you can reference.
In addition to the granting of reports they also require a few changes to the IR charting feature. (this also applies to 4.0)
- Adding a title to the chart
- Adding the point values to the chart
- Changing the label rotation for the vertical bar chart
- Changing colours
- Adding in drill own links to the data
So to devise a workaround, back in February (this is when the customer first requested the change, approval to start came in November, they move quickly
I wrote a theoretical approach to try and override the XML chart output.
Putting this theory into practice this week I came up with the following code prototype (Unfortunately I’m not able to provide the final solution for commercial reasons). It should give you a good idea of what needs to occur, I’d recommend making a few changes to the XML_CHART_API package as it’s only written to give you an idea of what to do….
create or replace
PROCEDURE FLASH3
( p IN VARCHAR2
, preloaderLoadingText IN VARCHAR2 DEFAULT NULL
, swffile IN VARCHAR2 DEFAULT NULL
, preloaderInitText IN VARCHAR2 DEFAULT NULL
, XMLCallDate IN VARCHAR2 DEFAULT NULL
)
--
-- URL rewriting See - http://www.yourhtmlsource.com/sitemanagement/urlrewriting.html
--
-- RewriteEngine on
-- RewriteRule ^pls/apex/apex_util.flash2(.*) /pls/apex/flash3$1
--
-- grant execute on #OWNER#.flash3 to apex_public_user;
-- create public synonym flash3 for #OWNER#.flash3;
--
-- update wwv_flow_epg_include_mod_local
--
AS
v_out_buf htp.htbuf_arr;
v_out_row_count NUMBER := 99999999;
v_clob CLOB := empty_clob();
v_xml_start_found BOOLEAN DEFAULT FALSE;
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
sys.htp.prn(substr(p_text, i * l_buflen + 1, l_buflen));
END LOOP;
END htp_buf_prn;
BEGIN
dbms_lob.createtemporary(lob_loc => v_clob, cache => true, dur => dbms_lob.call);
apex_util.flash2( p => p
, preloaderLoadingText => preloaderLoadingText
, swffile => swffile
, preloaderInitText => preloaderInitText
, XMLCallDate => XMLCallDate
);
owa.get_page(v_out_buf, v_out_row_count);
htp.init;
FOR i IN 1..v_out_row_count LOOP
--
-- Lets look for the start of our XML document and ignore any of the mime header
-- X-ORACLE-IGNORE etc.
--
IF instr(v_out_buf(i), '<?xml') > 0 OR v_xml_start_found THEN
dbms_lob.writeappend(lob_loc => v_clob, amount => length(v_out_buf(i)), buffer => v_out_buf(i));
v_xml_start_found := TRUE;
END IF;
END LOOP;
xml_chart_api.chart_mods
( p_in_out_chart_xml => v_clob
);
htp_buf_prn(v_clob);
END FLASH3;
/
create or replace
PACKAGE XML_CHART_API AS
PROCEDURE chart_mods
( p_in_out_chart_xml IN OUT NOCOPY CLOB
);
END XML_CHART_API;
/
create or replace
PACKAGE BODY XML_CHART_API AS
PROCEDURE chart_mods
( p_in_out_chart_xml IN OUT NOCOPY CLOB
)
AS
l_xml XMLType;
doc dbms_xmldom.DOMDocument;
nl DBMS_XMLDOM.DOMNodeList;
n DBMS_XMLDOM.DOMNode;
n2 DBMS_XMLDOM.DOMNode;
l_node DBMS_XMLDOM.DOMNode;
l_attr DBMS_XMLDOM.DOMAttr;
e DBMS_XMLDOM.DOMElement;
e2 DBMS_XMLDOM.DOMElement;
len NUMBER;
l_chart VARCHAR2(100);
BEGIN
-------------------------------
-- Turn our clob into XML doc
-------------------------------
l_xml := XMLType.createXML(p_in_out_chart_xml);
doc := dbms_xmldom.newDOMDocument(l_xml);
-------------------------------
-- Get the report type
-------------------------------
nl := DBMS_XMLDOM.getElementsByTagName(doc, 'chart');
len := DBMS_XMLDOM.getLength(nl);
-- loop through elements (should only be 1
FOR i IN 1 .. len LOOP
n := DBMS_XMLDOM.item(nl, i-1);
e := DBMS_XMLDOM.makeelement(n);
--
-- We need to know what type of chart, as label rotation is only
-- required for vertical chart
--
l_chart := DBMS_XMLDOM.getAttribute(e, 'plot_type');
END LOOP;
-------------------------------
-- Add a report drill down link
-------------------------------
nl := DBMS_XMLDOM.getElementsByTagName(doc, 'point');
len := DBMS_XMLDOM.getLength(nl);
-- loop through elements
FOR i IN 1 .. len LOOP
n := DBMS_XMLDOM.item(nl, i-1);
e2 := DBMS_XMLDOM.makeelement(n);
e:= DBMS_XMLDOM.createElement( doc, 'actions' );
--sys.htp.p('Debug: adding label');
n2 := DBMS_XMLDOM.makeNode(e);
e:= DBMS_XMLDOM.createElement( doc, 'action' );
DBMS_XMLDOM.setAttribute(e, 'type', 'navigateToURL');
--
-- Basic example of how to build up the URL for report drill down
-- this is where I would hand off to another function with the page number
-- and value to work out what items need setting in the URL
--
DBMS_XMLDOM.setAttribute(e, 'url', 'f?p='||v('APP_ID')||':1:'||v('APP_SESSION')||':'||DBMS_XMLDOM.getAttribute(e2,'name')||'_'||DBMS_XMLDOM.getAttribute(e2,'y'));
--
-- Lets open the drill down in a new window
--
DBMS_XMLDOM.setAttribute(e, 'target', '_blank');
l_node := DBMS_XMLDOM.appendChild(n2,dbms_xmldom.makeNode(e));
l_node := DBMS_XMLDOM.appendChild(n,n2);
END LOOP;
-------------------------------
-- Label Rotation & alignment
-------------------------------
nl := DBMS_XMLDOM.getElementsByTagName(doc, 'labels');
len := DBMS_XMLDOM.getLength(nl);
-- loop through elements
FOR i IN 1 .. len LOOP
n := DBMS_XMLDOM.item(nl, i-1);
-- cast our node into an element which we can manipulate
e := DBMS_XMLDOM.makeelement(n);
--DBMS_XMLDOM.setAttribute(e, 'display_mode', 'rotated');
CASE l_chart
WHEN 'CategorizedHorizontal' THEN
DBMS_XMLDOM.setAttribute(e, 'rotation', '0');
ELSE
DBMS_XMLDOM.setAttribute(e, 'rotation', '270');
END CASE;
DBMS_XMLDOM.setAttribute(e, 'align', 'inside');
END LOOP;
-------------------------------
-- Add point values
-------------------------------
nl := DBMS_XMLDOM.getElementsByTagName(doc, 'series');
len := DBMS_XMLDOM.getLength(nl);
-- loop through elements
FOR i IN 1 .. len LOOP
n := DBMS_XMLDOM.item(nl, i-1);
e:= dbms_xmldom.createElement( doc, 'label' );
DBMS_XMLDOM.setAttribute (e, 'enabled', 'true');
l_node := dbms_xmldom.appendChild(n,dbms_xmldom.makeNode(e));
END LOOP;
DBMS_XMLDOM.writeToClob(doc,p_in_out_chart_xml);
DBMS_XMLDOM.freeDocument(doc);
EXCEPTION
WHEN OTHERS THEN
DBMS_XMLDOM.freeDocument(doc);
sys.htp.p(SQLERRM);
END chart_mods;
END XML_CHART_API;
/
Additional Setup
- Apache mod_rewrite rule:
#
# change the PLS handler to the one defined for your DB
#
RewriteEngine on
RewriteRule ^pls/apex/apex_util.flash2(.*) /pls/apex/flash3$1 - grant execute on #OWNER#.flash3 to apex_public_user
- create public synonym flash3 for #OWNER#.flash3;
- update #APEX_SCHEMA#.wwv_flow_epg_include_mod_local function to include FLASH3
e.g.
if upper(procedure_name) in (
‘#OWNER#.FLASH3′,’FLASH3′) then
return TRUE;





The best part about APEX, compared with most other development products, is that our applications are just metadata. This gives us the benefit of managing and building our applications using SQL and using the APEX PLSQL API. This can really benefit you on larger applications. You can either update the definition of existing item(s)/region(s)/validation(s) etc. using a SQL DML update statement, or you could add a new region or item to every page in the application using the APEX PLSQL API (The easiest approach for this would be to create a dummy APEX page and create a PLSQL process on this page to issue the APEX PLSQL API calls. This way you’ll avoid all the setup requirement of running APEX code outside of the APEX engine. Every less line of code helps