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:

1 2 3 4 5 6

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:

1 2 3 4 5 6 7 8 9 10 11 12

<script src="#APP_IMAGES#jquery-1.3.2.min.js" type="text/javascript"></script> <script src="#APP_IMAGES#jquery.simplemodal-1.3.min.js" type="text/javascript"></script> <script src="#APP_IMAGES#idle-timer-0.7.080609.js" type="text/javascript"></script> <script src="#APP_IMAGES#jquery.jApex.0.9.2.js" type="text/javascript"></script> <script src="#APP_IMAGES#jquery.countdown-1.5.3.js" type="text/javascript"></script>   <script type="text/javascript">   //*** Insert script from below ***/   //Removed for display purposes </script>

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

BEGIN   FOR x IN (SELECT tab_name,                    tab_page,                       '<span class="apexTabURLs" tabname="'                    || tab_name                    || '" tabnewurl="'                    || apex_util.prepare_url (   'f?p='                                              || :app_id                                              || ':'                                              || tab_page                                              || ':'                                              || :app_session                                              || ':'                                              || tab_name                                              || '::'                                              || tab_page                                              || '::')                    || '"></span>' 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

1 2 3 4 5 6 7 8 9 10 11 12

<script src="#APP_IMAGES#jquery-1.3.2.min.js" type="text/javascript"></script> <script type="text/javascript">   $('span.apexTabURLs').each(function(i){     var vTabName = $(this).attr('tabname');     var vNewUrl = $(this).attr('tabnewurl');     $('#t20Tabs a[href="javascript:doSubmit(\'' + vTabName + '\');"]').attr('href',vNewUrl);   });   // Remove the extra span tags   $('span.apexTabURLs').remove(); </script>

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

<script src="#APP_IMAGES#jquery-1.3.2.min.js" type="text/javascript"></script> <script src="#APP_IMAGES#jquery.tooltip.pack-1.3.js" type="text/javascript"></script>   <script type="text/javascript">   $(document).ready(function(){   $('span.itemToolTip').each(function(i){     $('label[for="' +  $(this).attr('foritem') + '"]').attr('title',$(this).html()).tooltip({         track: true,         delay: 0,         showURL: false,         showBody: " - ",         fade: 250       });   });   // Remove Original ToolTip Help Text   $('span.itemToolTip').remove(); }); </script>   <style> #tooltip {  position: absolute;  z-index: 3000;  border: 1px solid #111;  background-color: #eee;  padding: 5px;  opacity: 0.85; } #tooltip h3, #tooltip div { margin: 0; } </style>

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

BEGIN   FOR x IN (SELECT    '<span class="itemToolTip" foritem="'                    || item_name                    || '">'                    || item_help_text                    || '</span>' 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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

-- 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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52

<script src="#APP_IMAGES#jquery-1.3.2.min.js" type="text/javascript"></script> <script src="#APP_IMAGES#jquery.jApex.0.9.2.js" type="text/javascript"></script>   <script type="text/javascript"> /**  * Updates a cell in a collection  * @author Martin Giffy D'Souza. http://apex-smb.blogspot.com  * @param pThis input item that was changed  *  Must contain the following attributes:  *  - seqid Seq ID from collection  *  - attrnum Attribute number (column number)  * @param pOptions Options  *  - appProcess: Application process to call  *  - collectionName: Collection to update  *  - successFn: Function to execute once completed  */ function updateCollectionCell(pThis, pOptions){   var vDefaults = {     appProcess: 'AP_UPDATE_COLLECTION_CELL',     collectionName: '',     successFn: function(){}   };   pOptions = jQuery.extend({}, vDefaults, pOptions);        // Check that collection name is present   if (pOptions.collectionName == ''){     alert('Missing Collection Name');     return;   }       jQuery.jApex.ajax({     appProcess: pOptions.appProcess,     success: pOptions.successFn,     x01: pOptions.collectionName,     x02: $(pThis).attr('seqid'), //Seq ID     x03: $(pThis).attr('attrnum'), //Attribute Number (i.e. column number)     x04: $(pThis).val() // New Value   });       return; }// updateCollectionCell   //Set all updateableIR columns onChange events $('.updateableIR').live('change',function(){   updateCollectionCell(this, {     collectionName: $v('P2300_COLLECTION_NAME'),     successFn: function(){}     }); }); </script>

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

1 2 3 4 5 6 7 8 9 10 11

-- 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

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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.