Mostly ORACLE APEX and Windsurfing stuff

Monday, 27 June 2016

Website Tester

Just found this really useful website for testing what people are seeing around the world:

Tuesday, 22 April 2014

Clear apex_item.popupkey_from_query

The apex_item.popupkey_from_query does not include a show null function that allows users to select a null
value.  The following add a clear function to the popup lov.

Javascrip Function:

function fClear(pThis) {
  pThis.prev('span').find('input').each(function() {

Column Formatting - HTML Expression:

<span style="white-space:nowrap;" >#COLUMN_NAME# 
  <span onClick="fClear($(this));">
    <img src="#IMAGE_PREFIX#delete.gif" alt="Clear" />


.clearLoc {cursor:pointer;}

Monday, 24 March 2014

Way to go Oracle

Just bought my self a new dev box, an Intel NUC with beautiful 29" LG ultra wide ;) and decided to install Windows after years of Mac and Linux.  I have to say awesome!  Installed Windows 8.1 64, Office 2013 Pro, Visual Studio, SQL Dev etc.  All going great until BI Publisher.  Run the latest 64 bit installer and 'error' - please use BI Publisher Desktop installer 32 bit (even though everything is 64 bit).  Tried the 32 bit install and 'error' - please use jre 1.6 or later (I have JDK 7 installed!).  So now starts the usual dance, uninstall JDK 7 and load an old JRE 1.6; same error.  Uninstall Office 2013 64 bit and load Office 2013 32 bit - Same error.  Try old versions of BI Pub desktop; lots of different errors, but still no cigar.  I've now spent 2 nights and messed up most of the good installations I'd completed by fricken around with various setting suggested on Metalink and forums.  I had the same problem last time I tried this 3 years ago and ended up using an XP vm running Word 2003 for BI Publisher.  Life is too short for this,  Jasper reports here I come! (and it's a whole lot cheaper).

Hooray, success this worked

Friday, 31 January 2014

Improve those Tabular Forms

Some of the new features in the "Out of the Box" tabular forms are great.  Here are a few tips to add
some spice to both the Out of the Box and Custom Tabular Forms.
  1. Add arrow key navigation.  This makes the tabular form work more like Excel and facilitates quicker data entry.  It's also a nice "accessibility" feature for users that navigate with the keyboard only.
  2. Remove the auto-complete feature added by most modern browsers.  This adds little value to a tabular form and screws up the arrow key navigation.
  3. If you are producing a Custom Tabular Form, then add the "Save Changes" before pagination feature.
  4. Add select all on focus.  This allows data to updated quicker and solely using the keyboard number pad.
To implement these feature add the following attributes to the input tag:

autocomplete="off" onkeyup="moveUpDown($(this), event)"

And then add the following JS functions:

// Save changes in tabular form before pagination

var p_changes = false;

$(document).ready(function () {
    $('.apexir_WORKSHEET_DATA,[id^="datatable"]').live('change', function () {
        p_changes = true;
        function (event) {
            if (p_changes) {
                if (confirm('"SAVE_CHANGES_MSG"')) {
                    p_changes = false;
                } else {

// will select all contents of a text field on focus

$(document).ready(function () {
    $("input[type=text]").focus(function () {
        // Select field contents;

// Arrow Key navigation

function moveUpDown(pThis, pEvent) {
    var keynum;
    var lName = pThis.attr("name");

    if (window.event) // IE
        keynum = pEvent.keyCode;
    } else if (pEvent.which) // Netscape/Firefox/Opera
        keynum = pEvent.which;

    if (keynum == 40) // Move down
        pThis.closest('tr').next().find('[name="' + lName + '"]').focus();
    if (keynum == 38) // Move up
        pThis.closest('tr').prev().find('[name="' + lName + '"]').focus();


Thursday, 8 November 2012

Disable Shuttle Item Values

I recently had a requirement to disable some values in a Shuttle Item .  As the values to be disabled differed for different users I populated a hidden item with the values and then used JQuery to loop through the Option tag and disable them.  This worked great except for the ‘move all’ and ‘remove all’ features, which took a bit of extra thought.  Here’s what I ended up with:

 Function and Global Variable Declaration

function shuttleMove(pMove1,pMove2) {
  $("#P60_SHUTTLE_"+pMove2+" option").each(function () {
    if (!$(this).attr('disabled')) {

Execute when Page Loads

var valDis = ':' + $v(P60_HIDDEN) + ':';
$("#P60_SHUTTLE option").each(function () {
  var valThis = ':' +$(this).val() + ':';
  if (valDis.indexOf(valThis) > -1) {

$('#P60_SHUTTLE_MOVE_ALL').replaceWith('<a onclick="shuttleMove(\'RIGHT\',\'LEFT\');"><img alt="Move All" src="/i/htmldb/icons/shuttle_last.png"></a>');

$('#P60_SHUTTLE_REMOVE_ALL').replaceWith('<a onclick="shuttleMove(\'LEFT\',\'RIGHT\');"><img alt="Move All" src="/i/htmldb/icons/shuttle_first.png"></a>')

Tuesday, 24 July 2012

Apex Item Select List Alternative

I've had a couple of projects now where there has been a requirement to produce a select list matrix to update results for people attending a course or event.  These have proved very popular with the users, but are a bit slow to run when there are large volumes of data involved, so I’ve been looking at a few different ways to speed things up.  The solution I’ve come with is a bit ‘off the wall’ but appears to do the trick nicely.  Let me know what you think!

This uses the standard APEX_ITEM.SELECT_LIST_FROM_LOV(1,null,'RESULT') to generate the Select Lists.  As you can see from the footer the region takes around 0.6 seconds for a dataset of 60 rows and 12 columns.  Scale this up a bit and it takes significantly longer.

This is a copy of the previous example, but instead of using the APEX_ITEM.SELECT_LIST_FROM_LOV I’m using the APEX_ITEM.TEXT function.  The region now takes < 0.1 seconds to generate, which is a significant improvement on performance.  So how do I convert the APEX_ITEM.TEXT into a Select List.  Well it’s “smoke and mirrors” and a bit of javascript/JQuery.  Here’s the code:

Function and Global Variable Declaration:

var gThis

function selectList(pThis) {
    gThis = pThis;
    var sel = $("#select");
    var pos = pThis.offset();
    var lTop = + 20;
    sel.attr('style', 'position:absolute;left:' + pos.left + 'px;top:' + lTop + 'px;border:2px groove;background:#ffffff;width:70px;');;

function selectClose(pVal) {

// function to close select list
// when the user clicks off the div
$(document).mouseup(function (e) {
    var container = $("#select");
    if (container.has( === 0) {

HTML Header:
<style type="text/css">

td.menuon {
  background-color: #000066;
  color: #FFFFFF;cursor:default;

td.menuoff {
  background-color: #FFFFFF;
  color: #000000;

#select {

.selectList {
  background-position:right center;


<div id="select">
<table border="0" margin="0">
  <tr><td onmouseover="className='menuon';" onmouseout="className='menuoff';" onClick="selectClose('Distinction');">Distinction</td></tr>
  <tr><td onmouseover="className='menuon';" onmouseout="className='menuoff';" onClick="selectClose('Merit');">Merit</td></tr>
  <tr><td onmouseover="className='menuon';" onmouseout="className='menuoff';" onClick="selectClose('Pass');">Pass</td></tr>
  <tr><td onmouseover="className='menuon';" onmouseout="className='menuoff';" onClick="selectClose('Fail');">Fail</td></tr>
  <tr><td onmouseover="className='menuon';" onmouseout="className='menuoff';" onClick="selectClose('Incomplete');">Incomplete</td></tr>
  <tr><td onmouseover="className='menuon';" onmouseout="className='menuoff';" width="70px" onClick="selectClose('');"> </td></tr>

Apex Item used in sql query:

APEX_ITEM.TEXT(1,null,12,12,'readonly class="selectList" onclick="selectList($(this));"')

Monday, 2 July 2012

The Perils of Tabbed Browsing

Most browsers since IE6 have the ability to have multiple tabbed windows running in the one browser session.  It’s a great feature and very useful when browsing the Internet, but can cause some confusing even insecure behaviour in Apex applications if not coded correctly.
The two situations that are Tab Browsing unfriendly are as follows:
  1.  Pages that branch back to themselves.
  2. Pages that reference session values from other pages.
Situation 1 occurs usually when you have a report and form and the form is set to branch back to itself once the Save or Create buttons are clicked.  To save re-querying the form record, you would think it quite ok submit the page and create a simple branch back to itself (e.g.  f?p=100:1).   Wrong! This is tabbed browsing unfriendly, as if a different record has be opened in another tab in the mean time, then the session values have changed and the page will refresh and display the session values from the other tab where the current tab values are null – very confusing.     Depending on how you’ve coded you application, this may become insecure as you can use tabbed browsing to manipulate session values and change the intended flow of the application.

Situation 2.  I’ve seen some developers do this all the time in their applications; I don’t except where I have a wizard process.  In a Wizard I’ll often share session values between the different pages to save re-querying the database.  This is now also tabbed browsing unfriendly as the user is able to open up two versions of the wizard, both of which will use session values from the latest wizard to be opened.  

Solution.  Don’t share session values from other pages unless you’re sure they will not be affected by tabbed browsing.  If they are ok and you are sharing them, then they should probably be application items.  When branching back to itself, ensure you reset the pk item value and clear the page cache, thus forcing a complete refresh of the session values.

To find which branches are potentially tabbed browsing unfriendly
  •  Go to utilities/branch utilities/Branches per Page report
  • Compute a new column with the following formula:
  • decode(A,substr(B,14,length(A)),'Y','N')
  • Filter the new column to display ‘Y’ and look for branches that have no parameters in them.

To find pages that reference items from other pages:
  • Utlities/Advisor  hit "Deselect All" and check the "Referenced item is on Current Page"