Tips for APEX Application Constants
How to use Text Messages for Constants
It's very common for APEX application to have application level items (usually these are named
G_... and are defined in Shared Components > Application Items). For values that are unique to a given user/session application items are a logical place to store them (ex:
APP_USER_ID). When the item contains a value a new row is inserted into the
apex_application_items table (this is really a view but there's an underlying table that stores the value).
If an application item is constant for the entire application (regardless of user/session) then it may not make sense for a new row to be created to store a session state value. For example, suppose you had the following setup:
Application item called
Set for each session based on a value in a lookup table
In dev you set to
Demo Dev, test:
Demo Test, and production:
This means that the following would occur for every session:
Computation or process
on new instanceto load value from lookup table
A new row is created in
apex_application_itemsfor every session
The above steps would happen for every session despite the fact that the value does not change. In smaller systems this may not matter however on higher volume systems or environments with lots of applications this may add additional overhead.
To avoid the additional overhead you can use a Text Message. Text Messages are primarily intended for translation purposes however they also allow developers to have configurable application constants. Text messages are different than Application Settings in the sense that they can be referenced in APEX as a substitution string.
The following example shows how to use text messages for this purpose.
Go to Shared Components > Text Messages
Create Text Message and add the following:
On the login page (or this can be anywhere in the application where you want a dynamic application name) change the region title to:
&APP_TEXT$APP_NAME. More information about referencing text messages as substitution strings can be found in docs.
When the page is run it shows
Demo Dev as expected:
To change this in other environments it's unrealistic to set the value via the APEX application builder as most developers won't have access to Test and Prod instances. Instead it can be easily set through the API
apex_lang.update_message As part of your release process after the APEX application has been update the following code should be run to set the value:
c_apex_app_id apex_application_translations.application_id%type := 200;
from apex_application_translations aat
and aat.application_id = c_apex_app_id
and aat.translatable_message = 'APP_NAME'
-- Detect if in APEX already
-- Usually necessary as this should be run as a post-install script in SQLcl
-- If you're not in an APEX application you'll get following error:
-- ORA-20001: Package variable g_security_group_id must be set.
-- May get ORA-20987: APEX - An API call has been prohibited.
-- If so go to Shared Components > Security Attributes
-- Check box: Runtime API Usage > Modify This Application
if apex_application.g_flow_id is null then
p_app_id => c_apex_app_id,
p_page_id => 1,
p_username => 'dummy'
-- Set text message:
p_id => l_translation_entry_id,
-- Note: This value should come from a lookup table
p_message_text => 'Dev Test'
-- Commit is required for changes to take affect
After running this script the login page will show the update application name:
I've used this technique in a very high-use application that can have between 300,000 ~ 500,000 sessions a day. The application has about 10 application constants. If Application Items were used for these constants it would result in
10 x 500,000 (5 Million) unnecessary extra writes occurring every day to store values that don't change.
I also found that there were some places "regular" substitution strings did not work (using an Application Item or Page Item) but worked for Text Messages. These were extreme edge cases.
When this technique is used you need to ensure that you have a good release process. I.e. right after the APEX application is installed/updated in each environments the text messages need to be updated as they will contain the "original" text message from the application export.