How Do I Translate Data That Supports List of Values in Oracle APEX

 

Introduction
Translating data that supports List of Values (LOVs) in Oracle APEX is vital for creating user-friendly, multilingual applications. LOVs are commonly used for dropdowns, select lists, and popup LOVs to present users with predefined choices. Ensuring these values are translated correctly allows users from different language backgrounds to interact with the application naturally and intuitively, enhancing usability and accessibility.

Translating data that supports List of Values (LOVs) in Oracle APEX is an important step to ensure dropdowns, select lists, and popup LOVs display meaningful options in the user’s preferred language. Since LOVs typically provide key-value pairs for user selection, the display values must be localized to deliver a smooth, multilingual user experience.

Here is a detailed guide on how to translate LOV data in Oracle APEX:

1. Identify the LOV Source
   LOVs in APEX can be static (hardcoded values) or dynamic (SQL queries pulling from tables or views). Understanding the source is essential because the translation approach varies accordingly.

2. Translate Static LOVs
   For static LOVs defined directly in the application:

   • Open the LOV definition in Shared Components > List of Values.

   • Static LOVs are defined as Display Value;Return Value pairs. You will need to create language-specific versions of these lists by using substitution strings or application items that dynamically switch between translated sets.

   • Alternatively, use APEX’s translation repository to translate the static display values by including them in the XLIFF export/import process.

3. Translate Dynamic LOVs (SQL-based)
   When LOVs are populated by SQL queries, the translation usually happens at the data source level:

   • Add language columns to your LOV tables, for example, DISPLAY_VALUE_EN, DISPLAY_VALUE_FR, etc.

   • Modify the SQL query to select the display column matching the current session language. This can be done using APEX built-in substitution strings or application items. For example:

   SELECT CASE :APP_LANGUAGE
            WHEN 'FR' THEN display_value_fr
            WHEN 'EN' THEN display_value_en
            ELSE display_value_en
          END AS display_value,
          return_value
   FROM lov_table
   ORDER BY display_value
   

4. Use Application Items or Session Variables to Control Language
   Create an application item (e.g., APP_LANGUAGE) that holds the current user language. This variable is used in LOV SQL queries to determine which language column to display.

5. Include LOV Values in Translation Files
   If LOV display values are stored in the application metadata or text messages, ensure they are included when you export XLIFF files for translation. This enables translators to provide the correct localized text.

6. Test Language Switching
   Run the application, change the session language, and verify that LOVs display the appropriate translated values. Check all LOVs across different pages for consistency.

7. Consider Using Shared LOVs
   To simplify maintenance, use shared LOV definitions reused across multiple pages. This makes translation management easier, as you only update a single LOV definition.

8. Handle Multi-language LOVs in Reports and Forms
   When LOVs are used in forms or reports, ensure that the translated display values appear correctly in both the input controls and any reports or grids showing selected values.

By carefully designing LOVs to support multiple languages—whether static or dynamic—you enable Oracle APEX applications to provide a smooth, localized user experience. This approach leverages database structure, session variables, and APEX translation capabilities to ensure all users see LOV choices in their preferred language, increasing usability and adoption globally.

 

When developing multilingual applications in Oracle APEX, you may need to translate dynamic data such as List of Values (LOVs) that are generated from database queries. This ensures that dropdowns, radio groups, and other selection components display options in the user's preferred language.

Understanding Dynamic Translations for LOVs

LOVs can be based on:

• Static Values – Manually defined values in APEX

• Dynamic Queries – Data retrieved from a database table

For static LOVs, translations are managed directly in the APEX interface. However, for dynamic LOVs, you must store translations in a database table and retrieve the correct language version dynamically.

Step 1: Creating a Translation Table

To support multiple languages, create a table that stores translations for LOV values.

CREATE TABLE LOV_TRANSLATIONS (

    LOV_ID          NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,

    LOV_CODE        VARCHAR2(50),  -- Identifier for the LOV value

    DISPLAY_TEXT    VARCHAR2(255), -- Translated text

    LANGUAGE_CODE   VARCHAR2(10)   -- Language identifier (e.g., 'en', 'fr', 'es')

);

This table will store different translations for each LOV option based on the language code.

Step 2: Inserting Translations

Insert translations for LOV values in multiple languages.

INSERT INTO LOV_TRANSLATIONS (LOV_CODE, DISPLAY_TEXT, LANGUAGE_CODE) VALUES ('STATUS_NEW', 'New', 'en');

INSERT INTO LOV_TRANSLATIONS (LOV_CODE, DISPLAY_TEXT, LANGUAGE_CODE) VALUES ('STATUS_NEW', 'Nuevo', 'es');

INSERT INTO LOV_TRANSLATIONS (LOV_CODE, DISPLAY_TEXT, LANGUAGE_CODE) VALUES ('STATUS_NEW', 'Nouveau', 'fr');

INSERT INTO LOV_TRANSLATIONS (LOV_CODE, DISPLAY_TEXT, LANGUAGE_CODE) VALUES ('STATUS_CLOSED', 'Closed', 'en');

INSERT INTO LOV_TRANSLATIONS (LOV_CODE, DISPLAY_TEXT, LANGUAGE_CODE) VALUES ('STATUS_CLOSED', 'Cerrado', 'es');

INSERT INTO LOV_TRANSLATIONS (LOV_CODE, DISPLAY_TEXT, LANGUAGE_CODE) VALUES ('STATUS_CLOSED', 'Fermé', 'fr');

COMMIT;

Now, the STATUS_NEW and STATUS_CLOSED values have translations in English, Spanish, and French.

Step 3: Creating a Dynamic LOV Query

To fetch the LOV values dynamically based on the user’s session language, create a SQL query for the LOV.

SELECT DISPLAY_TEXT AS DISPLAY_VALUE, LOV_CODE AS RETURN_VALUE

FROM LOV_TRANSLATIONS

WHERE LANGUAGE_CODE = NVL(:APP_LANGUAGE, 'en')

ORDER BY DISPLAY_TEXT;

• :APP_LANGUAGE is a session variable that stores the user’s selected language.

• The NVL function ensures that if no language is set, English ('en') is used as the default.

Step 4: Setting Up the LOV in APEX

1. Navigate to Shared Components > List of Values.

2. Click Create and choose Dynamic List of Values.

3. Enter the SQL query from Step 3.

4. Save and assign the LOV to a Select List, Radio Group, or Checkbox Group item.

When the application runs, the LOV values will be displayed in the user's selected language.

Step 5: Managing the Session Language

To ensure that APEX recognizes the user’s language, set the session language using:

BEGIN

    APEX_UTIL.SET_SESSION_LANG(:P0_LANGUAGE);

END;

• :P0_LANGUAGE is a page item that stores the selected language.

• This should be triggered via a Dynamic Action when the user selects a language.

Step 6: Testing Translations

1. Run the application.

2. Change the language selection (if applicable).

3. Verify that the LOV values update based on the selected language.

Best Practices for Translating LOVs

• Store translations in a dedicated table rather than hardcoding them.

• Use session variables to dynamically filter LOV values based on language.

• Ensure indexes are applied to improve LOV query performance.

• Keep language codes consistent across the application.

• Regularly update translations when adding new LOV values.

By implementing these steps, developers can provide a seamless multilingual experience in Oracle APEX applications, ensuring that LOV values dynamically adjust to each user’s language preference.

Conclusion
Properly translating LOV data in Oracle APEX guarantees that all users can understand and select from lists in their native language, improving the overall application experience. By using APEX’s translation tools and best practices for managing LOV metadata and associated labels, developers can maintain consistency and clarity across all supported languages, making applications truly global-ready.