Tuesday, March 15, 2016

Me and the ODTUG Board

This afternoon my Twitter timeline exploded with retweets, likes and congratulations. It all started with this tweet:

So it's official now. As Sarah Zumbrum had to step down due to her (dream) move to Oracle, a position on the ODTUG board opened up. And I was asked whether I was still interested. And of course I was! So after an internal voting procedure, I received the good news last weekend - and had to keep quiet for a few days...which was quite hard!
Last November I wrote a blog post "I *almost* made it to the ODTUG Board of Directors...", but now the word between the asterisks can be omitted.
I am thrilled to serve on the board - even though it's initially only until the end of the year. I'll definitely run again for the board in October - so you'd better become a member a.s.a.p. to vote for me then (if you aren't already)!
Hope to see you all at Kscope16 or any other local event.
You can read the official statement on the ODTUG site.

Monday, February 22, 2016

Unexpected behaviour using SSO Authentication for APEX

A customer of mine switched to a Singe Sign On Authentication Scheme for their APEX application - using Oracle Access Manager, but that's irrelevant to this case.
In the application there's a function to create an - temporarily - "real" APEX user. That APEX user is used in another JavaScript application that uses ORDS and the built-in APEX authentication - thus requiring the existence of APEX users. That function ran flawlessly when the application was still on APEX Authentication. But after switching to SSO, it complained about "ADMIN" privileges .... although the usernames are identical: SSO and APEX Authentication used the same usernames.
So it must be something in the authorization / security realm, isn't it? Thus we tried a smart thing and moved that particular function to another - APEX Authenticated - application, while sharing the login credentials via a cookie. But to no avail. Some complaint.
So I contacted the APEX Development team and - as always - I got a quick reply. It wasn't a bug. It's a feature. They explained that you can only use functionality that needs APEX Admin privileges if you are authenticated against the APEX Repository. An - accidental (?) - match on usernames is not enough to grant ADMIN privileges. And that totally makes sense....
So although it was unexpected behaviour, it is the correct behaviour!

BTW. the solution is to create a separate job that runs using the right privileges.

Wednesday, February 17, 2016

Navigating through your APEX Calendar - the easy way

The renewed Calendar Region in APEX is awesome. With just one SQL statement you can provide your users with a calendar view of their data that looks very familiar to what they already know, use and love. Apart from one thing: Navigating to a specific date is cumbersome.
The shortest route is:

  • Navigate to the right month (can be any number of clicks) 
  • Switch to "Week view" 
  • Navigate to the right week (between 0 and 4 clicks) 
  • Switch to "Day view" 
  • Navigate to the right dat (between 0 and 7 clicks)

Way to many clicks!
So how hard can it be to make it easier - more user-friendly? We would like to navigate from both the Month view and the Week view directly into the Day view of a specific date. So with just one click once we have that day in sight.

Dynamic Actions to the rescue. Of course. What else...

First from the Month view to the Day view:
1. Create a Dynamic Action with the specs as shown to the left. Notice the the Event Scope should be "Dynamic" otherwise it wouldn't work after you navigate to another month. This DA will fire upon a click on the TD element that contains the day number in the Month view.
That TD element contains the actual date as a "data" attribute. So we can access it using
$(this.triggeringElement).data().date















2. The Action is a piece of JavaScript:
// Go to the date of the clicked day
$(this.triggeringElement).closest("div.fc").fullCalendar("gotoDate", $(this.triggeringElement).data().date);
// Switch the view to Day view
$(this.triggeringElement).closest("div.fc").fullCalendar("changeView", "agendaDay");
// Kill the Dialog if you have one
setTimeout( function(){ apex.navigation.dialog.cancel(true); }, 1);
That last line is only necessary when you defined a "Create Link" on your Calendar region. And there are probably better, more elegant, ways of solving that.

Second from the Week view to the Day view:
1. Again, create a Dynamic Action. But now use ".fc-agendaWeek-view th.fc-day-header" as the selector.
In the Week view, the date itself is not contained in the header, but in the TD of another table below the headers. So we have to traverse the DOM a bit to get that clicked date.

2. The JavaScript code is:
var clicked = $(this.triggeringElement);
// find the index
var i = clicked.index();
// find the corresponding day and date
var day = $(clicked).closest("div.fc-agendaWeek-view").find("div.fc-bg td.ui-widget-content").eq(i).data().date;

clicked.closest("div.fc").fullCalendar("gotoDate", day );
clicked.closest("div.fc").fullCalendar("changeView", "agendaDay");
setTimeout( function(){ apex.navigation.dialog.cancel(true); }, 1);

The result is elegant and very intuitive:
Try it yourself on : apex.oracle.com

Thanks to Marc Sewtz for helping me out finding the "fullCalendar" methods.


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: 
class="editlink"

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');
$('#'+header).hide();

// 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(){ 
    $(".highlight").removeClass("highlight");
    $(this).addClass("highlight");
    $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
$('.rowlink').closest('tr').addClass('clickable');

if ( !( $v("P0_CURRENT_RECORD") ) ) {
  // Issue a dummmy click on the first row
  $(".rowlink:first").closest("tr").click();
}
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 :

#successMessage{
  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 = this.data.successMessage.text;
if (lMessage ){
  $('#successMessage_heading', top.document).html(lMessage);
  $('#successMessage', top.document).fadeIn(300);  
}
$("#successMessage").delay(3000).fadeOut(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.