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 APP_... or 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 APP_NAME

  • Set for each session based on a value in a lookup table

  • In dev you set to Demo Dev, test: Demo Test, and production: Demo

This means that the following would occur for every session:

  • Computation or process on new instance to load value from lookup table

  • A new row is created in apex_application_items for 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

Click 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;
    l_translation_entry_id apex_application_translations.translation_entry_id%type;

    from apex_application_translations aat
    where 1=1
        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 
        apex_session.create_session (
            p_app_id => c_apex_app_id,
            p_page_id => 1,
            p_username => 'dummy'
    end if;

    -- Set text message:
    apex_lang.update_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:

Closing Remarks

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.