Monday, November 09, 2015

I *almost* made it to the ODTUG Board of Directors ...

Last month was the annual election for the Board of Directors of ODTUG. And just like the year before, I was one of the 19 (!) candidates for one of the 5 spots. 
During the campaign and election periods I received a lot of positive feedback and my hopes were high - but the competition was extremely strong. During Oracle Open World the results were announced and I was notified I hadn't made it into the top 5 - alas.
And after receiving the detailed results it appears I was "that" close ...

Also, during the election period, I got some messages saying "if I could vote, I would vote for you". Of course that kind of support is helpful, but it would be better for me and for ODTUG when those people signed up as either a Individual or Corporate member - see the ODTUG website. For just $99 you get unlimited access to a wealth of knowledge and you support your favourite user group. So that's just $8.25 per month very well spent !

Thanks to the (large number of) people who voted for me. I hope I can count on your vote again if I'm running again this fall !

Tuesday, September 22, 2015

APEX Reports : Double click to edit

Apart from the Current Record Indicator in your APEX Reports, another question often pops up at my client sites. A lot of functionality is implemented using a Report and a (Modal) Form. But to access that Form, the user has to click exactly on the small link icon in the first column of the report. So can that be changed, making it more "accessible"?

Set the Link Attributes of the column you're using for the link (or the Link definition itself) to: 

On Page 0 / Global Page add a Dynamic Action that fires on "Double Click" on the jQuery Selector "tr.clickable". Note: This class was set using an After Refresh Dynamic Action, see the previous blogpost. You can also accomplish that without the Current Record Indicator, but you have to assign the class "clickable" to the TR - you can do that with just the "editlink" class as a selector.
The Dynamic Action should execute a JavaScript call:  
// Execute a click on the element that is used as anchor
apex.event.trigger( $(this.triggeringElement).find("a.editlink").children(":nth-child(1)") , "click");
This simulates a click on the anchor with the "editlink" class. And now your user can just open the Form with a double click somewhere in a row of the report.

Implementing a Current Record Indicator in your APEX Reports

Developers with an Oracle Forms background might still remember this nice feature: If you click on a row in an Oracle Form "report" then it can be highlighted - either the full row or just a small first item of the row. The main difference with the Oracle Forms "reports" and the APEX Reports is that in Forms a "report" is usually comparable to an APEX Tabular Form. But a similar feature is frequently asked for - especially at customer sites that are transforming from Forms to APEX.

With a few lines of code we can make these customers happy .... Let's get started.

First of all your query should contain one column (as "Plain Text") that describes the Primary Key - that might even be a concatenated set of columns. Now we have to tell APEX that this is the column that contains our PK. For a Standard Report just add the CSS class "rowlink" to the column (without the quotes) . For an Interactive Report we need to use the HTML Expression:
 <span class=”rowlink”>#PK-Column#</span>
Of course you have to replace "PK-Column" with the alias of the Primary Key column in your query. This column will be hidden when running the page so if you don want to see it, you have to name that column twice in your query.
That's the only thing you have to do for every report with a "Highlight Current Record" feature.

The wizardry will be implemented on Page 0 / Global Page. First of all add a hidden item P0_CURRENT_RECORD. Set these properties: Value Protected = No,  Source = Null (always).
Next, create a Dynamic Action that fires After Refresh of this JavaScript Expression:
$(".rowlink").closest("div.t-Region, div.t-IRR-region")
This expression returns the div (either Standard Report or Interactive Report) that contains a "rowlink").
And the Action is, execute this piece of JavaScript:

//Remove the alternating rows and row highlighting from the report
$('.t-Report--altRowsDefault, .t-Report--rowHighlight', this.triggeringElement).removeClass('t-Report--altRowsDefault t-Report--rowHighlight');

//Hide the rowlink column and header
var header = $(".rowlink").closest('td').hide().attr('headers');

// For each "rowlink" item
$(".rowlink").each( function(i,key){
  // Define a click handler for the TR that contains a   
  $(this).closest('tr').on("click", function(){ 
    $s('P0_CURRENT_RECORD', $(key).text());
  // Create a data-id attribute for each rowlink  
  $(this).attr("data-id", $(key).text())  

// Add a "clickable" class to the TR -- not for this feature, see next blog post

if ( !( $v("P0_CURRENT_RECORD") ) ) {
  // Issue a dummmy click on the first row
else {
  // Issue a dummmy click on the row containing the current PK
  $(".rowlink[data-id='"+ $v("P0_CURRENT_RECORD") +"']").closest("tr").click();    
Finally we need some CSS to actually see the highlighting in action - showing the current record with a light blue background that even changes slightly from color when hovering over it:
.t-Report tr.clickable { 
  cursor : pointer; 

.a-IRR-table tr td, 
.t-Report tr td {
  background-color: transparent;

.a-IRR-table tr.highlight,
.t-Report tr.highlight {
  background-color : lightblue;  

.a-IRR-table tr.highlight:hover td,
.t-Report tr.highlight:hover td{
  background-color: lightsteelblue;  

This code seems to work fine in most situations. There is an issue if you use something else than the Heading : "Fixed to None" property for an Interactive Report. And there might be a challenge when your page contains multiple reports.... but consider that as homework ;-)

Monday, September 21, 2015

Sometimes it works, sometimes it doesn't ...

Recently at a client site I ran into a strange issue. There was an APEX page where you can set some parameters and then a report would refresh according these new settings. But the strange thing was: Sometimes it worked perfectly, but sometimes it didn't. In the latter case one or more of the parameters seem discarded... WTF ??

So I dived into it and looked at the code. The parameter / refresh mechanism was implemented using a Dynamic Action as the picture below. 

So, setting the parameters was done using a JavaScript call. This was a simple "$.post" call to a PL/SQL procedure sending over some screen values. So what could possible be wrong here .... ???

<< think for a minute >>

If I run the page and looked at the network traffic going on when I was changing parameters, I got this result:

So the JavaScript "post" call - the upper one - finished after the refresh action! Both actions ran in parallel! Because JavaScript is (by default) ASYNCHRONOUS. It runs when it can. So sometimes the "post" action would finish before the refresh and the result would be ok. Sometimes it would finish "too late" and the result would not be ok.

Now we know what the problem is, how can we solve it?

The first option might be to change the JavaScript call to a PL/SQL call and check the "Wait for Result" option. When that's checked the processing will hold until the PL/SQL call is finished. But now an then it might be cumbersome to pass values from your page to PL/SQL if they're not "regular" form items (as in this case).
As an alternative you could use the jQuery.ajax() call instead of the "post". The "ajax" function has an "async" setting. Switching this to true will work as well. But this setting is deprecated as of jQuery 1.8. So that's not the way forward.
In this case the proper way is to use the success callback option of the "post" call to issue the next (refresh) action. In code that looks like:

$.post( "ofw_ajax.set_value"
      , { param1:$v("pInstance")
        , param2:"abc"
        , param3:"filter"
        , param4:"P336_STR_NR"
        , param5:$(this.triggeringElement).attr("id").substr(10)
        , param6:($(this.triggeringElement).prop("checked"))?1:0
      , function( data ) 
        { apex.event.trigger('#myRegion', 'apexrefresh');

Another option is to chain the done() function to the "post":

$.post( "ofw_ajax.set_value"
      , { params :... 
      ).done(function(){apex.event.trigger('#myRegion', 'apexrefresh');});

I noticed no difference in behaviour in both options, but there might be some subtle differences. Please post them in the comments if you know them.

After implementing these changes, the network looks like this, so both actions are processed synchronously:

And of course this resulted in the correct - and reliable - behaviour!

So if you develop in APEX and you need to use JavaScript, please understand how JavaScript works. And now - and use - your tools to check whether the result works the way it should.

Tuesday, September 08, 2015

Showing a success message after closing a modal dialog

APEX 5 comes with Modal Dialogs out of the box. Very neat. Especially for adding and changing data. And to minimise the number of time a user has to click, it could be useful to add a "Close Dialog" process after the actual data processing. When the data processing fails, the Dialog stays on top showing the error. When data processing runs fine, the Dialog is closed ... without any confirmation. And this might be scary for a shaky user.

So how can we provide the user some feedback? On Page 4 of the Sample Dialog Application you can see one solution: up on a Dialog Closed Event on the parent page it does a redirect to refresh the parent page appending the success message of the "Close Dialog" process. This has two drawbacks. First, it probably refreshes more than necessary. And second, if you're using multiple layers of dialogs (dialogs that open other dialogs) the message appears in the "parent dialog".

As an alternative you could follow these steps:
1. Create an Alert Region on the Inline Dialogs position on your Global Page (0). Assign a static ID "successMessage" to this region and define this styling for the region - either in the region header, a separate CSS file or your Theme style :

  position: fixed;
  top: 12px;
  right: 12px;
  max-width: 480px;
  z-index: 2000;
  opacity: 0.9;
  border-radius: 2px;
  display : none;

2. On that same Global Page, create a Dynamic Action on the "Dialog Closed" event. Set the Selection Type to "jQuery Selector" and use "body" as the jQuery Selector. Execute this JavaScript code when the result of the selector is true:

var lMessage =;
if (lMessage ){
  $('#successMessage_heading', top.document).html(lMessage);
  $('#successMessage', top.document).fadeIn(300);  

3. In the Modal Dialog itself : set the “Success Message” of the Close Dialog Process to something like "Changes saved" or anything else that makes sense to your user.

Now your user gets a nice feedback in the upper left corner of the (main) page that automatically fades away after 3 seconds. The only thing you have to do is adding a success message to the Close Dialog process.

Wednesday, August 19, 2015

Expert Oracle Application Express, 2nd Edition, is out now !

It took a while, but last month the second edition of the Expert Oracle Application Express was released. It is the natural successor of the first edition (duh), but its not just that. Some of chapters are updated for APEX 5, but about half of the chapters are completely new! And that means also half of the authors are new as well. Because this is a joint project, 14 authors, 14 chapters, one goal. And that goal is to raise as much money as we can for the funds that support the relatives of two of the greatest Oracle APEX Development Team members who passed away a few years ago: Carl Backstrom and Scott Spadafore.
So check it out on and get your copy today. If you buy one APEX book ... it should be this one. It's not only beneficial to yourself, but also for others !

Tuesday, June 30, 2015

Make even more of UKOUG Tech15: APEX 5.0 UI Training - Dec 10th in Birmingham !

APEX 5.0 has been released this spring. People who have already spent some time on this new version know this version is packed with new features aimed to make APEX developers even more productive, like the Page Designer.
Another striking new subset of features is aimed at creating better looking user interfaces for your APEX applications in an easy and maintainable way.
 The definition of user interface components in APEX 5.0 is very different to what we're used to. For example there is a new Universal Theme with Template Options and a Theme Roller. To get you up and running with this new toolset as quickly as possible, Dimitri Gielis of APEX R&D and Roel Hartman of APEX Consulting have joined forces and set up a one day course fully aimed at APEX 5.0 UI. So if you want to know not only how to use the new Theme, but also how to modify it to fit your needs, this is the event you should attend!

The training will be at the Jury’s Inn in Birmingham (UK) on Thursday Dec 10 - so conveniently immediately after the UKOUG Tech15 conference. More information and registration see

If you are from another country and think this training should be available in your country as well, please contact us - then we'll see what we can do!

Thursday, May 14, 2015

APEX 5.0 UI Training - Looking back and towards the future

This week Dimitri Gielis and I ran a training day about all the new APEX 5.0 UI features. We had a full room with 20 enthusiastic attendees from 4 different countries. We covered some general APEX 5 UI features, the Universal Theme, Responsive Grid layouts, Template Options, Theme Roller, Theme Styles and bringing it all together using some advanced features. See the website for more details.
And - as it was the first time - we received some very valuable feedback in order to make a next run even better. Thanks to all attendees for that!

And for a next run we are thinking about different options:
1. On line (broken down in 3 parts)
2. Another one in The Netherlands or Belgium
3. Somewhere else in Europe (Germany? Scandinavia? UK?)
4. Somewhere else in the world. 

Please let us know what you think. Please leave a comment or drop an email with your thoughts. We would really appreciate that.