May 18

I had a requirement today to pass through the REGION_ID value for every row in an APEX report to perform a row action using javascript, i.e. delete comments. In this case I was deleting a comment via AJAX and reloading the APEX report using $a_report, which requires the REGION_ID parameter to be passed in. Hence the topic of this post….

Unfortunately the #REGION_ID# substitution string is not replaced within the query source before/after the query is executed by the APEX engine (it would be nice if this was supported including the #REGION_STATIC_ID#) so what we get in our HTML report output is the raw substitution string #REGION_ID#. Now there were a couple of ways to workaround this problem but the simplest one I found was to do a join against “APEX_APPLICATION_PAGE_REGIONS” within the region query itself. e.g.

SELECT '<table><tr><td><strong>Comment By:</strong> '||portal_security.get_user_full_name(:APP_USER)||', '||to_char(updated_on, :APP_DATE_TIME_FORMAT)||'</td></tr><tr><td>'||comments||'</td></tr><tr><td><div><a href="#" onclick="Ext.app.deleteComment('''||r.region_id||''','''||comment_id||''','''||wwv_flow_item.md5(comment_id,comments)||''');">Delete</a></div></td></tr></table>' tech_comment
FROM   cmgmt_comments_vw
,      apex_application_page_regions r
WHERE  cust_id          = : P13_CUST_ID
AND    r.region_name    = 'Recent Sales Comments'
AND    r.application_id = :APP_ID
AND    r.page_id        = :APP_PAGE_ID
ORDER BY created_on desc

The only issue in the above query is that we do a filter on the region name, which if changed will break the query. So it’s important for us to document this so it’s not overlooked when changes occur to the region title. It also requires that we have a unique region title as well which may not always be the case, so a secondary filter would be added to the query to ensure we only ever return the one row/region we are interested in as we use a cartesion join (maybe display sequence etc.). Not exactly elegant but it’s the solution/workaround for now :) Here’s an example screenshot of the report:

Apr 28

Just a quick post on a benefit of using APEX and Ext JS. Enabling multiple grid dependencies is achieved really quite simply, all we need to do is update the session state page/application items which the underlying report/grid queries rely on as they’re defined as bind conditions within the WHERE clause e.g. “WHERE server_id = : P10_SERVER_ID”. Once session state is updated we then call the reload method for the dependent Grid store(s). The code to achieve this is really quite simple and only requires coding a few lines ( assuming you already have a handler function for APEX AJAX calls and updating session state for multiple items, if you don’t then you can use the one at the bottom of this post ).

Note: this code has been extracted from our integration design so you would implement it slightly differently. The aim is just to give you an idea of what’s involved:

{
   "rowSelectFunction": function (rowIndex, record) {
      Ext.app.apexHttpPost('Do Nothing', ['P#PAGE_ID#_CLUSTER_ID', 'P#PAGE_ID#_SERVER_ID'], [Ext.util.Format.stripTags(record.get('CLUSTER_ID_DISPLAY')), '']);
      Ext.getCmp('grid#REGION_ID_REPLACE_1#').getStore().reload();
      Ext.getCmp('grid#REGION_ID_REPLACE_2#').getStore().reload();
   },
   "rowDeSelectFunction": function (rowIndex, record) {
      Ext.app.apexHttpPost('Do Nothing', ['P#PAGE_ID#_CLUSTER_ID', 'P#PAGE_ID#_SERVER_ID'], ['', '']);
      Ext.getCmp('grid#REGION_ID_REPLACE_1#').getStore().reload();
      Ext.getCmp('grid#REGION_ID_REPLACE_2#').getStore().reload();
   }
}
.....
selModel = new Ext.grid.RowSelectionModel({
   singleSelect: true
});
selModel.on("rowselect", function (selectionModel, rowIndex, record) {
   selFunc.call(selectionModel, rowIndex, record);
});

The result is we can refresh two dependent grids via AJAX on a row selection event within a 3rd grid ( or we could have as many dependencies as we want :) )e.g.

Here’s the basic apexHttpPost function if you don’t have a similar function already…. mind you I usually prefer using something more robust like the design of jApex from Tyler Muth, but this still does the job.

// Used for setting item session state for 1 or more items
Ext.app.apexHttpPost = function (pAppProcess,pItems,pItemValues)
{
  var get = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS='+pAppProcess,$v('pFlowStepId'));
  if (typeof(pItems) == 'object') {
    for ( var i=0, len=pItems.length; i<len; ++i ){
      try {
        get.add(pItems[i],pItemValues[i]);
      } catch(e) {
      }
    }
  } else {
    if (pItems) {
      try {
        get.add(pItems,pItemValues);
      } catch(e) {
      }
    }
  }
  gReturn = get.get();
  get = null;
  return gReturn;
}

For the non EXT users, you could still provide the same design/facility for report drill downs by using $a_report instead of the store reload call.

Oct 14

I was working on a customer’s application today and made a change to a report which used the “export:CSV” report template. Unfortunately the change I introduced caused the following error “report error: ORA-06502: PL/SQL: numeric or value error: character string buffer too small”.

To quickly diagnose the problem I set the template back to a standard report and confirmed that there was nothing wrong with the query as the report displayed the results. Unfortunately I needed this report to output in CSV so I was stuck, I tried changing a few things in the query but had no luck and there were no obvious messages in any logs as to what the problem was (also “export:XML” worked fine).

So I needed a workaround and fast as I was on a tight deadline. I didn’t like the idea of writing this in PLSQL as it would be time consuming as I had over 40 bind values to deal with and 20 table joins in the report so I decided to create my own CSV report template and add in a little PLSQL magic which I’ve used previously (and borrowed from others).

My report template it looked like this (Note: the page template can be set to anything)…..

APEX Custom CSV Export - Report Template

Note: “After Each Row” I actually put in a carriage return… funny thing is that its removed every time I edit the report, so I need to add it back in every time I make a change!

The page regions look like this….

APEX Custom CSV Export - Regions

and the PLSQL within them contain this….

Mime Header:

htp.init;
owa_util.mime_header( 'application/octet', FALSE );
htp.p('Content-Disposition: attachment; filename="report.csv"');
owa_util.http_header_close;

Stop Processing:

htmldb_application.g_unrecoverable_error := true;

Also make sure you disable any column sorting, otherwise you’ll get HTML markup in your column headers in the CSV file.

The resulting “report.csv” file is not perfect as it contains a blank line before and after the column headers but it’s close enough and still satisfied my customer, so in the end it was success even if it was a little inelegant, point is that I produced this workaround in 10 mins which literally saved my butt! Verdict is: APEX is flexible in so many ways from my experience so there’s always a solution to every problem question is how much pain it involves, in most of my cases not a lot jut a little trick or two!

Aug 13

We’re all eagerly anticipating the release of APEX 4.0, and we can’t wait to see the plugin functionality Patrick Wolf is building into the product. We’ve seen a few people looking to host a plugin registry and we thought we’d throw our hat into the ring and let the community decide which will be the best one to use. We’ve decided to build a plugin registry using our APExt JS Framework as it seems like the perfect choice as we’ll be creating Ext plugins post release of APEX 4.0 (we’re looking to submit the plugin registry for the APEX developer competition, but chances are it will get disqualified as it’s a little grey around third party licensing)

Today’s post focuses on an issue we found with the row expander grid plugin. Some background first: we are building a simple report which will break the plugins into categories using Ext grouping and will provide row expanding functionality for the plugin comments. (See the following image for a basic example).

APEX Plugin Registry

We disabled the column headings in the Grid to make it look more like what you see on the Ext 3.0 samples page setting the “hideHeaders” attribute to true. In the example we actually have two columns, 1 for the plugin image and the other for the plugin details. In order to obtain the formatting for the plugin details we needed to embed some HTML into our SQL query. e.g.

select * from (
SELECT 'Some information to come soon!' comments
,      pc.name category
,      p.name  plugin_name
,      '<img src="&EXT_BASE_DIR./../../../apr/img/'||p.image||'" width="100" />' image
,      '<table cellpadding="0" cellspacing="0" summary=""><tr ><td style="padding-bottom:10px;"><span style="font-size:16px;font-weight:bold;">'||p.name||'</span></td></tr></table>'||
       '<table cellpadding="0" cellspacing="0" summary=""><tr ><td style="width:85px;">'||
       '<b>Description </b></td><td style="width:8px;"><b>:</b></td><td>'||substr(p.description,1,100)||
       '</td></tr><tr><td><b>Version </b></td><td><b>:</b></td><td>'||p.version||
       '</td></tr><tr><td><b>'||'Author </b></td><td><b>:</b></td><td>'||u.known_as||
       '</td></tr><tr><td><b>APEX Version </b></td><td><b>:</b></td><td>'||p.required_version||
       '</td></tr><tr><td><b>'||'Downloads </b></td><td><b>:</b></td><td>'||nvl(downloads_total,0)||
       '</td></tr><tr><td><b>'||'Rating </b></td><td><b>:</b></td><td>'||
       jquery_widgets.get_stars('f'||lpad(to_char(rownum),2,'0'),round(p.average_rating))||'</td></tr></table>' details
FROM   apr_plugins           p
,      apr_plugin_categories pc
,      apr_users             u
WHERE  p.category_id = pc.category_id
AND    p.author_id   = u.user_id)
where (
 instr(upper("CATEGORY"),upper(nvl(:P4_CATEGORY,"CATEGORY"))) > 0  and (
 instr(upper("PLUGIN_NAME"),upper(nvl(:P4_REPORT_SEARCH,"PLUGIN_NAME"))) > 0  or
 instr(upper("DETAILS"),upper(nvl(:P4_REPORT_SEARCH,"DETAILS"))) > 0
))

It gave us the look we were wanting to achieve, however the side affect was that it broke the row expander plugin because we were embedding a HTML table in one of the row columns, i.e. the detail column.. The problem was due to the following call on line 116 of the rowexpander.js plugin

var body = Ext.DomQuery.selectNode('tr:nth(2) div.x-grid3-row-body', row);

Basically body was “undefined” and we received a js error. The fix, after a little tedious debugging, was to change the CSS selector to the following:

var body = Ext.DomQuery.selectNode('tr:nth-child(2) div.x-grid3-row-body', row);

Essentially we just made sure that the CS selector looked at the direct children of the parent root node.

Note: you’ll also see that we’re using the jQuery star rating plugin that Roel Hartman blogged about, it’s a nice little plugin…. it shows that you can mix and match functionality from multiple javascript frameworks.

Jul 09

A bit of housekeeping first, apologies! We have disabled the comments on most of (if not all) of our posts due to the large amounts of spam we were receiving, however I have had a change of heart as I saw an interview last night on a news channel that was talking about blog sites that do this don’t allow the community to criticise or point out mistakes and I had to agree (especially since some comments on posts I’ve read have more weight than the blog post itself), so as of today I’m re-enabling them and welcome any feedback… just not the spam!!

The official post: The great thing about good frameworks is how easy it is to reuse code across your application and how little code you need to write, Ext is basically the gold standard in my opinion and with Ext 3.0 now officially out it has even more great functionality we can’t wait to get our hands dirty with, but for now we’re continuing with 2.2.1 and will deliver our integration kit at this version level.

Today’s post is focused on using the “Form on a Table with Report” components and modifying the report column links to open the anchor href in a new Ext modal window with maximization capability. It is a very simple and straight forward approach which reuses the form page created by the wizard simply by opening it within an iframe in the Ext window (we use a “No Tabs” style page template). The juicy bit is that when we close the form after an update we reload the report/grid which issued the window open call (well it’s actually the Ext Store that performs the reload). This gives us the flexibility of having multiple grids on a page with edit (popup) ability but when we change data and close the window we only perform a “partial page refresh” of the report/grid caller. Thus reducing demand on the server as we are not navigating away from our report page and simply just grabbing a JSON data object for the report/grid reload.

APEX ExtJS - APEX Form Component Selection

The basic framework is as follows…..

  • We create our Ext Window javascript function
    Ext.app.apExtWindow = function (pId, pTitle, pHref, pGrid, pWidth, pHeight) {
       var apExtWindow = new Ext.Window({
          id: 'window-' || pId,
          renderTo: Ext.getBody(),
          title: pTitle,
          modal: true,
          layout: 'anchor',
          width: pWidth,
          height: pHeight,
          maximizable: true,
          autoScroll: true,
          closeAction: 'close',
          plain: true,
          html: '<iframe id="iframe-' + pId + '"src="' + pHref + '" style="overflow:auto;width:100%;height:100%;" frameborder="0"></iframe>',
          listeners: {
             destroy: function () {
                if (pGrid) {
                   Ext.getCmp(pGrid).getStore().reload();
                }
             }
          }
       });
       apExtWindow.show();
    }
    
  • We define our Ext transform function for the anchor link, which traverses the DOM focusing on our report region (the Ext.get part) and looks for any anchor links within which match our criteria.
    Ext.app.anchorTransformById = function (pGridId, pRegionId) {
    /* Buggy code, always opened the window with the last anchor href in the grid
       Ext.get(pGridId).select("a[class*=ext-window]").each(function (el) {
          if (el.dom.className.indexOf("aext-anchor") == -1) {
             el.dom.className += " " + "aext-anchor";
             el.on('click', function (e) {
                e.stopEvent();
                Ext.app.gridRowAction(pRegionId, "Window", el.dom.href, pGridId, '', '', 400, 600);
             });
          }
       });
    */
       Ext.get(pGridId).select("a[class*=ext-window]").on('click', function (e) {
          e.stopEvent();
          var el = Ext.get(e.getTarget('a')) Ext.app.gridRowAction(pRegionId, "Window", el.dom.href, pGridId, '', '', 400, 600);
       });
    }
    Ext.app.gridRowAction = function (pId, pTitle, pHref, pGrid, pUpdateItem, pUpdateItemWith, pWidth, pHeight) {
    	if (pUpdateItem) { Ext.app.apexHttpPost('Do Nothing',pUpdateItem,pUpdateItemWith); }
    	Ext.app.apExtWindow(pId, pTitle, pHref, pGrid, pWidth, pHeight);
    }
    
  • We update our Ext grid javascript to perform the transform after each time the store loads
    pStore.on({
       load: function () {
          Ext.app.anchorTransformById('grid' + pRegionID, pRegionID);
       },
       columnmove: function () {
          Ext.app.anchorTransformById('grid' + pRegionID, pRegionID);
       }
    });
    
  • We update our column and add the class setting to the link attributes
    Column Link Settings

In summary, this is a write once approach. This functionality is available application wide to any link in any report which has our Ext grid template enabled. To enable the functionality we simply add the class attribute to the column link and to disable we simply remove the class setting, simple. The end result looks like this……

APEX ExtJS - Report column links that open in an Ext window

preload preload preload