APEX Developer Competition 2009 - Top 12

Yesterday I found out that the application I submitted for the Oracle Application Express Developer Competition 2009 finished in the top 12! You can view all the winners here: http://www.oracle.com/technology/products/database/application_express/html/comp_winners.html

You can read about my application here: http://apex-smb.blogspot.com/2009/09/apex-rules-guidelines.html. There's a link where you can download a copy of it as well.

I put together my application rather quickly in a weekend so it does have some bugs. I'll try to update it soon for those that are using / interested in using it in their organization.

Thank you to all the judges and congrats to everyone that participated!

Enhanced APEX Session Timeouts

APEX has built in logic to set the lifetime of a session. To configure this option go to Shared Components / Edit Security Attributes / Maximum Session Idle Time in Seconds and set the time in seconds

This essentially terminates the user's session in the database and the next time they submit the page they'll be redirected to the login screen. The user will only know that they are logged out once they submit the page. If you have an Interactive Report (IR), or use Partial Page Refresh (PPR) the users won't know they're logged out. Instead it will look as though the report is still trying to load.


Another situation that may happen is that the user is filling out a long form on your page, their session timesout, then they click "submit". They'll be redirected to the login page and they'll lose all the information that they entered.

What if a user wants to extend their session? i.e. they haven't done anything to the page but would like a warning message before we automatically log them out? Or they are entering a log form and don't want to be logged out? I got this idea from the Air Canada web site when I was booking tickets. I really liked the fact that they let me know that they were to end my session, and gave me the option to extend my session.


The following solution will allow you to use APEX's session timeout and resolve the issues listed above. You can view the demo here: http://apex.oracle.com/pls/otn/f?p=20195:2600

Please note that since the demo page is set to public you can refresh after the session is supposed to have timedout and it will still work. If you set the Idle Session in APEX, this will work for pages that require authentication



Here's a high level overview of what this solution does:

• Start a timer (pingApexSession) that will constantly "ping" (therefore refresh) your APEX session every X seconds.


• Start a timer (idleTimer) to detect movement on the page.


• If the idleTimer times out, give the user the option to extend session


• If the user does not extend their session, terminate their session

I haven't put this code into a production application yet. As I mention below, I plan to make a jQuery plugin for this, so if you please send me any feedback that would be useful fur the plugin.

This solution uses jQuery and the following plugins:

• Simple Modal


• jQuery idleTimer


• jApex


• jQuery Countdown

- Create Application Process: AP_NULL
- Process Point: On Demand
- Name: AP_NULL
- Type: PL/SQL Anonymous Block
- Process Text: NULL;

- Create Application Process: AP_LOGOUT
- Process Point: On Demand
- Name: AP_LOGOUT
- Type: PL/SQL Anonymous Block
- Process Text:

BEGIN
  apex_custom_auth.LOGOUT (p_this_app                   => :app_id,
                           p_next_app_page_sess         => :app_id || ':1');
END;

- Create Region: "Extend Session" on Page 0
- Title: Extend Session
- Type: HTML Text
- Static ID: P0_REG_EXTEND_SESSION
- Region Attributes: style="display:none"
- Region Source: Your session will timeout in: <span id="timeoutCountdownDisplay" style="font-weight:bold"></span>
You can put whatever message you want. Just make sure the span tags exist for the countdown timer

- Create Button: "Extend Session" on Page 0
- Button Name: EXTEND_SESSION
- Text Label: Extend Session
- Display in Region: Extend Session
- Target is a: URL
- URL Target: javascript:gTimeout.timers.killSession.liveFn();

- Create Region: "Session Timedout" on Page 0
- Title: Session Ended
- Type: HTML Text
- Static ID: P0_REG_SESSION_ENDED
- Region Attributes: style="display:none"
- Region Source: Your session has ended. Please login.

- Create Button: "Login" on Page 0
- Button Name: LOGIN
- Text Label: Login
- Display in Region: Session Ended
- Target is a: Page in this Application
- Page: 1

- Create Region: "JavaScript - Session Timeout" on Page 0
- Title: JavaScript - Session Timeout
- Type: HTML Text
- Template: No Template
- Region Source:

You'll need to upload the JS files beforehand. Please see the list above to obtain the files

Here's the script to put into the region above. I separated them for display purposes.

I probably should have created this as a jQuery plugin. I may convert it later on

var gTimeout = {
  //debug
  debug: false, //Set to True to turn on debugging
  debugFn: function(pMsg) {
    if (gTimeout.debug){
      console.log(pMsg); 
    }
  }, //debug
  modalRegions: {
    //Region that contains the "Extend Session" information
    extendSession: {
      id: 'P0_REG_EXTEND_SESSION',
      backgroundColor: '#CCC',
      opacity: 70,
      openFn: function() {
        gTimeout.debugFn('gTimeout.modalRegions.extendSession.openFn');
        // Start display timeout counter
        $('#timeoutCountdownDisplay').countdown('destroy');
        $('#timeoutCountdownDisplay').countdown({
          until: '+' + (gTimeout.timers.killSession.time / 1000),
          compact: true, 
          format: 'M:S'
        });
        // Load modal box to give user option to extend session
        $('#' + gTimeout.modalRegions.extendSession.id).modal({
          overlayCss: {backgroundColor: this.backgroundColor},
          opacity: this.opacity
        });
        return;
      }, //openFn
      closeFn: function(){
        gTimeout.debugFn('gTimeout.modalRegions.extendSession.closeFn');
        $.modal.close();
        return;
      }//closeFn
    },
    //Region that will be displayed if the user does not extend thier session
    sessionEnded: {
      id: 'P0_REG_SESSION_ENDED',
      backgroundColor: 'black',
      opacity: 70,
      openFn: function() {
        gTimeout.debugFn('gTimeout.modalRegions.sessionEnded.openFn');
        // Close Extend Sessios modal window
        gTimeout.modalRegions.extendSession.closeFn();
        // Open Logout modal window
        $('#' + gTimeout.modalRegions.sessionEnded.id).modal({
          overlayCss: {backgroundColor: this.backgroundColor},
          opacity: this.opacity
        });
        return;
      }// openFn
    }//sessionEnded
  },//modalRegions
  timers: {
    //Ping APEX Session timer will update the database session timer 
    pingApexSession: {
      id: -1,
      time: 5000, //Time to keep database session alive. This should be really close to the APEX idle time
      loadFn: function(){
        gTimeout.debugFn('gTimeout.timers.pingApexSession.loadFn:');
        this.id = setTimeout('gTimeout.timers.pingApexSession.fn();', this.time);
        return;
      },
      unloadFn: function(){
        gTimeout.debugFn('gTimeout.timers.pingApexSession.unloadFn:');
        clearTimeout(this.id); 
        this.id = -1;
        return;
      },//unloadFn
      fn: function(){
        gTimeout.debugFn('gTimeout.timers.pingApexSession.fn: Extending APEX Session');
        jQuery.jApex.ajax({
          appProcess: 'AP_NULL',
          success: function(){},
          async: true
        });
        gTimeout.timers.pingApexSession.loadFn();
        return;
      }//fn
    },//pingApexSessions
    //Kill current session. This is called when the user gets the option to extend their session
    killSession: {
      id: -1,
      time: 5000, // Time to kill the APEX session once launched. Should only be run when extend session popup box is loaded
      loadFn: function(){
        gTimeout.debugFn('gTimeout.timers.killSession.loadFn:');
        this.id = setTimeout('gTimeout.timers.killSession.killFn();', this.time);
        return;
      },
      unloadFn: function(){
        gTimeout.debugFn('gTimeout.timers.killSession.unloadFn: ');
        clearTimeout(this.id); 
        this.id = -1;
        gTimeout.modalRegions.extendSession.closeFn(); // Close extendSession Modal 
        return;
      },
      killFn: function(){
        gTimeout.debugFn('gTimeout.timers.killSession.killFn: Killing APEX Session');
        // Open Logout modal window
        gTimeout.modalRegions.sessionEnded.openFn();
        // Stop ping Apex session
        gTimeout.timers.pingApexSession.unloadFn(); 
        // Logout APEX session
        jQuery.jApex.ajax({
          appProcess: 'AP_LOGOUT',
          success: function(){},
          async: true
        });
        return;
      },
      // Prevents the session from being killed. We should be in a about to kill state now
      liveFn: function(){
        gTimeout.debugFn('gTimeout.timers.killSession.liveFn: ');
        //Check that we're about to be killed
        if (this.id == -1){
          alert('Session is not marked to be killed');
          return;
        }
        
        // Stop the kill timer
        this.unloadFn();
        return;
      }//liveFn
    },//killSession
    // Timer for user movement time
    idle:{
      time: 5000, // Time to load the "Extend Session" popup box
      loadFn: function(){
        gTimeout.debugFn('gTimeout.timers.idle.loadFn:');
        $.idleTimer(this.time);
        $(document).bind("idle.idleTimer", function(){gTimeout.timers.idle.idleFn();});
        // Trigger countdown timer
        return;
      },
      idleFn: function(){
        gTimeout.debugFn('gTimeout.timers.idle.idleFn:');
        // Load modal box to give user option to extend session
        gTimeout.modalRegions.extendSession.openFn();
        // Only load if we're not in a kill state
        if (gTimeout.timers.killSession.id == -1){
          gTimeout.timers.killSession.loadFn();
        }
        return;
      } //idle Fn     
    } // idle
  },//timers
  loadFn: function() {
    gTimeout.timers.pingApexSession.loadFn(); // Keep database sessions alive
    gTimeout.timers.idle.loadFn(); // Turn on user idle timer
    return;
  }//loadFn
  
};//gTimeout

$(document).ready(function(){
  // Set Parameters
  gTimeout.timers.pingApexSession.time = 5 * 1000; // Refresh APEX session every 5 seconds. This should be really close to your apex session timeout values
  gTimeout.timers.idle.time = 10 * 1000; // 10 seconds of inactivity will trigger this window
  gTimeout.timers.killSession.time = 10 * 1000; // Once the warning message pops up, user has 10 seconds to extend their session
  // Configure Modal windows (not required)
  gTimeout.modalRegions.extendSession.backgroundColor = '#CCC';
  
  gTimeout.loadFn();
});

Redirects for APEX Tabs instead of page submits

With APEX Tabs you may not want the page to be submitted each time the user clicks on a tab. If you look at the link for the tabs they look like: javascript:doSubmit('xxxx'); Where "xxx" is the name of the tab. "doSubmit" will submit the page and could trigger page computations, validations, and processes. If all you want to do is use the tabs as a form of navigation (i.e. when you click on a tab it redirects to another page) then this could cause some problems.

To avoid triggering Page Processing on tabs you can modify all the conditions on your Page Process (this could be very long) or change the link. They're multiple ways to change the links, here's a simple one using jQuery.

Here's a link to the demo: http://apex.oracle.com/pls/otn/f?p=20195:2500. Note, in the demo I don't trigger the javascript to change the link automatically so you can see what they look like before and after changing the links.

- Create an Application Process
Note: You may want to only apply this to certain pages depending on the use case

Name: AP_UNSUBMIT_TABS
Process Point: On Load: After Header

BEGIN
  FOR x IN (SELECT tab_name,
                   tab_page,
                      '' tabinfo
              FROM apex_application_tabs t
             WHERE application_id = :app_id)
  LOOP
    HTP.p (x.tabinfo);
  END LOOP;
END;

- Create a HTML region
Note: You'll need to install the jQuery JS file in Shared Components / Static Files

Tooltip Help in APEX: An alternative to popup help

The default help functionality for APEX is ok but can cause some problems with users browsers since it uses popup windows to display the help. If you do some digging around regarding web development best practices you'll find a lot of articles discussing why you should avoid popup windows. Instead of using the default help popup windows I prefer to use tooltips to display the help. Besides avoiding the popup window, tooltips generate a good user experience by displaying item help very quickly.

This demo uses a jQuery tooltip plugin: http://bassistance.de/jquery-plugins/jquery-plugin-tooltip/. Please visit the Bassistance website to find out how to configure the look and feel of the tool tips.

Here's a link to the demo: http://apex.oracle.com/pls/otn/f?p=20195:2400



- Create or Update Label Template
Note: You don't need to create a new template. If you want to, just update the existing templates
- Copy the "Optional Label with Help" and rename to "Optional Label with ToolTip".
Note: You can do this for required labels as well

- Change the "Before Label" From:
<label for="#CURRENT_ITEM_NAME#" tabindex="999"><a class="t20OptionalLabelwithHelp" href="javascript:popupFieldHelp('#CURRENT_ITEM_ID#','&SESSION.')" tabindex="999">

To:
I removed the href reference and replaced with #
<label for="#CURRENT_ITEM_NAME#" tabindex="999"><a class="t20OptionalLabelwithHelp" href="#" tabindex="999">

- Create a HTML region
This can be done on P0 to load for each page
Don't forget to upload the jQuery and tooltip JS files into Shared Components / Static Files

- Create an Application Process
Name: AP_GET_HELP_TEXT
Process Point: On Load: After Header

BEGIN
  FOR x IN (SELECT    ''
                   || item_help_text
                   || '' help_html
              FROM apex_application_page_items
             WHERE application_id = :app_id
               AND page_id = :app_page_id
               AND item_help_text IS NOT NULL)
  LOOP
    HTP.p (x.help_html);
  END LOOP;
END;

- Change Item Labels
Change Item labels to "Optional Label with ToolTip". Only do this if you created a new template

Updateable Interactive Report for APEX

A colleague had a requirement where he had over 10,000 rows of data which had to be updateable. Using Interactive Reports (IR) was the preferred approach as it would allow users to filter the data to modify the rows they wanted to before submitting the page. Tabular forms wouldn't work since the page would be to large. This is the solution that I proposed to make "Updateable IRs".

The following solution will work with IRs and standard reports with pagination. If the users applies filters or paginates to another set of data, the changes they make will remain in the collection. This is using a similar technique that I wrote about for APEX Report with checkboxes (advanced). To summarize this process:

• Store the current query data into a collection as well as the md5 checksum.
• Build an IR against the collection and use APEX_ITEMs to display input fields.
• When a user changes a field, we submit that change to the collection.
• Once the user is done with their changes you'll need to process the collection as required. In the last step in this example I have a query that will help identify changed rows.

You can do a lot with this approach but if you don't have an urgent need I'd suggest holding off until APEX 4.0. They're some security issues that would need to be addressed before launching this code in a public application. I didn't include the security updates in this example since I did not want to lose scope of the base functionality. Updating the code to make it secure shouldn't be too difficult.

Here's the link to the demo: http://apex.oracle.com/pls/otn/f?p=20195:2300



- Create IR Report Region
Note: You can use this for regular reports with pagination as well

SELECT e.empno,
    apex_item.text (2,
                    ac.c002,
                    NULL,
                    NULL,
                       'class="updateableIR" seqid="'
                    || ac.seq_id
                    || '" attrNum="2"'
                   ) ename,
    apex_item.text (3,
                    ac.c003,
                    NULL,
                    NULL,
                       'class="updateableIR" seqid="'
                    || ac.seq_id
                    || '" attrNum="3"'
                   ) job,
    apex_item.select_list_from_query
       (4,
        ac.c004,
           'select E.ENAME d, E.EMPNO r from emp e where E.EMPNO != '
        || e.empno,
        'class="updateableIR" seqid="' || ac.seq_id || '" attrNum="4"',
        'YES',
        NULL,
        '- Manager -'
       ) mgr,
    apex_item.text (5,
                    ac.c005,
                    NULL,
                    NULL,
                       'class="updateableIR" seqid="'
                    || ac.seq_id
                    || '" attrNum="5"'
                   ) hiredate,
    apex_item.text (6,
                    ac.c006,
                    NULL,
                    NULL,
                       'class="updateableIR" seqid="'
                    || ac.seq_id
                    || '" attrNum="6"'
                   ) comm,
    apex_item.select_list_from_query
                       (7,
                        ac.c007,
                        'SELECT d.dname d, d.deptno r FROM dept d',
                           'class="updateableIR" seqid="'
                        || ac.seq_id
                        || '" attrNum="7"',
                        'YES',
                        NULL,
                        '- Department -'
                       ) deptno
FROM apex_collections ac, emp e
WHERE ac.collection_name = :p2300_collection_name AND ac.c001 = e.empno

- Create Region Items
- Hidden & Protected: P2300_COLLECTION_NAME

- Create Page Computation
Note: This is just to set the collection name. You can call it whatever you want

Item: P2300_COLLECTION_NAME
Computation Point: Before Header
Computation Type: Static Assignment
Computation: P2300_IR_COLLECTION

- Create a Page Process (PL/SQL)

Name: Load Collection
Process Point: On Lead - Before Header

-- This creates the collection if it isn't created yet.
DECLARE
v_collection_name             apex_collections.collection_name%TYPE
                                                 := :p2300_collection_name;
v_reset_flag                  VARCHAR2 (1) := 'N';
BEGIN
-- Create collection if it does not exist or reset collection required
IF    apex_collection.collection_exists
                                   (p_collection_name            => v_collection_name) =
                                                                     FALSE
  OR v_reset_flag = 'Y'
THEN
 apex_collection.create_collection_from_query
   (p_collection_name            => :p2300_collection_name,
    p_query                      => q'! SELECT empno,
    ename,
    job,
    mgr,
    TO_DATE (hiredate, 'DD-MON-YYYY'),
    comm,
    deptno
FROM emp !',
    p_generate_md5               => 'YES'
   );        -- Generated md5 is important to help identify changed columns
END IF;
END;

- Create HTML Region to store JS code:
Note: This uses jQuery and Tyler Muth's jApex plugin. You'll have to upload the .js files as static files in Shared Components.

- Create Application Process:
Name: AP_UPDATE_COLLECTION_CELL
Process Point: On Demand

-- AP_UPDATE_COLLECTION_CELL
BEGIN
apex_collection.update_member_attribute
                             (p_collection_name            => apex_application.g_x01,
                              p_seq                        => apex_application.g_x02,
                              p_attr_number                => apex_application.g_x03,
                              p_attr_value                 => apex_application.g_x04
                             );
END;

- Query to see which rows were changed

SELECT ac.collection_name, ac.seq_id, ac.c001, ac.c002, ac.c003, ac.c004,
    ac.c005, ac.c006, ac.c007, ac.md5_original,
    CASE
      WHEN apex_collection.get_member_md5
                                       (:p2300_collection_name,
                                        ac.seq_id) =
                                                ac.md5_original
        THEN 'NO'
      ELSE 'YES'
    END row_change
FROM apex_collections ac
WHERE collection_name = :p2300_collection_name

APEX Rules & Guidelines

After several late nights of nerding I've finally submitted/completed my APEX application for the APEX Developer Competition 2009.

You can demo and download the application here: http://apex.oracle.com/pls/otn/f?p=46865
Note: Demo is no longer up and will not be supported.

This purpose of this application is to allow development teams to create an online Rules & Guidelines development document. The template for this application is based on Patrick Cimolini's (Cayman Islands Government) presentation at ODTUG.

The concept of the R&G template is:

Rule: Follow 100% of the time
Guideline: Follow 90% of the time

Mandatory Headings:

Rule/Guideline: Active voice sentence.
Why:Important for Team Buy-In

Optional Headings:

Result: Illustration of desired outcome
How: How the result is achieved.
Notes: State the non-obvious
See Also: Point to related Rules and Guidelines

You can use this template for both APEX development and your PL/SQL standards. I've included the installation files in the demo application so feel free to install at your organization. If you are using it please post any comments/suggestions on this post and I'll try to write some updates.