Translating Applications for Multibyte Languages in Oracle APEX

Introduction
Translating applications for multibyte languages in Oracle APEX is essential when building solutions for users who read and write in languages such as Chinese, Japanese, Korean, Arabic, or Hindi. These languages require special handling because their characters take more than one byte and often have unique display and formatting needs. Oracle APEX fully supports multibyte character sets, allowing developers to create localized applications that are both accurate and culturally appropriate.

Translating applications for multibyte languages in Oracle APEX involves several key steps to ensure that your application can correctly display and manage complex characters such as those used in Chinese, Japanese, Korean, Arabic, and other non-Latin scripts. Oracle APEX fully supports Unicode, which means it can handle multibyte characters natively, but you must follow certain practices to make sure translations appear correctly and consistently.

Step 1: Confirm Character Set Support

First, ensure your Oracle Database is using a character set that supports multibyte characters, such as AL32UTF8. You can check this with the following SQL command:

SELECT value FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';

If your database does not support Unicode, multibyte characters may display incorrectly or be truncated.

Step 2: Set the Primary Language

In APEX, go to Shared Components > Globalization Attributes and set the Primary Language of the application to the base language (e.g., English). This will be used as the source for translations.

Step 3: Add Target Multibyte Language

Navigate to Shared Components > Language and click Create to add a new language, such as:

• zh-cn for Simplified Chinese

• ja for Japanese

• ko for Korean

• ar for Arabic

Select the appropriate language code and give it a description. This defines the translation target.

Step 4: Export Translation Text

From Shared Components > Translate Application, select the target language and click Export XLIFF File. The XLIFF file is an XML format that contains all text labels, messages, prompts, and button captions from your application.

Download the file and open it in a text editor or translation tool that supports Unicode. Replace the <target> values with translated text using the appropriate multibyte characters. Save the file in UTF-8 format to preserve the encoding.

Step 5: Import Translated XLIFF

Return to APEX and go to Translate Application again. Use the Import XLIFF File function to upload your translated file. APEX will store the translations and associate them with the defined language code.

Step 6: Enable Language Switching

To allow users to switch languages:

• Create a page item (e.g., P0_LANGUAGE) with a list of available languages.

• Set Application Language Derived From to that item (under Globalization Attributes).

• Add a dynamic action to submit and redirect when the language is changed.

Use a Before Header process if you need to manually set the language in PL/SQL:

BEGIN
  APEX_UTIL.SET_SESSION_LANG(:P0_LANGUAGE);
END;

Step 7: Test Layout and Alignment

Multibyte languages such as Arabic (right-to-left) or Chinese (vertically dense) may affect layout spacing, font sizing, and alignment. Make sure to:

• Use web fonts that support the target language’s script

• Enable right-to-left support in page templates if needed

• Test label widths, alignment, and line wrapping

Step 8: Use Text Messages for Dynamic Content

For dynamically generated text, use Shared Components > Text Messages. These allow you to define key-value pairs for translated content:

-- Retrieve translated message
:PX_LABEL := APEX_LANG.MESSAGE('ORDER_SUCCESS');

Each message can have multiple language-specific versions.

By following these steps, Oracle APEX ensures your application displays multibyte characters correctly, supports multiple languages, and allows users to interact with the UI in their preferred script. Proper testing and Unicode handling are critical to maintaining data integrity and visual clarity across different language environments.

Oracle APEX supports building multilingual applications, including those that use multibyte languages such as Chinese, Japanese, and Korean. To ensure proper translation and display of these languages, it is essential to configure the database correctly and use APEX's built-in globalization features.

Configuring the Database for Multibyte Languages

Oracle databases store text using different character sets. To support multiple languages, including multibyte languages, the database should be configured with a Unicode character set such as AL32UTF8.

1. Check the Current Character Set
   Run the following SQL query to determine the current database character set:

SELECT parameter, value 

FROM nls_database_parameters 

WHERE parameter = 'NLS_CHARACTERSET';

If the result is not AL32UTF8, the database may need to be migrated to a Unicode-compatible character set.

2. Configure the Database for Unicode
   If the database does not use AL32UTF8, consider migrating it using Oracle Database Migration Assistant for Unicode or Data Pump Export/Import.

3. Ensure Proper NLS Settings
   Set the session-level NLS (National Language Support) parameters to match the desired language:

ALTER SESSION SET NLS_LANGUAGE = 'JAPANESE';

ALTER SESSION SET NLS_TERRITORY = 'JAPAN';

ALTER SESSION SET NLS_CHARACTERSET = 'AL32UTF8';

Translating Applications in Oracle APEX

Once the database supports multibyte characters, you can configure your APEX application for multilingual support.

1. Enable Globalization in APEX

• Go to Shared Components.

• Select Globalization Attributes.

• Set the Primary Language (e.g., zh-CN for Simplified Chinese).

2. Use XLIFF Translation Files

• Export the translation text as an XLIFF file.

• Translate the strings in the XLIFF file using a text editor or translation software.

• Reimport the translated XLIFF file into APEX.

3. Use the LANG Function
   The LANG function helps dynamically adjust language settings based on the user's session language:

SELECT LANG FROM APEX_APPLICATION_TRANSLATIONS WHERE LANGUAGE_CODE = :SESSION_LANGUAGE;

4. Store Multilingual Data Properly
   When storing multilingual content in the database, ensure that columns use NVARCHAR2 or CLOB data types to handle multibyte characters correctly.

5. Apply Language-Specific Formatting
   Use format masks to display dates, numbers, and currency based on language settings:

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD';

Testing Multibyte Language Support

1. Switch User Language Preferences

• In APEX User Preferences, change the Language to a multibyte language (e.g., Chinese).

• Verify that text and UI elements render correctly.

2. Test Data Entry and Retrieval

• Insert test records with Chinese, Japanese, or Korean characters.

• Run queries to confirm that multibyte characters are stored and retrieved correctly.

3. Check Browser and OS Language Settings

• Ensure the browser and operating system are configured to display multibyte characters correctly.

Best Practices for Multibyte Language Support

• Use AL32UTF8 as the database character set for full multilingual support.

• Store multilingual text in NVARCHAR2 or CLOB columns.

• Leverage APEX’s Translation Repository to manage multiple language versions efficiently.

• Apply format masks for locale-specific date, number, and currency formatting.

• Always test your application with different languages and user settings to ensure proper display.

By following these steps, you can effectively translate and manage Oracle APEX applications that support multibyte languages like Chinese, Japanese, and Korean.

Conclusion
By properly translating your Oracle APEX application for multibyte languages, you ensure accessibility and clarity for a wider audience. Leveraging Unicode support, translation repositories, and the APEX translation tools, you can deliver multilingual applications that function seamlessly across different languages and regions. With the right setup, your application can support complex scripts while maintaining performance, readability, and consistency.

 

How do I Apply Format Masks to Items in Oracle APEX

 

Introduction
Applying format masks to items in Oracle APEX allows you to control how data such as dates, numbers, and currencies are displayed to users. Format masks improve readability, ensure consistency across your application, and help users understand the expected input or output. Whether you're working with a date picker, a numeric field, or a display-only item, using the right format mask enhances both usability and presentation.

 Applying format masks to items in Oracle APEX is an essential step in controlling how data is displayed to users. Format masks do not change the stored data; they simply change how that data appears on screen. This is especially useful for dates, times, numbers, and currency values. Oracle APEX provides multiple places where you can apply format masks depending on the item type and how the data is presented.

To apply a format mask in Oracle APEX, follow these detailed steps:

1. Open Page Designer
   In APEX App Builder, go to your application and open the page containing the item you want to format.

2. Select the Item
   Click on the item (e.g., a date picker, text field, number field, or display-only item) in the Rendering tree or Layout view.

3. Find the Format Mask Property
   In the Property Editor, scroll down to the Appearance section. There you’ll find a field labeled Format Mask.

4. Enter the Format Mask
   Enter the appropriate format mask string based on the item’s data type. Some common examples:

   • Date Format Masks

     • DD-MON-YYYY → 05-JUL-2025

     • MM/DD/YYYY → 07/05/2025

     • YYYY-MM-DD HH24:MI:SS → 2025-07-05 13:45:00
       These are based on Oracle’s TO_CHAR format models.

   • Number Format Masks

     • 999,999.99 → 12,345.67

     • 000000 → 000123

     • FM9990.00 → removes leading/trailing spaces and shows two decimals.

   • Currency Format Masks

     • L999G999D00 → $1,234.56 (uses local currency symbol)

     • FML999G999D00 → no space before symbol, tight formatting

5. Display-Only Items
   If your item is set as Display Only, you can still apply a format mask. Just make sure Escape Special Characters is set to No if your format includes currency symbols or special characters.

6. Interactive Reports or Grids
   If you're working with an Interactive Report, Classic Report, or Interactive Grid, you can apply format masks to columns as well:

   • Open the Region (e.g., your report).

   • Locate the column under Columns.

   • In the Column Attributes, scroll to the Appearance section.

   • Enter your desired format mask under Number/Date Format.

7. Dynamic Formatting via PL/SQL or JavaScript (Optional)
   In some cases, you may want to control formatting using PL/SQL or JavaScript. For example, you could format a date using a PL/SQL process:

   :P1_FORMATTED_DATE := TO_CHAR(:P1_DATE, 'DD/MM/YYYY');
   

   Or use JavaScript with the Intl.NumberFormat or toLocaleString() functions to handle locale-based formats on the client side.

8. Preview and Test
   Run the application, enter or display values in the formatted item, and verify that they appear exactly as intended.

By using format masks in Oracle APEX, you can ensure that users always see data in a clear, readable, and standardized format. Whether you're localizing for global users or aligning to business rules, format masks give you full control over presentation without modifying your data structure.

 

Oracle APEX applies globalization settings automatically when rendering each page. These settings can affect how numbers, dates, and other formatted data appear based on the user’s session language and territory. To ensure consistency in data presentation, format masks can be applied to items such as text fields, number fields, and date pickers.

 

What Are Format Masks?

A format mask is a pattern that controls how data is displayed or entered in an item. It ensures that values are formatted correctly based on locale settings and prevents inconsistent data entry. Format masks are particularly useful for:

• Dates: Displaying dates in a consistent format (e.g., DD-MON-YYYY).

• Numbers: Controlling decimal and thousand separators (999G999D99).

• Currency: Formatting monetary values ($999,999.99).

• Text: Converting input to uppercase (UPPER).

 

How to Apply a Format Mask to an Item

1. Open the Page Designer

• Navigate to App Builder.

• Select the application and page where the item is located.

2. Select the Item

• In Page Designer, find the item that needs a format mask.

• Click on the item to open its properties.

3. Apply a Format Mask

• In the Property Editor, find the Format Mask property.

• Enter the appropriate format mask based on the data type.

4. Save and Run the Page

• Click Save and Run to test the format mask.

 

Common Format Masks in Oracle APEX

Date Format Masks

Format Mask	Description	Example Output
DD-MON-YYYY	Day, abbreviated month, and year	15-MAR-2025
YYYY-MM-DD	ISO standard format	2025-03-15
MM/DD/YYYY	U.S. style date format	03/15/2025
DD/MM/YYYY HH24:MI	Date with 24-hour time	15/03/2025 14:30

Number Format Masks

Format Mask	Description	Example Output
999G999D99	Standard number format	1,234.56
0000	Leading zeros	0045
99.99	Fixed decimal places	12.30
99999PR	Negative values in parentheses	(1000)

Currency Format Masks

Format Mask	Description	Example Output
$999,999.99	U.S. currency format	$1,234.56
L999G999D99	Uses session locale	€1.234,56 (for German locale)
99999.99 C	Currency symbol at the end	1234.56 USD

Text Formatting

Format Mask	Description	Example Output
UPPER	Converts input to uppercase	HELLO
LOWER	Converts input to lowercase	hello
INITCAP	Capitalizes first letter of each word	Hello World

 

Overriding Default Globalization Behavior

If Oracle APEX is applying unwanted automatic formatting based on globalization settings, use explicit format masks for date and number items.

Example: Force a Specific Date Format

If a session is set to a European locale (DD.MM.YYYY), but you need MM/DD/YYYY, apply the format mask explicitly:

1. Open Page Designer.

2. Select the date item.

3. Set the Format Mask to MM/DD/YYYY.

4. Save and run the application.

This ensures that dates always appear in the expected format, regardless of session settings.

 

Best Practices for Using Format Masks

• Use consistent formats across the application to avoid confusion.

• Match format masks with database column formats to prevent conversion errors.

• Consider user locale settings when defining format masks for currency and numbers.

• Test different APEX session languages and territories to ensure correct formatting.

Applying format masks correctly in Oracle APEX enhances user experience, maintains data integrity, and ensures that applications display information in a predictable and professional manner.

Conclusion
Format masks in Oracle APEX offer a simple yet powerful way to tailor the display of item values without altering the underlying data. By applying the appropriate format masks to items, you ensure your application communicates information clearly and aligns with regional or business-specific display standards. It's a key part of creating polished, professional, and user-friendly APEX applications.