I had an APEX page based on a Form region that I’d built by hand (rather than using the wizard). I was wondering why the user always got an unexpected warning “Changes that you have made may not be saved.” – even though they hadn’t changed anything on the page.
I noticed that the item had a List of Values, and it had the Display Null Value setting set to No; however, the value in the underlying column was NULL. What was happening was that the item could not handle a null value, so it was changing to the first value in the LOV; this in turn marked the item as “changed” which caused the “unsaved changes” warning to show when the user tries to navigate away from the page.
When I set Display Null Value to Yes, the problem was resolved. Alternatively, I could have ensured that the underlying column would always have a value (e.g. by putting a NOT NULL constraint on it), which would also have resolved this problem.
Font APEX is preferred most of the time but sometimes there are icons I really want to use which are not (yet) included. For these cases I want to load the latest Font Awesome library.
It is possible to load Font Awesome instead of Font APEX by opening Shared Components -> Themes -> Universal Theme, and setting Custom Library File URLs to the location of the library (wherever you have loaded it). However, this replaces the Font APEX font completely so you can’t use both at the same time using this method.
In order to use both at the same time, I’ve downloaded the latest free version of Font Awesome 5 from here (fontawesome.com), taken a copy of the file css/all.css and edited it to replace all occurrences of “.fa” with “.fa5” (if you use CSS precompiler you can do this by editing the appropriate variables file, e.g. _variables.less). This is necessary because the “fa” class prefix would conflict with Font APEX. I named my custom file “fa5.css” and created a minified version as well.
On my web server I created the folder /fa5 under my public html folder, and copied the following files / folders into it:
/fa5/webfonts/* (all contents)
In my APEX application, in the Universal Theme properties I set:
Custom Library File URLs = /fa5/css/fa5#MIN#.css
Custom Prefix Class = fa5
(optional)Custom Classes = (comma-delimited list of your favourite icons)
Alternatively, you could upload the library into your Static Application Files and load them from there.
If I want my page to use an icon from Font APEX, I use the fa- icons as usual, e.g. fa-apex. Where I need an icon from Font Awesome, I have to include both the fa5 class as well as the icon class, e.g. fa5-restroom. For brand icons, of which Font Awesome has a large selection, the class is fa5b, e.g. fa5b fa5-amazon-pay. Font Awesome also includes a range of modifiers including sizes, spin, pulse, rotating, mirroring, and stacking.
You are, of course, asking, can I stack two icons AND spin just one of them? The answer, of course, is yes:
Issue #1: Featured Hero Region Icon
When I tried to use a Font Awesome icon in a Hero region with the “Featured” style, the font failed to load. This is because the “Featured” style overrides the font-family causing it to fail to use the Font Awesome font. To fix this, on the page I added the following CSS:
In a navigation menu, APEX includes the “fa” class which controls the positioning of the icons in the menu, but it also overrides the font library and fails to load the icon from Font Awesome. To fix this, I further edited my fa5.css file (as well as the minified version) to add the following:
The Icon attribute on a region can only be used to provide a class (or list of classes) to serve as the icon for the region. To use a Stacked icon in this case is impossible as the stack must be specified using a span with nested nodes for each icon in the stack. A workaround for this is to use some jQuery to modify the html at runtime, as follows:
Set the region’s Static ID, e.g. stacked
Add this to the page’s Execute When Page Loads (this example is for a Hero region:
It’s a messy kludge, and you’ll have to adapt it if you want to use it in other region templates (check what the span class is), but if this provides significant business benefit then it might be worthwhile.
Comparing Font Awesome 5 Free with Font APEX
I’ve loaded lists of all the icons in the Font Awesome 5 Free and the Font APEX libraries into a table and created a little application that allows me to compare them.
Note: these stats are not perfect because some of the icon names are slightly different between the libraries – for example, all of the “hand” icons have slightly different names between the two libraries.
An application I maintain needed a checklist feature added. I wanted to show a “Yes / No” switch for a list of checklist items. Initially, when the record is created, the checklist is populated with the questions along with a NULL for the response.
I generated the switches in an ordinary Classic report using code like this:
select r.name as risk_category
(p_idx => 10
,p_value => i.response
,p_on_value => 'Yes'
,p_on_label => 'Yes'
,p_off_value => 'No'
,p_off_label => 'No'
,p_item_id => 'RESPONSE_' || rownum
,p_item_label => i.risk_category_code || '-' || i.rci_fk
,p_attributes => 'data-risk="' || i.risk_category_code || '"'
||apex_item.hidden(p_idx => 11, p_value => i.rci_fk)
from supplier_risk_checklist_items i
join risk_categories r on r.code = i.risk_category_code
where i.sri_fk = :P10_ID
order by r.sort_order nulls last, i.sort_order nulls last, i.rci_fk
The problem is that all the NULL responses are indistinguishable from the “No” responses. If the user clicks “Yes” or “No”, the response is saved correctly – but the user cannot tell which ones haven’t explicitly been answered yet.
To find a solution for this issue I started by examining the HTML being generated for each question. I noticed that the input option for the “No” value was marked as “checked”, while the hidden input item had no “value” on it. These were the ones that needed fixing.
In the page’s Execute When Page Loads, I search for all instances of responseSwitch where the hidden input item does not have a value attribute; in each case, I find the associated input item that shows “No” and unset the “checked” property:
// workaround for generated switch items showing "No" when value is null
// search for the hidden input items without a value (i.e. null on the database)
var id = $(this).attr("id");
// these will have "checked" on the "No" option; remove it
This makes it clear to the user which checklist items have been answered so far, and which ones haven’t.
Note: the user is given no way to unset an answer once it has been saved; if this were a problem I would change this to use an ordinary Select list item instead of a Switch item.
In the past when I’ve needed to add half a dozen or more buttons to a page, I’ve sometimes encased them in a Collapsible region so that the user can slide them out of the way instead of clogging up the screen. Recently however I’ve started (sparingly) using a Menu Popup, as per this tutorial. The issue I have with this method, however, is that the menu items are defined in a shared component (a List) which means it’s not defined on a per-page basis.
Some of the actions simply need to do a Submit on the page, which is simple enough: set the URL Target to something like:
In other cases, the action needs to do something more specific to the page, e.g. show a region:
Or the action might need to navigate to another page, passing parameters based on specific items on the page. This means the list, defined in Shared Components, now has hardcoded elements that are only useful for that one page; more to the point, they are defined outside of the page – I’d rather that everything specific to a page is defined within that page’s definition.
The approach I’m using now is to use a custom trigger. Each list item has its URL Target set to something like:
The third parameter is set to a unique code that the page can use to identify which menu option was chosen. This parameter will be passed to this.data in the custom trigger’s event handler.
On the page, I have a Dynamic Action with the following attributes:
Custom Event: menuAction
The benefit of this approach is that this trigger becomes the jumping-off point for all such menu actions for this page. In theory I could re-use the same List on multiple pages (if the items in the list are generic enough) but have different behaviour occur for each menu item specific to each page. The only challenge with this approach might be if you needed some conditions on each menu item, e.g. so they are shown or hidden in specific circumstances. If the condition for a menu item references a particular page item the List will no longer be generic and re-usable. For this reason, I usually still use a separate List for each menu for each page.
Perhaps in a future release of APEX we will gain the ability to define a List on a Page instead of in Shared Components. In the meantime, if you are interested in all the details on this method (including a solution for implementing a redirect to another page, or to open a modal page), refer to this tip.
EDIT 22/8/2019:Thanks to Robert Gerstein who noticed an issue with this solution when using Internet Explorer. A workaround for this is to not call apex.event.trigger directly in the URL on the list item, but to call a function instead (refer to comments below).
An item in the Universal Theme using the Optional – Floating template looks like this:
An item using the Required – Floating template looks like this:
In addition, if the item is required we would most probably set the Value Required attribute to Yes. What if the item is sometimes required but not always? How do we create a Conditionally Required field?
Firstly, we would make sure there is a Validation on the field that checks that the value is provided if required. This way, regardless of what the form may or may not send to the database, it is validated appropriately.
Secondly, to indicate to the user that the item is required or optional, based on the value of another item, we can use a Dynamic Action that sets the required item property (this triggers the client-side validation) and adds or removes the is-required class from the item’s container (this shows the little red “required” indicator on the page).
For example, let’s say that whether item P1_COST_CENTRE is required or not is dependent on whether a hidden item, P1_COST_CENTRE_REQUIRED, has the value 'Y'.
Create a Dynamic Action
Selection Type: Item(s)
Client-side Condition Type: Item = Value
var item = $("#P1_COST_CENTRE");
var item = $("#P1_COST_CENTRE");
The above code works for all item templates (“Optional”, “Optional – Above”, “Optional – Floating”, etc.) in the Universal Theme; I’ve tested this on APEX 18.2 and 19.1.
Note: this is custom code for the Universal Theme, so it may or may not work for other themes; and might stop working in a future revision of the theme.
To use these plugins, select them as an Action to take on a Dynamic Action:
EDIT 29/7/2019: modified to use a better method to find the container div.
In other themes, the way that a required field is rendered is different. For example, in Theme 26 (Productivity Applications) the label for a required item is rendered in bold, along with a red asterisk; if the item is optional, no red asterisk is rendered. The way to make an item conditionally mandatory in this theme is:
Set the item to use the Required template (so that the red asterisk is rendered).
var itemLabel = $("label[for='P1_COST_CENTRE']");
To make the item required again:
var itemLabel = $("label[for='P1_COST_CENTRE']");
The floating item type has an optional “Icon” property that allows you to render an icon next to the item, which can help users quickly identify what the item is for. This is especially helpful when the form has a lot of items.
The icon attribute can be static, e.g. fa-hashtag, or it can be chosen based on the value of another item, e.g. &P1_FA_ICON..
If you want the icon to change dynamically as the user enters or modifies data, it’s a little bit more complicated. I have a list item based on a table of asset categories, and each asset category has an icon assigned to it. When the user selects an asset category from the list I want it to get the icon from the table and show it in the item straight away.
The Execute PL/SQL Code action is a simple PL/SQL block which gets the icon from the reference table for the selected category code. Make sure the “Wait for Result” is “Yes”, and make sure the Items to Submit and Items to Return are set to P260_CATEGORY_CODE and P260_CATEGORY_FA_ICON, respectively.
from asset_categories x
where x.code = :P260_CATEGORY_CODE;
On examining the source of the page, we see that the select item is immediately followed by a span which shows the icon:
It’s a little gimmicky but it’s an easy way to delight users, and it might help them to quickly identify data entry mistakes.
Refreshing any of our non-prod environments (e.g. dev, test, etc.) with a clone from production is a fairly regular process at my client. A recurring issue with this is emails: we’ve had occasion where users have received a second copy of an email immediately after the clone has completed. This was confusing because they thought the event that had triggered the email actually occurred twice.
As it turns out, the duplicate emails were caused by the fact that the emails happened to be waiting in the APEX mail queue in production at the time of the export. After the export, the APEX mail queue was processed normally in production and the users received their emails as expected; after the clone was completed, the database jobs were restarted in the cloned environment which duly processed the emails sitting in the cloned queue and the users effectively got the same emails a second time.
What’s worse, if the same export were to be used for multiple clones, the users might get the same emails again and again!
A good way to solve this sort of issue would be to isolate the non-prod environments behind a specially configured mail server with a whitelist of people who want (and expect) to get emails from the non-prod systems. We don’t have this luxury at this client, however.
Instead, we have a post_clone.sql script which is run by the DBAs immediately after creating the clone. They already stop all the jobs by setting job_queue_processes=0.
In case the mail queue happens to have any emails waiting to be sent, the post clone script now includes the following step:
*** WARNING: DO NOT RUN THIS IN PRODUCTION! ***
for r in (
(p_security_group_id => apex_application_install.get_workspace_id);
This script is run as SYS but it could also be run as SYSTEM or as APEX_nnnnnn, depending on your preference.
ADDENDUM: Overriding the From Email Address
Christian Neumüller commented that an additional technique that might be useful is to override the From (sender) email address to indicate which environment each email was sent from. To do this, run something like the following:
'apex-' || sys_context('userenv','db_name') || '@mydomain');
I’ve tested this in APEX 19.1 and it seems to work fine. Regardless of the p_from parameter that the code passes to apex_mail.send, the EMAIL_FROM_OVERRIDE email address is used instead. Note that this is currently undocumented, so this may stop working or change in a future release.
The updated Universal Theme has added new “Floating” item templates which look great, e.g.:
I had a list item which I wanted to leave optional; if the user leaves it null, I wanted it to show a “default” display value (derived at runtime). To implement this, I added a hidden item (P10_DEPTNO_DEFAULT) and on the list item set Null Display Value to &P10_DEPTNO_DEFAULT..
If the page is shown in read-only mode, however, the list item is rendered as a Display Item, and the Null Display Value attribute is ignored:
To solve this, I added a Dynamic Action which injects the default value into the HTML for display (without affecting the value of the underlying item):
Event: Page Load
Server-side Condition: <page is readonly> AND :P10_DEPTNO IS NULL
Fire on Initialization: No
This finds the span for the display-only item and injects the default display value for display:
Like most active sites our applications have a healthy pipeline of change requests and bug fixes, and we manage this pipeline by maintaining a steady pace of small releases.
Each release is built, tested and deployed within a 3-4 week timeframe. Probably once or twice a month, on a Thursday evening, one or more deployments will be run, and each deployment is fully scripted with as few steps as possible. My standard deployment script has evolved over time to handle a number of cases where failures have happened in the past; failed deployments are rare now.
One issue we encountered some time ago was when a deployment script happened to be run at the same time as a database scheduler job; the job started halfway during the deployment when some objects were in the process of being modified. This led to some temporary compilation failures that caused the job to fail. Ultimately the deployment was successful, and the next time the job ran it was able to recover; but we couldn’t be sure that another failure of this sort wouldn’t cause issues in future. So I added a step to each deployment to temporarily stop all the jobs and re-start them after the deployment completes, with a script like this:
for r in (
where schedule_type = 'CALENDAR'
and enabled = 'TRUE'
order by 1
(name => r.job_name
,force => true);
This script simply marks all the jobs as “disabled” so they don’t start during the deployment. A very similar script is run at the end of the deployment to re-enable all the scheduler jobs. This works fine, except for the odd occasion when a job just happens to start running, just before the script starts, and the job is still running concurrently with the deployment. The line force => true in the script means that my script allows those jobs to continue running.
To solve this problem, I’ve added the following:
prompt Waiting for any running jobs to finish...
whenever sqlerror exit sql.sqlcode;
max_wait_seconds constant number := 60;
start_time date := sysdate;
where state = 'RUNNING'
and rownum = 1;
when no_data_found then
job_running := null;
exit when job_running is null;
if sysdate - start_time > max_wait_seconds/24/60/60 then
'WARNING: waited for '
|| ' seconds but job is still running ('
When the DBA runs the above script, it pauses to allow any running jobs to finish. Our jobs almost always finish in less than 30 seconds, usually sooner. The loop checks for any running jobs; if there are no jobs running it exits straight away – otherwise, it waits for a few seconds then checks again. If a job is still running after a minute, the script fails (stopping the deployment) and the DBA can investigate further to see what’s going on; once the job has finished, they can re-start the deployment.
It’s well known that after processing a page one often needs to add a Branch so the user is taken to another page, e.g. to start the next step in a process. It’s less common to need a Branch that is evaluated before the page is shown. This is a “Before Header” Branch and I use it when the user might open a page but need to be redirected to a different one.
A good example is a page that is designed to direct the user to two or more different pages depending on what data they requested.
Another way that a branch like this can be useful is where a user might navigate to a page that should show a record, but if the record ID is not set, I might want the page to automatically redirect the user back to a report page instead.
Of course, you have to take some care with these sorts of branches; if the target page also has its own “Before Header” branch, that will also be evaluated; if the user ends up in a loop of branches the page will fail to load (with a “ERR_TOO_MANY_REDIRECTS” error).
This is the order in which various steps are done before APEX shows a page:
“Before Header” Branches
“Before Header” Processes
One of the implications of the above order is that any computations or processes that set or change application state (e.g. an application item) are not run before it evaluates conditions or attributes for any “Before Header” Branches. This applies regardless of whether the computations or processes are defined on the page, or defined globally in Shared Components. This little detail tripped me up today, because I had a branch that I needed to run based on a condition that relied on state that should have been set by a “Before Header” process defined globally.
If you need to redirect a user to a different page on the basis of any application state that is set by a computation or process, you can use a PL/SQL Process instead that does the redirect. The PL/SQL code to use is something like: