Friday, July 16, 2010

Quick Notes

Just a bit of housekeeping.

APEX 4.0 Builder Hotkeys
This is just a quick note on the APEX 4.0 Builder Hotkeys. I have updated the script because not all previous/next buttons were included in the last version. Alt + PageUp/PageDown should now give the right response.

You can download and install the script here:

Installation instructions can be found here:

Ye Olde Sample App still working! I just went through the Application Upgrade and changed the theme while at it. I even updated the Google Maps example. ("Updated" sound so much better than "fixed a bug". Quick solutions will make you do that...) This application will probably not be updated anytime soon. There are a few more elaborate examples I had in mind, but then there is this "time"-thing all over again. Exploring APEX 4.0 is at the top of my spare time roster for now.

The application will be left alone for the time being, and can be found here:

Summer Vacation
Finally, my last day of work before I start my summer vacation. One of the nicer benefits as a worker in Norway, is the length of paid vacation established by national law. I guess this is not so normal for Americans.

True story from the ODTUG Kaleidoscope this year. An American guy comes up to me with his friend, and after introductions the following conversation takes place:
Him: So, you're from Norway?
Me: Yep!
Him: Could you tell my colleague here how much paid vacation you guys have?
Me: Five weeks!
Friend: ... (going through the whole stunned jaw dropping improvised look)

Cheers :-)

Thursday, July 15, 2010

Modal Pop Up with Dynamic Actions

You have all seen it in the more "modern" web applications, pop-up windows displayed inline, and not in a separate window. With JQuery embedded into Oracle APEX 4.0, and JQuery UI equally at your disposal (at least in the new themes I have tested), creating this type of dialogs is well withing reach. The JQuery UI dialog is already in use in the application templates (the tool tip dialogs).

I wanted to use Dynamic Actions to create a modal pop-up dialog of APEX-pages, and this is what I came up with. It is not as streamlined as I would have liked it to be, but a big leap in the right direction nonetheless.

Demo Application
I have created a demo application which you can test here: You can also download the application here:

Identifying Triggering Elements
The trick with the Dynamic Actions is again to identify which elements you would like to use to trigger the dialog. In my example I have a plain SQL-report, but I have edited the primary key column to display an edit icon, and under the Column Link properties, I have set Link Attributes to include id="callModalDialog#EMPNO#". This gives me the possibility to identify the links using a JQuery selector expression.

I created a Dynamic Action on the click event, with Selection Type JQuery Selector with value a[id^=callModalDialog]. This will identfy all items with attribute id starting with callModalDialog, which, incidentally, is the same id I used on my edit links in the report.

To ensure that the event triggers after refresh of the report, I set the Event Scope to live under Advanced (as opposed to bind, which is normal). This saves me the effort of re-binding the click-events when the report is refreshed.

Calling the Dialog
When the user clicks the edit links, I want to do two things: suppress the default browser behavior, and call the modal dialog. I achieve this by adding a true Action of type Execute Javascript Code.

This contains the javascript code to do both the things I want to do:
/* prevent default behavior on click */
var e = this.browserEvent;
/* Trigger JQuery UI dialog */
var horizontalPadding = 30;
var verticalPadding = 30;
$('<iframe id="modalDialog" src="' + this.triggeringElement.href + '" />').dialog({
   title: "Edit Employee",
   autoOpen: true,
   width: 700,
   height: 300,
   modal: true,
   close: function(event, ui) {apex.event.trigger('#P2_AFTER_MODAL','select',''); $(this).remove();},
   overlay: {
       opacity: 0.5,
       background: "black"}
}).width(700 - horizontalPadding).height(300 - verticalPadding);
return false;            
Some explanation for this is in order. The first part is just to prevent the default browser behavior (user click a ink, and the browser naturally wants to open that page). The second part is by far more complex, and contains all the code needed to start the JQuery UI dialog.

The edit link points to an APEX application page, and the code above invokes the page in an IFRAME, and uses the JQuery UI dialog to show the IFRAME inline in a modal dialog. I extract the link from the triggeringElement, set the title (this could also have been extracted from the triggeringElement), set various attributes for the dialog and IFRAME. In short the code above says open the HREF of the triggeringElement in an IFRAME, show the IFRAME in a modal dialog and open the dialog immediately.

Returning from the Dialog
If you notice the code above, the close configuration for the dialog looks quite complex. The reason for this is that I want to execute a Dynamic Action when the dialog is closed. There are two problems with this; (as far as I know) I cannot execute a Dynamic Action directly from Javascript, and I have nowhere to attach the Dynamic Event anyways. Write som javascript function you say? Yes I could do that, but the Dynamic Action is too good to pass up. Let us say that I want to refresh a report, set some item values and execute some PL/SQL when returning, with Dynamic Actions that is a walk in the park.

This is where I cheat :-)

The dialog does not exist when the page renders, so I have nowhere to attach my Dynamic Action to execute when returning from the dialog. My (kludgy) solution to this was to create a hidden item in the report region, and attach the Dynamic Action to the item. This way I can see all that happens in the Builder (easier to maintain), and if I give it sensible names, I can even discern what it does. In my case I created an item called P2_AFTER_MODAL, and attached a Dynamic Action called Refresh Report.

So, now I have defined what to do after returning, but how to trigger the event? And I cheat again... I attach the Dynamic Event to P2_AFTER_MODAL with the select event. The select event on a hidden item does not get triggered very easily. In effect this gives me a way to control that the Dynamic Action only executes when I programmatically tells it to. That is exactly what I tell it to when closing the dialog, using the APEX javascript API apex.event.trigger.

I also destroy the dialog after use, so I can create a new on the fly when the user wants to edit another employee, $(this).remove(); takes care of that (removing the IFRAME in effect, I could not get the dialog.destroy-method to work properly).

Closing the Dialog
When creating an APEX page that is to be a pop-up window, you need to take special care on the branching. In this case, you want to take care to close the dialog, both on cancel and after processing.

To handle cancel, let the cancel button redirect to URL, and set the URL target to: javascript:parent.$('#modalDialog').dialog('close');.

Notice the use of parent, the dialog is spawned by the original web page, and the dialog and its method belongs to the parent.

When branching after processing you have to be a bit more creative, create an unconditional branch to PL/SQL Procedure that runs the following code:
--closes this popup window
htp.p('<script type="text/javascript">');
This is in effect the same as the URL target behind the cancel button, but rendered by htp.p in PL/SQL.

Layout of the Dialog Page
In the sample application have have copied the Printer Friendly template, called it Modal Dialog, and used that as a page template for the dialog page. The modifications is basically just removing the navbar, and overriding the default "min-height" and "min-width" css attributes by explicitly setting style-attributes for the body-tag and div with id="body". This will vary, depending on the theme you are using. The goal is to strip the dialog of all unnecessary layout clutter.

Some Notes
I have created a few modal dialogs in the previous versions of APEX, and the javascripting involved is a pain. Maintaining the code even more painful. Even with the simple trick (or cheat, if you will) of attaching the Dynamic Action to an item in a declarative way, helps me getting control over the classic "what happens where, when and why".

Even though the basics is demonstrated in the sample application, there is a bit of work left. When you return from the dialog and refresh the report, you might want to take care to maintain the pagination, and if the user closes the dialog with cancel or window close, you might not want to refresh the report, etc., etc.

Enjoy :-)

Friday, July 9, 2010

APEX 4.0 Builder Hotkeys

Until recently I only developed in Oracle APEX using Mozilla Firefox. Partly because of the add-ons (like Firebug), but most of all because of Patrick Wolf's Oracle APEX Builder Plugin. It can be really annoying at times to scroll and click to apply changes.

After upgrading to APEX 4.0, the APEX Builder Plugin stopped working. I tried a quick and dirty approach, and updated the config files to look for button-elements instead of input-elements, but that did not work.

Enter Google Chrome
I've been using this browser on and off for some time, and it is incredibly fast compared to Firefox (even without add-ons, I checked). I did a bit of research (sounds better than a quick google search :-)), and found that Chrome supports Greasemonkey scripts natively. How nice! If only I had a Greasemonkey script that worked, that is...

So, I had to make my own. It's an absolute first try, so don't hold your breath. I have probably made any number of rookie mistakes, feel free to correct me :-)

Greasemonkey is a Mozilla Firefox add-on which can run scripts after page load, and do pretty much everything with it. Greasemonkey scripts are nothing but javascript with some "special" declarations at the beginning of the file, but runs in it's own sandbox.

Google Chrome understands and runs Greasemonkey scripts as add-ons (no install of Greasemonkey necessary). Chrome was my target after all.

APEX 4.0 Builder Hotkeys
This is nothing like the more sophisticated Builder Plugin from Patrick Wolf, I have only aimed at the dearly missed hotkeys (or shortcut keys if you will).

Hotkeys implemented so far:
  • F8 - Run page
  • F9 - Apply Changes
  • Alt + F9 - Click the orange button
  • Alt + PageUp - Click button named "<" or "< Previous"
  • Alt + PageDown - Click button named ">" or "Next >"

Installation instructions for both Chrome and Firefox:
  • Point browser to
  • Click green Install-button to the right
  • Click Install in the dialog (In Chrome: Click Continue at the bottom of the page, and then Install in the dialog)

Firefox users have to install Greasemonkey before installing the script above. I have hosted the script at Have a look around while you are there, the number of scripts (and the crazy things people do to web pages) is just staggering.

  • Point the browser to chrome://extensions/
  • Click Uninstall under the "Oracle APEX 4.0 Builder Hotkeys" extension.

  • Right click the little monkey in the lower right corner of the window
  • Choose Manage User Scripts
  • Click "Oracle APEX 4.0 Builder Hotkeys" on the left
  • Check "Also uninstall associated preferences"
  • Click Uninstall

Some Notes
I had to revert to JQuery 1.3.2 to make it play nice with Greasemonkey. I know there are ways make it work for 1.4.x, but I am not using JQuery to it's full potential (only simple selectors and click-events). I didn't even bother trying. I also had to include the whole script to make it work with Chrome (no external references).

There might be some problems with namespacing and JQuery conflicts, but I have encountered none so far.

Feel free to modify the scripts, or to make enhancement requests (hey, I might even do something about them. It could happen! Really!)

So, will I retire Firefox now? Nope, Firebug, YSlow, WebDeveloper, etc. are all good reasons not to. I am not that comfortable with the Chrome developer tool.

Wednesday, July 7, 2010

Dynamic Actions

One of the killer features of Apex 4.0 are Dynamic Actions. What a fantastic way to declaratively include JQuery and Ajax events into your application! I never really enjoyed placing javascript calls all over the place to achieve the rich functionality in the earlier versions of Apex. Too much work, and too hard to maintain.

Now you have Dynamic Actions, and all the little knobs and wheels show up in the page editor as any other kind of objects. The Apex-team has done a marvelous job of integrating JQuery into the framework.

How Dynamic are Dynamic Actions?
As I upgrade applications I want to get rid of all the little Javascript function calls that are all over, and replace them with Dynamic Actions. As I go along, there are a few challenges, but no show stoppers (yet). Below is a description on one of the hurdles (well, more like a small bump) passed along the way.

I find that Dynamic Actions are extremely versatile, but you should have some knowledge of JQuery selectors to realise it's full potential.

Invoking Dynamic Actions from Report Columns
In the editor there are no possibility of attaching dynamic actions to tabular forms columns. I wanted to invoke an Ajax-call to commit changes when the user changed a select list in a tabular form, here is a way you can do the same.

If you just want to inspect the code for yourself, I have created a small application you can download from

First off, create a tabular form, in this example I have a select that uses the APEX_ITEM-api to create application items:
select apex_item.hidden(1, emp.empno) empno
,      apex_item.display_and_save(2, emp.ename) ename
,      apex_item.select_list_from_query(3, emp.deptno, 'select dname d, deptno r from dept order by dname', p_item_id => 'empno'||empno) deptno       
from   emp
Next, create two hidden items, one for the primary key (P1_EMPNO), and one for the changed column value (P1_DEPTNO).
Next, create a Dynamic Action on the page level to invoke the update statement:

Right click Dynamic Actions on the page level, and choose Create

Choose Advanced

Give the Dynamic Action a name

Set the event to trigger on Change, set Selection Type to JQuery Selector and set JQuery Selector to "select[id^=empno]". This is where we set up the Dynamic Action to fire on every change to all items where the item ID starts with "empno". If you notice the select statement above, I explicitly set the item ID when creating the select lists.
Now I have set up when the event is supposed to trigger, next I define what is actually going to happen once the Action is triggered.

Set the Action to Execute PL/SQL Code, and the PL/SQL Code to the update statement below.
update emp
   set deptno = :P1_DEPTNO
 where empno = :P1_EMPNO;

In addition you must set Page Items to Submit with the request (P1_DEPTNO and P1_EMPNO):
Click create and you are almost done.

Now the Dynamic Action is defined in essence, but there is one piece missing: When the user changes the values of the select list, the new DEPTNO-value and the current EMPNO must be reflected in P1_DEPTNO and P1_EMPNO before the PL/SQL-Code (in this case the update statement) is invoked. This is where the pure genious of the Dynamic Actions come into play; Apex allows you to create additional Actions to execute in the same event.

To set the values of P1_DEPTNO and P1_EMPNO, do the following:

Open the editor for the Dynamic Action
Under True Actions region, click Add True Action

Set the sequence to something lower than the PL/SQL-action (normally this means setting it below 10)
Set the Action to Set Value
Make sure the Fire on Page Load is unchecked
Set Set Type to Javascript Expression
Set Javascript Expression to this.triggeringElement.value, which basically means get the value of the triggering element
Under Affected Elements, set Selection Type to Item(s) and Item(s) to P1_DEPTNO

Now we have to do the same for P1_EMPNO, this almost the same as for P1_DEPTNO, but we have to get the EMPNO-value from the ID of the triggering element. To achieve this, Click Add True Action, and set it up as seen below:

The Javascript code gets the ID of the triggering element, and strips off the leading empno-string. The remainder is the actual EMPNO of the employee affected.

When you return to the Dynamic Actions editor, this is what the True Actions should look like:

Run the page to check that it works.

A Word of Caution
This was a special and simplified case, normally you should use the native tabular forms functionality in Apex. There are several reasons for this. There is no MD5-checking here (data has been changed by another user), and every little change will result in a round trip to the server (no batch submit and no way for user to cancel changes).

Either way, Dynamic Actions rock!

Monday, July 5, 2010

ODTUG Kaleidoscope 2010

I attended the ODTUG Kaleidoscope this year. First off; this conference is truly a developers conference. The amount of knowledge shared (both within and outside sessions) is immense. The sheer number of profiles in Oracle development community speaks for itself. In short, if you are a developer in Oracle products, this is the conference for you.

The Goal
This year my primary focus was middle tier development, and ADF in particular. Being a consultant, you jump when customers says toad. While I remain a fan of APEX, it is part of my job to keep abreast with the alternatives.

The Oracle Application Development Framework (ADF) has been pushed by Oracle quite hard for a number of years. The market adoption has been a bit on the slow side (to say the least). That being said, I think it is one of the best frameworks out there based on Java to develop rich internet applications.

I think ADF sort of lives in a limbo; Traditional Java developers will not touch it (it's Oracle), and traditional (database) developers will not touch it (it's Java). It is not as simple as that, obviously, and it is being used out there, just not to the extent I expected.

I think Kaleidoscope reflects the limbo-effect, both in content and interest. Java developers obviously has other arenas, database developers are, well, database developers. I get that it is hard to combine the two, but look at the adoption of the conference by Essbase and Hyperion developers! They have really swarmed to the conference.

So, what did I get out of it? Related to ADF, not as much as I would have liked. There still seems to be considerably technical issues if you move out of the pure ADF sphere (like the SOA-suite). ADF on the other hand has matured considerably, and creating a slick looking, efficient application requires much less effort than it was in my last ADF project on 10g.

I still miss more to-the-point sessions on how to attack large implementations (like Andrejus Baranovskis).

The Meat
What really gave me value was the sessions by the big guns; Cary Millsap, Jonathan Lewis and Steven Feuerstein. Worth the trip alone to hear any of those speaking :-)