As of its first release, APEX has supported globalized applications. Since, by definition, a web application can be accessed from anywhere, globalization is an important feature to consider. APEX and the Oracle Database provide the functionality to help you build applications that can display data based on the location of the end user.

In this chapter, I will cover two major aspects of globalization: translation and localization. Translation allows you to run applications in multiple languages without having to duplicate the logic. Localization is used to format and display the content in the application based on where the end user is located. Even if your applications are internal or intranet applications and do not need to be translated, the globalization parameters may still be useful for date and number formatting.

The first part of this chapter will be about translations. It will show you how to install the builder in different languages and how to translate an application, along with the configuration required to do so. Translation also covers how to implement the mechanism to switch from one language to another while executing an application. The second part of the chapter will cover localization, including time zones and date formats.

As you probably know by now, APEX is built with PL/SQL and runs directly in the database, allowing for the use of all language specifics available in the Oracle Database. For example, you could change the time zone or NLS_SORT using either an alter session or, in certain cases, built-in APEX utilities. Using the Oracle language capabilities allows you to build applications in 132 supported languages while the APEX builder is available in these 10 languages: English, German, Spanish, French, Italian, Japanese, Korean, Brazilian Portuguese, Simplified Chinese, and Traditional Chinese.

Using the Builder in Other Languages

When APEX is first installed, English is the default language. To be able to use the builder in other languages, you must install them. Note that uploading new languages for the builder will also translate action menus and error messages in interactive reports that will be used by the translated applications.

You can find the scripts required to load any of the nine other languages available for the builder in the APEX ZIP file within the builder folder, as shown in Figure 6-1. This directory contains one subdirectory for each of the available languages.

Within each directory, a language-loading script is identified by the language code (for example, load_de.sql or load_ja.sql).

To load the language, you must first set the NLS_LANG environment variable at the operating system level. Check the documentation for the proper command for your operating system (in this example I am running on Windows). Note that this setting is always the same for every language being loaded.

set NLS_LANG=AMERICAN_AMERICA.AL32UTF8

Then, run the appropriate load_lang.sql script in sqlplus using sqlplus / as sysdba, where lang is the specific language (for example, load_de.sql for German or load_ja.sql for Japanese).

ALTER SESSION SET CURRENT_SCHEMA = APEX_050000;
@load_lang.sql

Note that you will have to load the other languages every time you upgrade APEX.

You can remove a language by running the corresponding unload_lang.sql script.

Once the language is loaded, you can find the option to use this language at the bottom of the login region on the APEX login page, as shown in Figure 6-2.

The option to change the language of the builder is also available at the bottom left of the APEX builder home page, as Figure 6-3 shows.

There is a report available in the Administrative APEX workspace (internal) that shows which languages have been loaded for the builder. To view this report, log in as the administrator of the internal workspace and select the option Installed Translations under Manage Instance.

Translating Applications

Some systems are required to be bilingual or even multilingual, depending on where and how they are used. Applications are becoming more commonly available on the Internet, and users may prefer to access them in their own language. For example, Google, Hotmail, and even the iPhone are multilingual because the business market is literally worldwide.

The translation process has two parts: first the configuration of the parameters and then the application translation itself. Applications have to be translated and published every time changes are made to the primary application so that they can be applied to all the languages.

The following paragraphs will explain the different steps for translating your application. In short, they are as follows:

1. Configure the globalization attributes.
2. Map your primary application ID with the translated applications’ IDs.
3. Seed the translated text to the APEX translation repository.
4. Translate your application.
5. Publish the translated applications.

The first step required to translate an application is to configure the globalization attributes. They are defined in the Shared Components section of the application (see Figure 6-4).

This section is also accessible via the Edit Application Properties dialog and the Globalization tab.

The first parameter to set is the default language of the primary application (the current application). To do so, choose the language in the Application Primary Language select list, as shown in Figure 6-5. The Application Primary Language setting is extremely important since all functionalities will be localized in the chosen language. This parameter should be set even when translation is not required. By setting this parameter, sorting, CSV exports, interactive reports, and APEX messages will be translated and localized for the primary application. The default value of the Application Primary Language setting is always English (United States) (en-us).

The way APEX supports multilingual applications is by creating a copy of the primary application for each translated version. At runtime, this is transparent to the end user. The developer makes changes to the primary application and then publishes these to the translated versions. This will be explained in more detail later in this chapter.

Application Language Derived From

Once the primary language is defined in the globalization attributes, the Application Language Derived From parameter must be set, as shown in Figure 6-6. This will determine how APEX decides which language will be used at runtime. Using this parameter, APEX will execute the corresponding translated application.

The different options to derive the language from are as follows:

• NO NLS (Application not translated): Choose this if no translations are required for the application.
• Application Primary Language: This uses the primary language value as the application language. This is useful for developers to run tests in a specific language. For example, if the primary application is en-us (English US) and a fr-ca (French Canada) application is mapped, you could change the primary language to fr-ca and use an Application Language Derived From setting of Application Primary Language to run the fr-ca application. It is important not to forget to change the primary language back to its original setting once testing is complete. When the primary language is changed, the interactive report menus and the APEX error messages will both be displayed in that language. For this parameter, you can select any of the 132 available languages.
• Browser (use browser language preference): When using this option, APEX will check the browser language and execute the mapped application in the same language code. For example, if the browser is set to fr-ca, APEX will run the translated fr-ca application if it exists. If, let’s say, no fr-ca application exists, it will use the primary application language.
• Application Preference (use FSP_LANGUAGE_PREFERENCE): This setting uses an APEX preference to determine which language to use. Preferences are programmatically set using the APEX_UTIL.SET_PREFERENCE procedure and are linked to an application user. To check the user preference, you can use the APEX_UTIL.GET_PREFERENCE API.

  APEX_UTIL.GET_PREFERENCE (
  p_preference  IN    VARCHAR2 DEFAULT NULL,
  p_user        IN    VARCHAR2 DEFAULT V(’USER’))
  RETURN VARCHAR2;

• Item Preference (use item containing preference): This setting can be confusing because of its use of an application item called FSP_LANGUAGE_PREFERENCE and not a preference. When you use this option, an application item called FSP_LANGUAGE_PREFERENCE has to be created in the Shared Components area. Afterward, to set the language of the application, a simple change of the value of FSP_LANGUAGE_PREFERENCE is needed. For example, in PL/SQL you could write the following:

  :FSP_LANGUAGE_PREFERENCE := ’fr-ca’ ;

• Once the value is changed, in the next page rendering, APEX will check that value and run the corresponding translated application with the corresponding language.
• Deriving the language using this method can sometimes be tricky when accessing a specific page in a specific language via a direct external URL.
• For example, if you wanted to access page 200 in fr-ca with a primary application in en-ca and while setting FSP_LANGUAGE_PREFERENCE, the URL would be as follows:

  http://domain:port/pls/apex/f?p=999:200::::FSP_LANGUAGE_PREFERENCE:fr-ca

• In that case, APEX will start to execute the application ID 999, page 200, in the default primary language en-ca. Then, it will set the application item FSP_LANGUAGE_PREFERENCE to fr-ca as defined in the URL.
• The en-ca application, which is the primary language, will be displayed, since the application item is set in the URL and will be processed during the page rendering. The problem here is that even if you want to see the fr-ca as specified in the URL, the first page view will still show the en-ca version. Only subsequent pages will be in fr-ca.
• One way to be able to see the fr-ca version on the first call would be to always go to a page that sets the application item and branches back. In this page, FSP_LANGUAGE_PREFERENCE would be set, followed by a redirection to page 200, in this example in order to then display the right application in fr-ca. The language switch will then be transparent to the end user.
• Another option to change the language via the URL is the P_LANG parameter. This can also be used to set the language when deriving the language with the FSP_LANGUAGE_PREFERENCE item, although this will not change the value of the FSP_LANGUAGE_PREFERENCE application item. Using P_LANG in the URL will make all subsequent page views in that language.

  http://.../pls/apex/f?p=999 :1 :0 :::::&p_lang=fr-ca

• Session: The last option for Application Language Derived From is Session. When this option is selected, APEX will determine the application language from the session setting.
• The session is set by using the API APEX_UTIL.SET_SESSION_LANG or by using the P_LANG parameter in the URL. Here’s an example:

  http://.../pls/apex/f?p=999 :1 :0 :::::&p_lang=fr-ca

• To get the session language value, use the API APEX_UTIL.GET_SESSION_LANG or use the variable BROWSER_LANGUAGE. To reset the session language value, use APEX_UTIL.RESET_SESSION_LANG. Those APIs can be used in application processes or in database packages and procedures.

Mapping

Once you have set the globalization attributes, the next step is to map your primary application to the application IDs that will be used by the translated applications.

Translations have to be mapped to an application ID. Those IDs will be used to generate the applications that APEX will run when a specific language is required. To do so, go to Translate Application under Globalization in the Shared Components area of the application, and select the first option: “Map primary language application to translated applications.” Click Create. You will get to the application language mapping page, as shown in Figure 6-7.

You can change the mapping when you import the primary application with translations. Also, if the application ID that is mapped to the translated application is already in use when you import, APEX will automatically assign another ID.

Enter the corresponding translated application ID. This ID must be available in the APEX installation. The translation will create a copy of the primary application using this translated application ID and assign it to the selected language.

One way of making sure that the translated application IDs are not already in use is to define a development standard and reserve a range of IDs. For example, all the translated applications could use ID 9001 and up. So, the primary application 543 would be mapped to the translated application 9543. This standard also makes it easier to see that two applications are related when exporting and importing them. Also note that translated application IDs cannot end with 0; an error message will be displayed if that is the case.

Once the application ID is defined, you can enter the language and the image logical directory defined in the web server configuration that the translated application will use. Using this option, all the images used by the translated application will have to be in that directory.

You can create as many mappings as required, one for each language that your primary application is going to be translated into.

CSV Encoding

When exporting the data in CSV format, it is important to make sure that the character set of the application running will be used. Setting Automatic CSV Encoding to Yes, as shown in Figure 6-8, will export the data in the character set of the language of the application that is currently running.

To set Automatic CSV Encoding, go to Globalization Attributes in the primary application’s Shared Components area.

If Automatic CSV Encoding is not set to Yes when running multilingual applications, Excel exports may not display special characters correctly. If it is enabled, the output will then be properly converted to match the localized applications.

Translating an Application

The translation process consists of the following:

• Seeding the translated application
• Translating text either manually or via an XLIFF file
• Publishing the translated application

This process must be done every time you make changes to the primary application, before they will appear in the translated applications. Translating a primary application creates a new translated application that is exportable/importable but not modifiable.

All the following steps are in the Shared Components area’s Translate Application section.

Seed Translatable Text to Translation Repository

The APEX main database schema (APEX_050000 for APEX 5.0) contains a set of tables and views used for translation that I call the translation repository. These tables are used to store the original values from the primary application and the translations that will be used to generate the translated applications.

The seeding process extracts the translatable content from the primary application and creates entries in the APEX translation repository, which will allow application text to be translated.

To initiate the seeding process, select the language mapping that you want to seed and click Seed (see Figure 6-9).

The data created during the seeding process is part of the APEX translation metadata and can be changed only via the translation process.

To be able to publish the mapped applications, there must be values in the APEX translation repository.

Clicking the option to manually edit the translation repository will show a report containing the rows from the translation repository. The labels, region titles, and other translatable text will be in that report. I will come back to that option later.

Download Translatable Text from Repository to Translation File (XLIFF File)

This step extracts the data from the translation repository and creates an XML Localization Interchange File Format (XLIFF) file that you can save locally on your disk. This format is an official XML format for translations.

To download the XLIFF file on your client locally, select the language to extract and click the Export XLIFF File for Application option (see Figure 6-10). If desired, you can export only the elements requiring translation. This will extract only the text that is new in the translation repository or has been updated in the primary application but not translated in the repository. To be able to translate the text, the Include XLIFF Target Elements check box must be selected since it will be those values that will be updating the repository. It is also possible to download the XLIFF file for a specific page.

The download will generate an .xlf (XLIFF) file containing the source application ID, the target application ID, the source language, and the target language.

It is good practice to export the primary application with translations. This will keep the translation repository data in the export file. This way, you will always have the translations with your primary application. See the “Copying Translations to Other Environments” section.

The XLIFF file contains all translatable elements, including labels, region titles, button names, and more.

If you look at the header of the file, the source and target languages, as well as the generated XLIFF filename, are specified. When using source control software, this indicates when the file was generated.

<?xml version="1.0" encoding="UTF-8"?>
<!--
  ******************
  ** Source     :  122
  ** Source Lang:  en-ca
  ** Target     :  9122
  ** Target Lang:  fr-ca
  ** Filename:     f122_9122_en-ca_fr-ca.xlf
  ** Generated By: ADMIN
  ** Date:         07-FEB-2015 13:15:21
  ******************
 -->
<xliff version="1.0">
<file original="f122_9122_en-ca_fr-ca.xlf" source-language="en-ca" target-language="fr-ca" datatype="html">
<header></header>
<body>
<trans-unit id="S-4-4463613133056390-122">
<source>Projects</source>
<target>Projects</target>
</trans-unit>
<trans-unit id="S-5-1-122">
<source>Projects</source>
<target>Projects</target>
</trans-unit>
<source>Login</source>
<target>Connexion</target>
</trans-unit>

Each trans-unit ID is loaded into the translation repository and mapped with a specific component in the primary application during the seed process. The trans-unit ID is composed of a unit ID, a metadata ID, and the application ID. In the example <trans-unit id="S-4-4463613133056390-122">, S-4 is an internal code that corresponds to the text of a tab, 4463613133056390 is the metadata ID, and 122 is the primary application ID.

Translate Text

In this step, you have to edit the XLIFF file and translate all the text extracted from the primary application.

Editing the XLIFF file is a relatively simple process. Since the XLIFF format is an official XML format for translations, translators will be able to use the XLIFF files with their specialized translation software. This way, the translation could also be done by a third party. But in the majority of cases, a simple text editor can be used to perform the translation as long as the text editor is UTF8 compatible.

To translate the text, you have to change the values of the text contained between the <target> tags. Only changes on the <target> tags will be taken into account when the file is applied to the translation repository.

<trans-unit id="S-5-101-122">
<source>Login</source>
<target>Connexion</target>
</trans-unit>

If the source tags are changed, these updates will not be applied to the primary application; in other words, you cannot use this process to change the original application.

In the translation process, sometimes there may be text in the application HTML templates that requires translation as well. To have the template text included in the XLIFF file, these templates have to be identified as translatable, as shown in Figure 6-11.

Check the Translatable check box in the template properties. This tells APEX to include the content of the template in the translation repository and in the XLIFF file.

There is also a Translatable check box option for list entries that will make the Seed option extract the translatable text.

Apply XLIFF Translation File to Translation Repository

Once the translation of the XLIFF file is complete, it must be uploaded in APEX and applied to the translation repository. This will parse and insert the contents of the file in the APEX translation tables (see Figure 6-12).

First, upload the XLIFF file that you want to apply. You can upload multiple XLIFF files if required. To apply the XLIFF file, check the corresponding file name and click Apply Checked. You can also apply multiple XLIFF files at the same time.

Publish Translated Application

The publish process generates the translated applications. Any time a primary application changes, the translated applications must be published in order for the changes to be replicated across the different languages. This is true even if the changes do not require text translation. Since the publish process generates the mapped application ID, updates to SQL code and styles, as well as most other changes, will not appear in the mapped applications until the publish process is performed.

You can see whether the translated applications are synchronized by going to the Translate area in the globalization section in Shared Components. See Figure 6-13.

The mapped applications are not editable but can be exported. You will see them in the list of available applications in the export options and also in the list of applications in the administration of the workspace.

To execute the translated application, you have to first configure how to derive the translated application, as explained earlier. An easy way of quickly running the translated application would be to change the primary language of the primary application and use the “use primary language derived from” option. (Switching from one language to another is explained later in the chapter in the “Switching Languages” section.)

Manually Translate an Application

You can translate your application directly within APEX through the Shared Components Translate Application menu. This allows you to bypass the XLIFF file export and apply process. After you have manually translated your application, all you need to do is the publish process. You will find the option to manually edit the translation repository at the bottom of the page, in the Translation Utilities region, as shown in Figure 6-14.

This report, shown in Figure 6-15, shows the contents of the translation repository for the applications mapped to your primary application.

The application must first be seeded in order for the data to appear in the translation repository.

When making changes manually, the XLIFF file does not have to be exported and applied because the information was changed directly in the translation repository. All that must be done is to publish the translated application.

This report is useful for making quick changes without going through an XLIFF file. You could do the whole text translation with this option, but making a lot of changes for applications with lots of pages and text is much faster with the XLIFF file method explained earlier.

This report can also be used to visualize the component description related to the translation string. For example, to translate only the text buttons, you could use a filter on the Column Description for Page Button Text. Unfortunately, this information is not available in the XLIFF file.

Translating Data in the Database

For multilingual applications, it is important to plan the multilingual implementation from the start of the project. It is important to remember that not only user interface elements require translation but the underlying data may as well. This will impact the data model. For example, an application that lists departments should display the department names in the language in which the application is running, such as a French name for when the user uses the application in French and an English name for when it is used in English. A multilingual application implies that the application will be used simultaneously in multiple languages.

For example, let’s say you have a bilingual application in French and English. Descriptions must be displayed both in French and in English, based on the language currently being used.

In the database, you could use a table column to store the French description and another for the English description. When displaying the description to the end user, the application should show only the one that corresponds to the active language.

First, add a new column to the departments lookup table that will contain the French department name.

Alter table departments  add (DEPARTMENT_NAME_FR varchar2(30));

Then, to avoid having to check for the language in every single select throughout the application, you will create a view that will do just that. Notice the BROWSER_LANGUAGE variable. At any time, to know the language of the application running, you can check its value from the session state. Even if the name refers to the browser, it really contains the language of the application, not the browser. Also notice that you use the APEX V function. This function returns the values of the user session state.

CREATE OR REPLACE FORCE VIEW DEPARTMENTS_V
(DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID) AS
select DEPARTMENT_ID
,decode(nvl(v(’BROWSER_LANGUAGE’),’en-us’),’fr-ca’,coalesce(DEPARTMENT_NAME_FR,
 DEPARTMENT_NAME),coalesce(DEPARTMENT_NAME, DEPARTMENT_NAME_FR)) DEPARTMENT_NAME
, MANAGER_ID, LOCATION_ID
from DEPARTMENTS;

Note that it is best to always display something as a description rather than nothing at all. This allows for the view to display the department name in the application language, but if it is null, it will display the department name in the other language. Therefore, if the application is run in French and no French department name is found, the English department name will be displayed.

Once the view is created, you can use it in all your lists of values, reports, and any other places that you have to display the department name in the appropriate language.

The advantage of such a view is that if you later want to add a new language or add other columns for translation to your tables, you will reduce the changes to your applications. Basically, adding a new language will require changing views.

Dynamic Translations

In some cases, lists of values or other strings used in queries may not be translatable using the XLIFF file.

The dynamic translations API (APEX_LANG.LANG) checks the language of the running application and returns the translation from an APEX table. The data in the dynamic translations table is populated by the developer of the primary application. The dynamic translations will be part of the application export file; therefore, they will be automatically imported.

To create dynamic translations, you must use the “Manage dynamic translation repository” option, as shown in Figure 6-16. You can find this section at the bottom of the page in Translate Application in the Shared Components area of the Globalization section.

For example, you may decide to always translate Sales to Ventes, Accounting to Comptabilité, and Shipping to Livraison.

The steps are as follows (see Figure 6-17):

1. Select the language to translate to and that matches the mapped translated application language.
2. Enter Sales for Translate From Text.
3. Enter Ventes for Translate To Text.

The Translate From Text value must exactly match the text that will be selected by the List of Values using the APEX_LANG.LANG function. Once all the translations have been entered into the dynamic translations repository, the APEX_LANG.LANG function will return the corresponding translations in the lists of values.

Select APEX_LANG.LANG(Department), deptno
From departments;

Figure 6-18 shows the result of the LOV query in English (en-us).

Figure 6-19 shows the result of the same LOV query in French (fr-ca).

Dynamic translations have to be entered manually through these pages. It would be nice to have the option of using an API to update these values programmatically, but none currently exists. There is a view called APEX_APPLICATION_TRANS_DYNAMIC that lists the content of dynamic translations.

Translating APEX Internal Text

As previously mentioned, when you apply the translations that come with APEX, every one of the internal APEX messages is translated. These messages include the interactive report menus, the help menus, and the report paginations.

If you want to translate into a language that is not part of the ten included APEX languages, you’ll need to translate all the internal messages manually.

You can translate the internal messages by selecting Text Messages in the Shared Components area of the Globalization menu, shown in Figure 6-20. Other options exist in the Shared Components area that link to the same option. Text message translation is also accessible in the Translate Application section when selecting “Create and manage text messages,” “Manage Messages Repository,” “Create and Manage messages callable from PL/SQL,” or “Optionally translate messages which are used by PL/SQL procedures and functions.”

Table 6-1 lists the internal messages used for interactive reports.

As an example, consider a bilingual application with the primary language as English Canadian and a mapped Czech application. To translate the interactive report button Actions, the internal APEX messages must be translated using the APEX text messages. Create an entry in Shared Components Translate Text Messages for the APEXIR_ACTIONS message. The English text message is already defined in APEX; it was loaded at installation. If other languages have been loaded, the internal text messages in those languages are also already defined. They cannot be overridden.

Therefore, all that needs to be done is to create an entry for APEXIR_ACTIONS in cs (Czech), as shown in Figure 6-21.

When the application runs in Czech (cs)—for example, if I change Default Language to Czech (cs)—the correct translation will appear in every interactive report within the application, as Figure 6-22 shows.

Keep in mind that these internal translations must be performed for every distinct primary application. If multiple applications must to be created and translated, a base application with internal messages translated could be created, and a copy can be used for new application developments. When exporting and importing an application, translated text messages and dynamic actions are also exported and imported.

Some of the internal messages use substitution variables, making it important to reuse them in the translated message text. For example, FLOW.VALIDATION_ERROR message text includes the number of errors that occurred; “%0 errors have occurred” could be translated to “%0 erreurs sur la page.”

This feature of translatable messages can also be used for other strings used in applications generated from PL/SQL stored procedures, functions, triggers, packaged procedures, and functions. Simply define a new text message and use the APEX_LANG.MESSAGE function. This is the same function as the dynamic translation:

Ex : select APEX_LANG.MESSAGE(’MY_MESSAGE’)

This will display the text defined in the message text in the language in which the application is running for MY_MESSAGE.

There is a view called APEX_APPLICATION_TRANSLATIONS that lists all the text message translations.

The APEX_LANG API

Like many of the functionalities in APEX, there is an API that allows you to perform all the translation tasks programmatically. As explained earlier, you can use APEX_LANG.LANG to create dynamic translations and APEX_LANG.MESSAGE to translate the internal text used for interactive reports and other APEX functionalities. APEX_LANG can also be used for all the other translation functionalities such as creating mappings, seeding, publishing, and updating the translated strings. The APEX API documentation explains in great detail the APEX_LANG API and includes examples.

With the APEX_LANG API, you can use the following procedures to create or delete the language mapping for the translation of an application:

CREATE_LANGUAGE_MAPPING
DELETE_LANGUAGE_MAPPING
UPDATE_LANGUAGE_MAPPING

The LANG procedure is used for dynamic translations. See the “Dynamic Translations” section.

The MESSAGE and UPDATE_MESSAGE procedures are used for translatable text message and the APEX internal text (for example, the Interactive reports menu) for the specified application. See the “Translating APEX Internal Text” section.

The SEED_TRANSLATIONS procedure is used to see the translatable text from the specified application into the translation repository.

The UPDATE_TRANSLATED_STRING procedure allows you to change the translated strings in the translation repository.

The PUBLISH_APPLICATION procedure is used to publish the translated version of an applications. Perform a seed and publish process each time you want to update the translated version of your application and synchronize it with the primary application.

Copying Translations to Other Environments

In a standard configuration, you usually have multiple environments: one for development where developers have the flexibility to change almost anything and one for user testing that is usually more restricted and often used for testing the move to production, maybe a preproduction environment and a production environment that is completely secured and restricted. This section explains how you can move your applications and their translations from one environment to another.

Translating a primary application into multiple languages generates a separate translated application for each mapped language. When the seed is completed, the translation repository is populated, and this data is used to generate and publish the translated applications. The translated applications cannot be modified in the builder but can be exported and imported.

There are different ways to promote your applications and their translated versions from one environment to the other.

You can do the following:

• Export/import the primary application with the translation
• Export and import the primary application and the translated applications
• Export only the primary application and the XLIFF file

You should always use the first option of exporting the primary application with the translation. The other options, as explained later, can be used in specific cases.

A good practice is to use the same application IDs in all your environments. This makes it easier to maintain because you can relate and identify the same application everywhere. But, in certain cases, you may have to change the primary application ID or the translated application IDs. It could be because you are sending your application to the cloud or because you use the same database for dev and test.

The good news is that you can change the mapping of the translated application at import time. APEX will even automatically give you a new application ID for your mapped application if it is already used by another application.

Translation text messages and dynamic translations will always be included in the primary application export.

Export/Import the Primary Application with the Translations

To move your primary applications and their translation mappings from one environment to the other, you have to export the primary application with the translation. In the export preferences, there is an option called Export Translations (see Figure 6-23). This option is set to Yes by default.

Using this option will include the translation repository content of all your mapped translations in the export file.

After you import the primary application into the destination environment, the translation repository will be populated, and you will have to publish (synchronize) the translated applications. This is not done automatically.

If you want to change the primary application ID, this is the method you have to use. Importing an application that contains the translations will automatically map new application IDs.

Export/Import the Primary Application and the Translated Applications Separately

You can also export the primary application and the generated translated applications one by one separately. The translated applications cannot be modified but are accessible in the export process.

Be aware that if you import the translated application by itself, you will not be able to regenerate it without the content in the translation repository. If ever you import the primary application and the mapped applications separately and you want to regenerate the mapped applications at a later time, you will have to keep the mapping in the primary application and upload a valid XLIFF file that uses the same application IDs.

Export Only the Primary Application and the XLIFF

Another way of promoting your applications is to export the primary application without the translations and download the corresponding XLIFF files. This is the same as exporting the primary application with translations but doing so manually. The difference is that you will have to save the XLIFF file in your source versioning system and make sure that you apply it and publish in the destination environment.

This could be useful if ever you want to restart the translation from a backup or if you forgot to select the Export Translations preference during the export.

Localization

Localization refers to when data is displayed to the end user based on his location. It could be the time and date of a meeting or the number format used in the end user’s region, for example. When localizing applications, special attention must be paid to the different formats used for dates and numbers. Date formats are different depending on the user’s location.

Since APEX is a set of PL/SQL programs and runs in the Oracle Database, every database NLS setting can be changed using an alter session.

Oracle also provides locale-sensitive date formats, which, of course, can be used in APEX. For example, a Long Date format of DL can be used to show the date in different languages.

Alter session set NLS_TERRITORY=’CANADA’;
Alter session set NLS_LANGUAGE = ’FRENCH’
Select to_char(sysdate,’DL’) from dual;

TO_CHAR(SYSDATE,’DL’)
---------------------
lundi 21 février 2011

Alter session set NLS_TERRITORY=’CANADA’;
Alter session set NLS_LANGUAGE=’ENGLISH’
Select to_char(sysdate,’DL’) from dual;

TO_CHAR(SYSDATE,’DL’)
---------------------
monday 21 february 2011

Developers should be careful in their choice of date formats that are used when developing global applications. Long Date format (DL) and Short Date format (DS) could be used to display dates in locale-sensitive formats.

Long Date format (DL):

• en-us: Monday, February 21, 2011
• fr-ca: lundi 21 février 2011

Short Date format (DS):

• en-us mm/dd/yyyy: 6/16/2008
• de dd.mm.yyyy: 16.06.2008

SINCE Format Mask

In APEX, you can use the SINCE format mask on date and timestamp columns. It has to be defined in the format mask of report columns or in the Automatic DML page items.

Values will be displayed in the format “x days ago,” where x is the number of days before the current time, as shown in Figure 6-24. This format mask is also translated into the ten languages available for APEX builder (see “Loading Languages” at the beginning of this chapter).

Future dates and timestamps are also supported in APEX. It will display “x days from now,” where x is the number of days after the current time. The SINCE format mask is supported against the columns of type TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE.

The APEX_UTIL.GET_SINCE API can also be used to return the SINCE format for a DATE or a TIMESTAMP.

Numeric Formats

The same approach should be taken with numeric formats. If your application is going to be global, choose appropriate numeric formats. The important part of the format is the locale-neutral format G and D that defines the Group (G) separator and the Decimal (D) separator. The TO_CHAR function also accepts NLS parameters.

For example, to_char(123.45,’999D99’,’NLS_NUMERIC_CHARACTERS = ’’.,’’’) will display the number 123.45 with the appropriate decimal notation depending on the territory defined in the APEX session. For the Currency format, the following should be used: FML999G999G999G990D00. The FML format will display the correct currency symbol depending on the territory of the session. Keep in mind that this formats only the number and that there are no automatic processes available to convert the amounts based on currencies and localization in APEX. To convert an amount to a different currency, a process has to be programmed in order to get the currency conversion rate and apply it to the amount.

The session NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, and NLS_TIMESTAMP_TZ_FORMAT are set in the Globalization attributes of the Shared Components of the application, as shown in Figure 6-25.

Time Zones and Territories

To demonstrate the time zone and territory localization features in APEX, I built a simple application. In the example shown in Figure 6-26, I am running the application in Canada, localized in Montreal. You can see that the time zone is Greenwich –5, the currency sign is $, and the decimals indicator is represented by a comma, which is the locale standard for that region.

If you use a timestamp with the local time zone data type, the display of the time automatically changes when time zone changes are made. A user accessing the application from France will therefore see their local time, while a user accessing the application from New York will see the dates in their local time.

It is also possible to automatically set the time zone by setting Automatic Time Zone to Yes in the Globalization attributes in the Shared Components area of the application (see Figure 6-27). This setting will use the client localization attributes defined by the web browser to determine the time zone and will be set for the duration of the Application Express session.

When Automatic Time Zone is set to Yes, it is automatically set in the URL when the application is run for a new session. You can see the TZ parameter at the end of the URL. This setting can be overridden using APEX_UTIL.SET_SESSION_TIME_ZONE or reset using APEX_UTIL.RESET_SESSION_TIME_ZONE.

In this example, a select list is used to allow the user to select the time zone, as shown in Figure 6-28. Once selected, it is changed using the APEX_UTIL API as follows:

APEX_UTIL.SET_SESSION_TIME_ZONE(:P1_NEW_TZ);

Because in this example you are using a data type timestamp with the local time zone, the query will automatically change the display of the time of the date value from the database to the corresponding time zone.

It is also possible to change the time zone directly in the URL; for example, you could add &tz=+2:00 for the Turkey time zone.

/pls/apex/f?p=115:1:0:::::&tz=+2:00

The territory is linked to the application language. When the language is derived using “session,” the p_territory parameter can be changed in the URL without having to change the application language. There is also an API that can be used to change the territory programmatically.

This parameter also establishes the default date format, the default decimal character and group separator, the default International standard (ISO), and local currency symbols.

In this example, you added a select list to select the territory for the application, as shown in Figure 6-29. Changing the select list will call the APEX_UTIL API as follows: APEX_UTIL.SET_SESSION_TERRITORY(:P1_NEW_TERRITORY);.

For a list of valid territories, you can use the following select statement:

select value
from v$nls_valid_values
where parameter=’TERRITORY’
order by 1

The number will then be localized based on the territory since you are using the number format FML999G999G999G990D00. The format changes only the display of the number; it does not convert the amount. To apply currency conversion, you need to get the current conversion rate, as explained earlier.

You can also change the territory in the URL by using the P_TERRITORY parameter with a valid Oracle territory name.

/pls/apex/f?p=115:1:0:::::&p_territory=AMERICA

Switching Languages

Using multiple languages in an application often means that the user can select which language he wants to access the application in.

A simple way to allow this is to add a link in the navigation bar to switch from one language to another. For example, let’s assume that the language is derived using “session” as defined in the globalization attributes of the shared components of the primary application. What you want to do is change the Language value in the session and refresh the page.

One way of doing this is to use a dynamic action that changes the language in the session using the APEX_UTIL.GET_SESSION_LANG and SET_SESSION_LANG and JavaScript to reload the page.

For example, let’s say that the language of the primary application is English and that the translated application is French. You want to display a link on the page to switch from one language to the other. So, in your English version, you want a link that says “Français” and in your French version you want a link that says “English.”

Here are the steps to implement this:

1. Add a link in all your pages to switch the language.

   The first step consists of adding a link in your page to allow the user to switch to another language. One way of doing so is to add a link with a CSS id=switchlang. Using this ID you will be able to assign a dynamic action (see step 2) to this link.

   You can use the navbar entries to have the link at the top of every page. For example, if you are using the APEX 5 universal theme, you can do the following:

   1. Edit the Navigation Bar list template in the User Interface section of the shared components.
      • Add the CSS id=#A03# to the list template in the Current and NonCurrent sections (see Figure 6-30).

        

        Figure 6-30. Modifying the list template in the Current and NonCurrent sections for id=#A03#

      • At the bottom of the page, in the Attribute Descriptions section of the same template (see Figure 6-31), for the attribute #A03#, add a description like Switch Lang ID. This is the attribute you will use to set the link with CSS ID=switchlang.

      

      Figure 6-31. Setting the #A03# attribute description

   2. Edit the Navigation Bar List in the Navigation section of the shared components. Create a new entry in the Desktop Navigation Bar (see Figure 6-32) with the following:
      • List Entry Label: Français
      • Target type: URL
      • URL Target: javascript:void()
      • User Defined Attribute #3: switchlang (see Figure 6-33; this value will replace the #A03# that you added in the template in step 1a at runtime and set the id=switchlang)

      

      Figure 6-32. Setting the navbar list entry

      

      Figure 6-33. Setting the navbar list entry user-defined attribute #3

   This will create a link on the top-right corner that says “Français” with the id=switchlang and a link that calls javascript:void();. Checking the Translatable check box indicates that this list entry will be extracted for translation.

2. In a page 0 global page, add a dynamic action on click, based on a JQuery selector of #switchlang with two true actions (see Figure 6-34).

   

   Figure 6-34. Switch Lang dynamic action

   In the Execute PL/SQL Code area, do the following:

   if nvl(apex_util.get_session_lang,’en-ca’) = ’en-ca’
      then apex_util.set_session_lang(’fr-ca’);
      else apex_util.set_session_lang(’en-ca’);
   end if;

   In the Execute JavaScript Code area, do the following:

   location.reload();

   Make sure that both your actions are set to No for Fire on Page Load.

3. Seed, translate, and publish your application.

   The last step is to translate the application so that the switch language link will be functional in both French and English.

   Seed the application, translate List Item Display Text from “Français” to “English,” and publish.

   Voilà! (See Figure 6-35.) Using this example, the user will be able to switch languages on any pages within the application.

   

   Figure 6-35. Switch Lang dynamic action

   Care should be taken when running an application in multiple languages because it can be confusing. Development is always done in the default primary language application, but the translated applications must be resynchronized (published) even if the changes made are not text related or do not require translation in order for these to be included.

   Testing an application in the default language first and publishing the translated applications before testing other languages is an essential part of debugging and testing.

Summary

In today’s business market, globalization is an important aspect of application development. Companies often have offices all over the world, and customers can be located anywhere. Unfortunately, globalization is often a feature overlooked by many, but it is definitely worth looking at and may end up to be useful. The Internet allows you to create applications that can be used by multiple users in multiple locations at the same time. Oracle Application Express possesses all the required tools to support you in building globalized applications to help you better serve your customers.

As shown in this chapter, translation is relatively easy once you understand that an application must be seeded, translated, and published. Having all the information stored in a metadata makes it even easier. Another important aspect is that the translation process can be extended to third parties, because of its use of the standard XLIFF format, also used by translators.

Localization is the other aspect of globalization taken care of in APEX. Date formats, number formats, time zones, territories, and other NLS settings are easily customizable. There are even automatic settings available to developers for building fully localized applications. And even for U.S. English–only applications some of the globalization attributes can be useful.

All in all, APEX has been handling globalization from its first version, and this feature is well integrated. And I am sure that it will continue to improve in its next releases.