When APEX is first installed, English is the default language. In order to be able to use the builder in other languages, you must install them. This will also translate Interactive Reports menus and error messages that will be used by the translated applications.

The scripts required to load any of the nine other languages available for the builder can be found in the APEX zip file within the builder folder, as shown in Figure 5-1. This directory contains one subdirectory for each of the available languages.

Figure 5-1. Language directories of the APEX zip file

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 OS level. Check the documentation for the proper command for your operating system (in this example we are running on Windows XP). 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:

ALTER SESSION SET CURRENT_SCHEMA = APEX_040000;
@load_lang.sql

where lang is the specific language (for example, load_de.sql for German or load_ja.sql for Japanese).

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

Once the language is loaded, the option to use this language in the APEX login page can be found at the bottom of the login region, as shown in Figure 5-2.

Figure 5-2. Available and loaded languages for the builder

The option to change the language of the builder is also available on the APEX builder home page, as Figure 5-3 shows.

Figure 5-3. Loaded languages for the builder and internal text

There is a report available in the Administrative APEX workspace (internal) which 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.

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 since the business market is 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 first step required to translate an application is to configure the Globalization Attributes. They are defined in the Shared Components of the application (see Figure 5-4).

Figure 5-4. Globalization Attributes option

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

The first thing to do is define the primary application language. To do so, simply choose the language in the Application Primary Language select list, shown in Figure 5-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 is always English (United States) (en-us).

The way APEX supports multilingual applications is by creating one copy of the primary application for each translated version. At run time, 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.

Figure 5-5. Globalization attributes

Application Language Derived From

Once the primary language is defined, the Application Language Derived From parameter must be set, as shown in Figure 5-6. This will determine how APEX decides which language will be used when running the application. Using this parameter, APEX will execute the corresponding mapped application.

Figure 5-6. List of choices for Application Language Derived From

The different options to derive the language from are

• NO NLS (Application not translated): If no translations are required for the application.

• Application Primary Language: Uses the primary language value as the application language. This is useful in order 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 the Application Language Derived from "Application Primary Language" setting 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.

• 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 mapped 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. Prefences 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 API APEX_UTIL.GET_PREFERENCE:

  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 due to 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. After, to set the language of the application, a simple change of the value of FSP_LANGUAGE_PREFERENCE is needed. For example in PL/SQL one could write:

  :FSP_LANGUAGE_PREFERENCE := 'fr-ca' ;

  Once the value is changed, in the next page rendering, APEX will check that value and run the corresponding mapped 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, to access page 200 in fr-ca with a primary application in en-ca and while setting the FSP_LANGUAGE_PREFERENCE, the URL would be:

  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 we 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, the FSP_LANGUAGE_PREFERENCE would be set, followed by a redirection to page 200, in our 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 FSP_LANGUAGE_PREFERENCE item, although this will not change the value of the FSP_LANGUAGE_PREFERENCE application item.

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

• Session: The last option for Application Language Derived from is Session, which is new in APEX 4.0. When this option is selected, APEX will determine the application language from the session setting.

  The session is set using the API APEX_UTIL.SET_SESSION_LANG or by using the P_LANG parameter in the URL. For 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 application to the language you would like to translate it to.

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 of the application, and select the first option: Map your primary language application to a translated application. You will get to the application language mapping page as shown in Figure 5-7.

Figure 5-7. Application Language Mapping configuration

Enter the corresponding application ID. This ID must be available in the APEX installation. The translation actually creates a copy of the application. This application will use the Application ID entered here and will be available for exports and statistics only.

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 application 131 would be mapped to application 9131. 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 mapped application will use. Using this option, all the images used by the application will have to be in that directory.

Once the mapping is done, you will see the following message: "Translated applications are published as new applications. You must specify a primary language application ID and a translated language application ID for each application and for each language you wish to translate." That means that you can create as many mappings as required, one for each language that your application is going to be translated to.

CSV Encoding

When exporting the data in a 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 5-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 application Shared Components.

Figure 5-8. Automatic CSV Encoding option

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 seeding the translated application, translating text either manually or via an XLIFF file, and finally 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 versions. Translating an application creates a new hidden application that is exportable/importable, but not modifiable.

All the following steps are in Shared Components, Translate Application.

Seed Translatable Text toTranslation Repository

The seeding process 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, click Next, and confirm (see Figure 5-9). You will see a summary page with some statistics on the translatable text loaded in the repository.

Figure 5-9. Seed to translation repository

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

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

The Option "manually edit 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. We 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 Application Translation to extract and click the "Export XLIFF" option (see Figure 5-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" checkbox must be checked, 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.

Figure 5-10. Download the XLIFF file.

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

It is good practice to keep a copy of the XLIFF file with the export of your application. This way, if ever a restore of the application is required, the corresponding XLIFF file will be available and you will be able to publish and regenerate the corresponding mapped applications.

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

If we 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 gives indications of 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-2011 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>Projets</source>
<target>Projets</target>
</trans-unit>
<trans-unit id="S-5-1-122">
<source>Projets</source>
<target>Projets</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.

Warning

If you change the primary application ID by exporting and importing an application, you will not be able to reuse the XLIFF file. See the section "Moving Translations to Other Environments" for more details.

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 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. For example, PSPad is a freeware editor that supports UTF8.

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 will be 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 the translation process, sometimes there may be text in the application templates that requires translation as well. In order to have the template text included in the XLIFF file, these templates have to be identified as translatable, as shown in Figure 5-11.

Figure 5-11. Translatable option for templates

Check the Translatable checkbox in the template properties. This tells APEX to include the content of the template in the XLIFF file.

Apply XLIFF Translation File to Translation Repository

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

Figure 5-12. Apply the XLIFF file

First, upload the XLIFF file that you want to apply. Specify a title that will identify the upload. Since the same filename may be uploaded multiple times, adding a version number to the title, along with a description, will make it easier to identify it later. Use the Browse button to select the file to upload. The file is uploaded in the APEX repository.

To apply the XLIFF file, click on the name of the file that was just uploaded. Select the language mapping it should be applied to in the Apply To select list, and click the Apply XLIFF Translation File button.

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, styles, and most other changes will not appear in the mapped applications until the publish process is performed.

The mapped applications are not editable but can be exported. You will see them in the list of available applications in the export option 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 before. 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 also translate directly within APEX through the Shared Components, Translate Application menu. This allows you to bypass the XLIFF file export and apply 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 5-13.

Figure 5-13. Manually Edit Translation Repository option

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

Figure 5-14. Manual translation in the translation repository

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 very 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 only translate the text buttons, a filter on the Column Description for Page Button Text could be used. Unfortunately, this information is not available in the XLIFF file.

Another purpose of this report is to validate that the right XLIFF file was applied in the translation metadata. You can do this by simply checking whether the changes you made in the XLIFF file are in this report using the Search option. During the translation process a lot of XLIFF files can be generated and applied and sometimes it gets confusing.

For multilingual applications, it is very 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: 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 that we have a bilingual application in French and English. Descriptions must be displayed in both French and in English, based on the language currently being used.

In the database, we 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 only show the one that corresponds to the active language.

First, add a new column 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, we 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, we 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 we 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.

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

Dynamic Translation is in fact a simple function (APEX_LANG.LANG) that 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 application.

To create dynamic translations, you must use the "Optionally identify any data that needs to be dynamically translated to support SQL based lists of values" option in the Translate Non Application Text section, as shown in Figure 5-15. This section is found in Translate Application in the Shared Components.

Figure 5-15. Dynamic Translations option

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

The steps to follow are as follows (see Figure 5-16):

Figure 5-16. Dynamic Translation page

The Translate From Text 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)
From departments;

The result of the LOV query in English (en-us) is shown in Figure 5-17.

Figure 5-17. Example of dynamic translation (en-us)

Result of the the same LOV query in French (fr-ca) is shown in Figure 5-18.

Figure 5-18. Example of dynamic translation (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 programatically, but none currently exists. In APEX 4.1, there is a new view called APEX_APPLICATION_TRANS_DYNAMIC listing the content of dynamic translations.

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 reports 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 of the Globalization menu, shown in Figure 5-19. Other options exist in the Shared Components that link to the exact 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."

Figure 5-19. Translating APEX internal text

Table 5-1 is a short list of 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. In order 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 5-20.

Figure 5-20. Interactive report text translation

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

Figure 5-21. Interactive report action button in Czech

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. Currently, in APEX 4, there are no views available to list the translated text messages for an application.

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".

For the complete list of translatable Internal Messages, please refer to the Application Builder User's Guide, Chapter 16, "Managing Application Globalization, Translating Messages, Translating Messages Used Internally by Oracle Application Express."

This feature 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.

Most of our customers uses multiple environments: usually one for development where developers have the flexibility to change almost anything, one for user testing that is usually more restricted and often used for testing the move to production, 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 an application into multiple languages generates a separate 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. These applications cannot be modified in the builder, but can be exported and imported.

Be aware that when exporting and importing an application, APEX keeps the mapping to the application IDs defined in the globalization attributes. But this does not export and import the translation metadata and, without the metadata, the translated application cannot be republished.

Because the application mapping to translations is done with the application ID, the same application IDs must be used in all your environments: development, test, and production. In an APEX installation, application IDs are unique throughout all the workspaces. This is also true for translated applications. In order to keep the translation mapping and translated applications working, keep the same application ID in all environments.

Anyway, it is good practice to use the same application IDs in all environments, especially if you have translated applications. It is also important to be aware that if an application is shared with others, the same application IDs will have to be reused to keep the language mapping.

The following sections describe two methods for moving applications from one environment to another, copying only the applications or copying the primary application and publishing the translated applications.

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 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):

And for the Short Date format (DS):

SINCE Format Mask

In APEX, you can use the SINCE format mask on date and time stamp 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 5-22. 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 now supported in APEX 4. 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.

Figure 5-22. Example of SINCE date format

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 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 only formats 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 5-23.

Figure 5-23. Application date formats

Time Zones and Territories

To demonstrate the time zone and territory localization features in APEX, we built a simple application. In the example shown in Figure 5-24, we are running our 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.

Figure 5-24. Example application for Time Zones and Territories

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.

In APEX 4, it is also possible to automatically set the time zone by setting the Automatic Time Zone to Yes in the Globalization attributes in the Shared Components of the application (see Figure 5-25). 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.

Figure 5-25. Setting the time zone automatically

In our example, a select list is used to allow the user to select the Time Zone, as shown in Figure 5-26. 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 we are using a data type timestamp with 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.

Figure 5-26. Example of time zone change

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 which 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 our example, we added a select list to select the territory for the application, as shown in Figure 5-27. 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, the following select statement can be used:

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

Figure 5-27. Example of territory change

The number will then be localized based on the territory since we are using the number format FML999G999G999G990D00. The format only changes 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.

The territory can also be changed 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

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 application.

First, add an entry to the navigation bar in the Shared Components of the application.

In the example shown in Figure 5-28, the new entry (link) will call page 111 using the request BRANCH_TO_PAGE_ACCEPT. This will execute the processing and branching of the page 111 only and not the rendering part. The P111_LAST_PAGE will be also set to the current page ID using the variable APP_PAGE_ID. This will be used to to branch back to the current page.

Figure 5-28. Navbar entry for switching the language

Next, create a blank page 111, and edit it. Add an HTML region called Switch Lang and add a Page Item P111_LAST_PAGE, which will be used to store the page id of the calling page.

Then add a PL/SQL process that will switch the language on submit after computations and validations that contains

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

This PL/SQL checks the current language of the application with APEX_UTIL.GET_SESSION_LANG; if it is English, it sets the language to French. If not, it sets the language to English.

Finally, add a branch to page 111 to return to the calling page using P111_LAST_PAGE, as shown in Figure 5-29.

Figure 5-29. Branch to return to calling page

Don't forget to seed the translated application so that the new navbar and page 111 will be in the translated mapped application.

And, voilà! Using this example, the user will be able to switch languages from any pages within the application.

Care should be taken when running an application in multiple languages, as it can be confusing. Development is always done in the default primary language application, but the translated applications must be re-created (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.

In today's business market, globalization is an important aspect in 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 definetly worth looking at and may end up to be very useful. The internet allows us 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 seen 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 meta data makes it even easier. Another important aspect is that the translation process can be extended to third parties, due to 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 US 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 very well integrated. And I am sure that it will continue to improve in its next releases.