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.

How Do I Translate Messages in Oracle APEX

 

Introduction
Translating messages in Oracle APEX is a crucial step in building applications that effectively communicate with users in their preferred language. Messages include validation errors, success notifications, prompts, and other dynamic content that guide user interactions. Proper translation of these messages ensures clarity, improves usability, and creates a more inclusive experience for a global audience.

Translating messages in Oracle APEX is essential to provide users with clear, localized feedback such as validation errors, success notifications, prompts, and system messages. Oracle APEX offers a structured approach to translating these messages using the Text Messages feature and the application translation framework.

Here is a detailed process on how to translate messages in Oracle APEX:

1. Access Text Messages Repository
   Navigate to Shared Components > Text Messages in your application. This repository contains key-value pairs where each key represents a message name, and the value is the message text in the primary language.

2. Create or Identify Messages to Translate
   You can either create new messages for your application’s dynamic content or use existing ones. Messages may include:

   • Validation error texts

   • Custom informational messages

   • Confirmation prompts

3. Assign Language-Specific Translations
   Oracle APEX allows you to define translations for each message by language. When you create or edit a text message, you can add translations for the languages supported by your application.

   • Select the message key.

   • Add or edit translations by choosing the language code (e.g., fr for French).

   • Enter the translated message text.

4. Use Messages in Your Application
   To display translated messages dynamically, use the APEX_LANG.MESSAGE API in your PL/SQL code or processes. For example:

   DECLARE
     l_msg VARCHAR2(4000);
   BEGIN
     l_msg := APEX_LANG.MESSAGE('MY_CUSTOM_ERROR');
     APEX_ERROR.ADD_ERROR(
       p_message => l_msg,
       p_display_location => apex_error.c_inline_in_notification);
   END;
   

   This approach retrieves the correct translation for the current session language automatically.

5. Export and Import Translations
   To manage translations efficiently, use the Translate Application feature under Shared Components:

   • Export the XLIFF file for the target language, which includes text messages.

   • Translate the messages in the XLIFF file using a supported tool or text editor.

   • Import the translated XLIFF back into APEX, which updates all message translations in bulk.

6. Test Translated Messages
   Change the application’s session language to verify that messages appear correctly in different languages. Ensure placeholders and substitution variables within messages remain intact and render properly.

7. Maintain Consistency
   For consistent translations across the application, standardize message keys and their usage. Reuse text messages for common prompts and errors to avoid duplication and ensure uniformity.

By leveraging Oracle APEX’s Text Messages and translation framework, you can deliver precise, culturally appropriate feedback to users. This enhances the overall user experience and makes your application truly multilingual and accessible worldwide.

Oracle APEX allows developers to translate messages within an application to support multiple languages. These messages include system-generated notifications, error messages, success messages, and custom messages defined by the developer. Translating messages ensures that users receive information in their preferred language.

Understanding Translatable Messages

Messages in Oracle APEX that may require translation include:

• System Messages – Standard error messages and notifications generated by APEX

• Validation Messages – Custom messages displayed when form validations fail

• Success Messages – Confirmation messages after successful actions

• Warning or Informational Messages – Alerts triggered by Dynamic Actions or PL/SQL processes

• Custom Messages – Messages stored in APEX_TEXT_MESSAGE for dynamic retrieval

Oracle APEX provides Globalization and Translation tools to translate these messages into multiple languages.

Step 1: Creating a Language Mapping

Before translating messages, define the primary and target languages for the application.

1. Open the APEX application.

2. Navigate to Shared Components > Globalization > Translate Application.

3. Click Create Language Mapping.

4. Select the Primary Language and the Target Language.

5. Click Create to establish the mapping.

This allows APEX to create and manage translations for the selected languages.

Step 2: Seeding Translations

Seeding extracts all translatable text, including system and custom messages.

1. Go to Shared Components > Globalization > Translate Application.

2. Click Seed Translations.

3. Select the Application ID and the Target Language.

4. Click Seed Translations to populate the repository.

After seeding, APEX extracts all messages into the Translation Repository.

Step 3: Exporting the XLIFF File

APEX stores translatable text in XLIFF (XML Localization Interchange File Format) files.

1. Navigate to Shared Components > Globalization > Translate Application.

2. Click Export XLIFF File.

3. Choose the Target Language and download the file.

This XLIFF file contains all messages requiring translation, structured as:

<trans-unit id="VALIDATION_ERROR">

    <source>Please enter a valid email address.</source>

    <target></target>

</trans-unit>

<trans-unit id="SUCCESS_MESSAGE">

    <source>Record saved successfully.</source>

    <target></target>

</trans-unit>

Step 4: Translating Messages in the XLIFF File

Edit the XLIFF file and provide translations within the <target> tags.

Example:

<trans-unit id="VALIDATION_ERROR">

    <source>Please enter a valid email address.</source>

    <target>Por favor ingrese un correo electrónico válido.</target>

</trans-unit>

<trans-unit id="SUCCESS_MESSAGE">

    <source>Record saved successfully.</source>

    <target>Registro guardado exitosamente.</target>

</trans-unit>

Once all messages are translated, save the file.

Step 5: Importing the Translated XLIFF File

1. Go to Shared Components > Globalization > Translate Application.

2. Click Apply XLIFF Document.

3. Select the Target Language and upload the file.

4. Click Apply to import translations.

Step 6: Publishing the Translated Messages

1. Go to Shared Components > Globalization > Translate Application.

2. Click Publish Application.

3. Select the Target Language and confirm.

The translated messages will now appear when users switch to the target language.

Step 7: Using APEX_TEXT_MESSAGE for Custom Messages

For dynamic messages stored in the database, APEX provides the APEX_TEXT_MESSAGE table.

Creating a Translatable Message:

BEGIN

    APEX_LANG.CREATE_MESSAGE (

        p_name      => 'WELCOME_MESSAGE',

        p_message   => 'Welcome to the application!',

        p_workspace => 'MY_WORKSPACE'

    );

END;

Retrieving a Message in PL/SQL

DECLARE

    v_message VARCHAR2(4000);

BEGIN

    v_message := APEX_LANG.MESSAGE('WELCOME_MESSAGE');

    DBMS_OUTPUT.PUT_LINE(v_message);

END;

When the application is translated, APEX_LANG.MESSAGE automatically retrieves the correct language version.

Step 8: Testing Translated Messages

To verify that messages appear correctly:

1. Set the Session Language to the Target Language.

2. Navigate through the application and trigger messages (e.g., form validation errors, success messages).

3. Ensure that all messages are displayed in the selected language.

Best Practices for Translating Messages in APEX

• Regularly seed translations after making UI changes to capture new messages.

• Use consistent terminology across all translations.

• Maintain XLIFF files for future updates.

• Test translations in different browsers and user sessions.

• Use APEX_LANG.MESSAGE for dynamic messages in PL/SQL code.

By following these steps, developers can effectively translate messages in Oracle APEX, ensuring a smooth multilingual experience for users.

 Conclusion
By translating messages in Oracle APEX, you enable your application to provide meaningful feedback to users across different languages and cultures. Utilizing APEX’s built-in translation tools and text message repositories helps maintain consistency and accuracy throughout your application. This careful attention to message translation enhances user satisfaction and supports the development of truly multilingual applications.