BIG checkboxes

Getting older, it’s getting harder to see and click those tiny checkboxes…

checkboxestoosmall

csscheckboxes

input[type=checkbox] {
/* Double-sized Checkboxes */
-ms-transform: scale(2); /* IE */
-moz-transform: scale(2); /* FF */
-webkit-transform: scale(2); /* Safari and Chrome */
-o-transform: scale(2); /* Opera */
}

checkboxesbig

CAN YOU SEE THEM NOW? Ah, good. That’s all right then.

Brought to you by dept-of-coding-by-copy-and-paste.


Refresh APEX Calendar

calendarwithrefreshbutton.PNG
Sometimes it’s the simple little things that can add polish and make your Apex application shine. One simple little thing that you can do is add a Refresh button to improve the usability of your Apex 5 calendar. This makes it easy for the user to see recent changes on the database, e.g. if events had been added or changed since the page had last been loaded.

  1. Set the Static ID on the Calendar region (e.g. “eventscalendar“)
  2. Add an Icon button (Button Template = “Icon”) to the calendar region
  3. Set the button’s Static ID (e.g. “refreshbutton“)
  4. Set Icon CSS Classes to “fa-refresh
  5. Set Action to “Defined by Dynamic Action”
  6. (optional) Set Template Option -> Style to “Remove UI Decoration”
  7. Add a Dynamic Action to the button, Event = “Click”
  8. Set Fire on Page Load to “No”
  9. Add a True Action “Execute Javascript Code” with the code below:
$("#eventscalendar_calendar").fullCalendar("refetchEvents");

This calls the refetchEvents method of the FullCalendar object. Replace the “eventscalendar” part of the id with whatever static ID you set on the Calendar region in step #1.

Now, to add a bit of pizzazz you can get the refresh button icon to spin while the calendar is being refreshed. To do this, change the dynamic action code to this instead:

$("#refreshbutton span.t-Icon").addClass("fa-spin");
window.setTimeout(function() {
  $("#eventscalendar_calendar").fullCalendar("refetchEvents");
  window.setTimeout(function() {
    $("#refreshbutton span.t-Icon").removeClass("fa-spin");
  }, 1000);
}, 50);

This code starts the refresh icon spinning before invoking refetchEvents, then stops the icon spinning after it has completed. Note that these are done via timeouts (otherwise the icon isn’t repainted until after the entire javascript function has completed). I added a wait of 1 second prior to stopping the spinning because most of the time the refresh is too quick to notice the spinning effect.

You can, if it makes sense in your case, also make the calendar automatically refresh itself periodically, using some simple javascript: add the following function to the page Function and Global Variable Declaration:

function refreshCalendar() {
  $("#refreshbutton span.t-Icon").addClass("fa-spin");
  window.setTimeout(function() {
    $("#eventscalendar_calendar").fullCalendar("refetchEvents");
    window.setTimeout(function() {
      $("#refreshbutton span.t-Icon").removeClass("fa-spin");
    }, 1000);
  }, 50);
}

Then add this to start the timer in the page attribute Execute when Page Loads:

var periodicrefresh = setInterval(function() {
                                    refreshCalendar();
                                  }, 30000);

In this example, I’ve set the timer to go off every 30 seconds. Not only does it refresh the calendar, but the user gets feedback on what’s going on because the refresh button icon is spinning. Be careful not to set the timeout too low, or else your database could get very busy!

The function I’ve declared can now also be reused by the button’s dynamic action, so I can replace the DA javascript with simply:

refreshCalendar();

APEX Developer Toolbar Options

One of the things that used to bug me about the Apex developer toolbar was that it sometimes obscured the content I was trying to test at the bottom of the page; you could turn it off but then next thing you want to access it you have to jump through the hoops to turn it back on again.

I just noticed it now has some new display options which solves this problem perfectly:

devtoolbaroptions1

  • Auto Hide – I turn this on so that it slides almost completely out of the way when I don’t want it (move your mouse over it to make it pop out again, click into your page to hide it)
  • Show Icons Only – once you’re familiar with the options you can shrink the toolbar to show only the icons (hover over the icon to see the label)
  • Display Position – put it on the Right-hand side of the window instead of the bottom

A random string of digits

715175039885776956103287888080

I needed to generate a random string with an exact length consisting of numeric digits, that I could send in an SMS to a user as a temporary account “pin”. DBMS_RANDOM.string is unsuitable for this purpose as its supported modes all include alphabetic characters. So I used DBMS_RANDOM.value instead. I call TRUNC afterwards to lop off the decimal portion.

FUNCTION random_pin (digits IN NUMBER)
  RETURN NUMBER IS
BEGIN
  IF digits IS NULL OR digits < 1 OR digits > 39 THEN
    RAISE_APPLICATION_ERROR(-20000,'digits must be 1..39');
  END IF;

  IF digits = 1 THEN
    RETURN TRUNC( DBMS_RANDOM.value(0,10) );
  ELSE
    RETURN TRUNC( DBMS_RANDOM.value(
                    POWER(10, digits-1)
                   ,POWER(10, digits) ));
  END IF;
END random_pin;

random_pin(digits => 6);

482372

EDIT 8/1/2016: added special case for 1 digit

ADDENDUM

Because the requirements of my “pin” function was to return a value that would remain unchanged when represented as an integer, it cannot return a string of digits starting with any zeros, which is why the lowerbound for the random function is POWER(10,digits-1). This, unfortunately, makes it somewhat less than perfectly random because zeroes are less frequent – if you call this function 1000 times for a given length of digits, then counted the frequency of each digit from 0..9, you will notice that 0 has a small but significantly lower frequency than the digits 1 to 9.

To fix this, the following function returns a random string of digits, with equal chance of returning a string starting with one or more zeroes:

FUNCTION random_digits (digits IN NUMBER)
  RETURN VARCHAR2 IS
BEGIN
  IF digits IS NULL OR digits < 1 OR digits > 39 THEN
    RAISE_APPLICATION_ERROR(-20000,'digits must be 1..39');
  END IF;
 
  RETURN LPAD( TRUNC(
    DBMS_RANDOM.value(0, POWER(10, digits))
    ), digits, '0');
END random_digits;

The above functions may be tested and downloaded from Oracle Live SQL.


Detect Empty List

You have a Select List item on your page driven from a dynamic query, e.g. one that only shows valid values. One day, users notice that the list appears empty and raise a defect note.

emptylist.PNG

You check the query behind the list and verify that indeed, the list should be empty because there are no valid values to show. It’s an optional item so the user is free to save the record if they wish.

There are a number of ways we could make this more user-friendly: depending on the specifics of the situation, we might just hide the item, or we might want to show an alternative item or a warning message. We can do any of these things quite easily using either a computation on page load (if the list doesn’t change while the page is open) or a dynamic action.

In the case of my client, they wanted the item to remain on screen, but to show an orange warning message to let them know that there are no gateways currently available; this is only a warning because there are subsequent processes that can handle the missing gateway (e.g. a higher-privileged user can assign a “hidden” gateway to the record if they deem it suitable).

To do this we create a display item (e.g. “P1_NO_GATEWAY_WARNING” which shows the warning message) and a dynamic action with the following attributes:

  • Event = Page Load
  • Condition = JavaScript expression
  • Value = listIsEmpty("P1_GATEWAY_ID")
  • True Action = Set Value
  • Set Type = Static Assignment
  • Value = Warning: no gateways currently available
  • Selection Type = Item(s)
  • Item(s) = P1_NO_GATEWAY_WARNING

In the page’s Function and Global Variable Declaration, or (even better) in the application’s global javascript file, we add the following:

function listIsEmpty(itemName) {
  return $("#" + itemName + " option:enabled").filter(
    function(){return this.text;}
    ).length==0;
}

This was adapted from some solutions here. It looks for all <option>s under the item, filters the list for options which are not disabled and have a label, and returns true if the remaining set is empty. I added the this.text bit because the empty lists generated by Apex include a single empty option for the “NULL” value. This is because I have set the list item’s Null Display Value to blank (null).

emptylistwarning


Media player in APEX

Quite a long time ago I made a collection of MP3s available from our APEX website and made them playable within the browser using Google’s shockwave player, using code like this:

<embed type="application/x-shockwave-flash"
       flashvars="audioUrl=#FILE_URL#"
       src="/3523697345-audio-player.swf"
       width="400"
       height="27"
       quality="best">
</embed>

This relies on the user’s browser being able to run flash applications. It looked like this:
audio-player-old

With HTML5, however, this is no longer required, so I’ve updated it to:

<audio controls preload>
  <source src="#FILE_URL#" type="audio/mpeg">
</audio>

Not only is it simpler and no longer requires flash, it looks much nicer as well:
audio-player-new

Note: you may or may not want to include the preload tag, especially if you have more than one audio control on a page.


Auto-convert field to uppercase

This is just a quick note for my future reference. I needed all items with the class “uppercase” to be converted to uppercase, and I thought it would work with just some CSS:

.uppercase { text-transform:uppercase; }

This makes the items appear uppercase, but when the page is posted it actually sends the values exactly as the user typed. They’d type in “lower“, it looks like “LOWER” on screen, but gets posted as “lower“.

In many cases I could just convert the value in my PL/SQL code, but in cases where I was using Apex tabular forms, I don’t know a simple way to intercept the values before the insert occurs.

To solve this I added this to the page’s Execute when Page Loads:

//the item looks uppercase but the internal value
//is still lowercase
$(document).on('change','.uppercase',function(){
  var i = "#" + $(this).attr("id");
  $(i).val( $(i).val().toUpperCase() );
});

Or, even better, add this to the application’s global javascript file:

$(document).ready(function() {
  $(document).on('change','.uppercase',function(){
    var i = "#" + $(this).attr("id");
    $(i).val( $(i).val().toUpperCase() );
  });
});

Disable IE Compatibility Mode

Most places I’ve worked at allow employees to use any of the major browsers to do their work, but mandate an “SOE” that only supports IE, presumably because that generates the most amount of work for us developers. I’d conservatively estimate that 99% of the rendering bugs I’ve had to deal with are only reproducible in IE. (cue one of the thousands of IE joke images… nah, just do a Google Image search, there’s plenty!)

Anyway, we had a number of these rendering issues in Apex on IE8, IE9 and IE10, mainly in edge cases involving some custom CSS or plugins. In some cases I was never able to reproduce the issue until we noticed that the user had inadvertently switched “IE Compatility Mode” on:

iecompatibility1

We told them to make sure the icon was grey, like this:

iecompatibility2

– and most of the issues went away.

Since there’s nothing in our Apex application that requires compatibility mode, we would rather the option not be available at all. To this end, we simply add this code to all the Page templates in the application:

<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">

This is added just after the <head> tag, like this:

iecompatibility3

Now, the compatibility button doesn’t appear at all – one less choice for users and less bug reports to deal with:

iecompatibility5

For more information, see this stackoverflow question and read all the answers. Note that it may be better to add this as a header in the response generated by your web server. In our case it was simpler to just add it into the html.


Deploying APEX: showing an “Under Maintenance” web page

systemdown

I’ve added this script to our toolbelt for future upgrades. We have a friendly “System is under maintenance, sorry for any convenience” web page that we want to show to users while we run upgrades, and we want it to be shown even if we’re just doing some database schema changes.

So I took the script from here and adapted it slightly, here’s our version:

declare PRAGMA AUTONOMOUS_TRANSACTION;
  v_workspace CONSTANT VARCHAR2(100) := 'MYSCHEMA';
  v_workspace_id NUMBER;
begin
  select workspace_id into v_workspace_id
  from apex_workspaces where workspace = v_workspace;
  apex_application_install.set_workspace_id (v_workspace_id);
  apex_util.set_security_group_id
    (p_security_group_id => apex_application_install.get_workspace_id);
  wwv_flow_api.set_flow_status
    (p_flow_id             => 100
    ,p_flow_status         => 'UNAVAILABLE_URL'
    ,p_flow_status_message => 'http://www.example.com/system_unavailable.html'
    );
  commit;
end;
/

It uses an autonomous transaction because we want the system to be unavailable immediately for all users while the deployment is running.

Warning: WWV_FLOW_API is an undocumented package so this is not supported.

The opposite script to make the application available again is:

declare PRAGMA AUTONOMOUS_TRANSACTION;
  v_workspace CONSTANT VARCHAR2(100) := 'MYSCHEMA';
  v_workspace_id NUMBER;
begin
  select workspace_id into v_workspace_id
  from apex_workspaces where workspace = v_workspace;
  apex_application_install.set_workspace_id (v_workspace_id);
  apex_util.set_security_group_id
    (p_security_group_id => apex_application_install.get_workspace_id);
  wwv_flow_api.set_flow_status
    (p_flow_id             => 100
    ,p_flow_status         => 'AVAILABLE'
    );
  commit;
end;
/

However, if we run the f100.sql script to deploy a new version of the application, we don’t need to run the “set available” script since the redeployment of the application (which would have been exported in an “available” state already) will effectively make it available straight away.

UPDATE FOR APEX 5.1 and later

The APEX API has been updated with a documented and supported call – APEX_UTIL.set_application_status to do this which should be used in APEX 5.1 or later.


Tabular Form – set default values with jQuery

tabular-form-default-jquery4

I have a simple tabular form with a numeric “sort order” column. I want the value of this column to be defaulted automatically, based on the maximum value of the rest of the records on the screen. Unfortunately the builtin Apex default type for columns in a tabular form can only be based on an Item, or a PL/SQL expression or function. I didn’t want to make a database call to get the maximum value because the user may have added multiple records which have not yet been saved to the database.

I tried basing the default on a hidden page item which I kept updated based on the values entered, but it seems the tabular form only gets the item’s value on page load and doesn’t re-examine the item’s value when you click the “Add” button. Instead, I had to turn to javascript and jQuery to get the job done.

1. Add a class to the tabular form column by setting the Element CSS Classes column attribute (I’ve used “sortorder” as the class name).

tabular-form-default-jquery2

2. Add a javascript function to the page that searches for all the “sortorder” items, calculates the maximum value and adds 10, and assigns it to the last sortorder item on the page.

function setNextSortOrder() {
  var highest=0;
  $(".sortorder").each(function(){
    if (this.value != "") {
      highest=Math.max(highest,parseFloat(this.value));
    }
  });
  $(".sortorder").last().val(highest+10);
}

tabular-form-default-jquery1

3. Modify the javascript for the “Add” button to call the setNextSortOrder function immediately after adding a row.

javascript:{apex.widget.tabular.addRow();setNextSortOrder();}

tabular-form-default-jquery3

It’s a bit hackish and might not work correctly in some cases but it’ll do for now.