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

1. 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
2. grant execute on #OWNER#.flash3 to apex_public_user
3. create public synonym flash3 for #OWNER#.flash3;
4. 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;