Tag: tips-&-tricks

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.


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)

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

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


Tabular Form – set default values with jQuery


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


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;
    if (this.value != "") {


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



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

Apex CSV Import: “Data Loading Failed”

If you are using the Apex built-in Data Loading feature to allow your users to upload CSV files, you may have encountered this error.

(Note: the error may appear differently in your application as I have built a custom error handling function)

It’s not a particularly useful message, and the logs don’t seem to shed much light on the problem either – reporting only the following:

DATA_LOAD - Final collection is created
...Execute Statement: select 1 from "DEMO"."MY_TABLE" where "RECORD_ID" = :uk_1
Add error onto error stack
...Error data:
......message: Data Loading Failed
......additional_info: ORA-01403: no data found
......ora_sqlerrm: ORA-01403: no data found
  ORA-06512: at "APEX_040200.WWV_FLOW_DATA_UPLOAD", line 4115
  ORA-06512: at "APEX_040200.WWV_FLOW_PROCESS_NATIVE", line 213
  ORA-06512: at "APEX_040200.WWV_FLOW_PROCESS_NATIVE", line 262
  ORA-06512: at "APEX_040200.WWV_FLOW_PLUGIN", line 1808
  ORA-06512: at "APEX_040200.WWV_FLOW_PROCESS", line 453

After trial and error I tracked down one potential cause of this error so I thought I’d share it in case it happens again. I’ll probably come across this again later and forget what the solution was and find this article.

In my case (Apex 4.2.4), the problem was caused by an invalid entry in the Column Name Aliases list of values. I was using a custom List of Values so that alternative names for the columns would be automatically mapped without the user having to select them every time. To do this, I had to edit the List of Values directly to add the alternative names; but I had mistyped one of the Return Values which must map to a real column name on the target table. Whenever I picked this column for an import, I’d get the “Data Loading Failed” error message. Correcting the return value resolved the issue.

In order to stop this happening again, I added the following check to my Apex QA script (this is run whenever the application is deployed):

PROMPT Invalid dataload column mappings (expected: none)
SELECT REPLACE(lt.owner,'#OWNER#',USER) AS owner
      ,le.return_value  AS target_column_not_found
      ,le.display_value AS col_alias
      ,lt.name AS dataload_definition
FROM   apex_appl_load_tables lt
JOIN   apex_application_lov_entries le
ON     le.lov_id = lt.column_names_lov_id
  FROM   all_tab_columns tc
  WHERE  tc.owner = REPLACE(lt.owner,'#OWNER#',USER)
  AND    tc.table_name = lt.table_name
  AND    tc.column_name = le.return_value)
ORDER BY lt.name, le.display_sequence;

If the above query returns any rows, it’ll be a problem.

Remove punctuation from string using Javascript

I’m a morning person, and my mind is usually sharpest on Monday or Tuesday mornings, so these are the best times for me to work on fiddly javascript stuff. Today was one of those mornings and here are the results, just in case I want to refer back to them later on.

I had many items dotted around an Apex application where the user is allowed to enter “Codes” – values that must be uppercase and contain no spaces or other punctuation characters – except underscores (_) were allowed.

To make things easier for the user, I wanted the page to automatically strip these characters out when they exit the field, instead of just giving validation errors (Note: I still included the validations, but the javascript just makes the process a bit smoother for the user doing the data entry).

My Apex application already has a global .js file that is loaded with each page, so all I had to do was add the following code to it:

function cleanCode (c) {
  return c.replace(/[^A-Za-z0-9_]/g,"");

$(document).ready(function() {

  //automatically remove non-code characters from
  //"edit_code" class
  $( document ).on('change', '.edit_code', function(){
    var i = "#"+$(this).attr("id");
    $(i).val( cleanCode($(i).val()) );


EDIT: greatly simplified regexp based on the excellent contribution by Jacopo :)
EDIT #2: corrected, thanks to Sentinel

Finally, on each “Code” page item, I set the following attribute (or append, if other classes have already been added):

HTML Form Element CSS Classes = edit_code

For code items within a tabular form, I set the following column attribute:

Element CSS Classes = edit_code

Make Tabular Form Conditionally Read-only

If you decide to use an editable tabular form to present a number of records for viewing and/or editing, but you have some users who are only allowed to view the data but not edit it, you’d think you could set the “Readonly” condition on the region; but this condition is only applied to any extra region items you add, not to the editable items within the report itself.


Here’s my tabular form, with the records still editable:


One way to get around this is to have two separate report regions on the page – one is the editable tabular report, the other is an ordinary standard report that doesn’t have any of the edit capabilities – and use conditions to hide one or the other depending on the user’s authorisation.

Another way is to use conditions and jQuery to make all the items in the tabular form readonly:

1. Put a condition on all the buttons (e.g. “Add Row”, “Delete”, “Save”, etc) so they are not shown if the user doesn’t have edit privilege

2. Put the same condition on the Multi-Row processes so that they will not run if the user doesn’t have edit privilege.

3. Set the static ID on the region so jquery can find it:


4. Add a Dynamic Action to make all the input items within that region disabled:

Event: Page Load

Authorization Scheme: {Not Editor} (this is just an example where I have an Authorization scheme called “Editor”; alternatively you could set a Condition instead)

True Action: Execute Javascript Code

$("#linesreport input, #linesreport select").prop("disabled",true)

Now, when the page loads, if the user doesn’t have edit privilege the items are rendered readonly, e.g.:


There are other variations on this theme, e.g. we could target the jQuery expression to just the text inputs while still allowing the user to use the checkboxes (e.g. if there was some action that we wanted to allow). Of course, if I wanted to hide the checkboxes completely, I’d just put the authorization on the [row selector] column in the tabular report definition.