Chapter 11. Localization Issues

Since APEX is a web development environment, it allows your application to be accessed over an intranet or the Internet by a geographically diverse set of end users. These end users may cross geographical and language boundaries, which means that you may need to enable your application to be viewed in different languages. Fortunately, the APEX development team foresaw this requirement, and has provided a number of features to allow developers to create multilingual applications.

Now, not every application needs to handle different languages. Often, you can just ship an English version of the application. However, you can still take advantage of some localization-related features to make your application behave a bit nicer from the users' perspective, as you will see in this chapter.

Also, not only the application itself can benefit from being multilingual. Many people aren't aware that the Application Builder environment also provides the ability to be viewed in a number of different languages, potentially making development easier if English is not your first language.

Localizing Application Builder

Typically, when you install APEX, you will find that the Application Builder, SQL Workshop, and other user interfaces display in English, regardless of whether your operating system is set to use English as the primary language. In other words, even if your default locale is set to French, the APEX development environment (but not your applications) will be displayed in English. However, it is possible to have the development environment display in a number of different languages, depending on the default locale specified by the user's browser—that is, the default language setting of the browser used by the developer logging in to the development environment.

You can see which language is used when you connect to the Application Builder by looking in the lower-left section of the screen. For example, Figure 11-1 shows that the language is set to en-us (American English).

Browser language set to en-us

Figure 11.1. Browser language set to en-us

Choosing a Language

Where you choose your browser's language depends on your browser and operating system. For example, suppose you use an Apple Mac and Firefox or Safari as your main browser (we can't recommend Firefox highly enough—it makes your development life easier). In Mac OS X, you can change the order of your preferred languages in the International section of System Settings, as shown in Figure 11-2. You can find the equivalent settings in Windows in the Control Panel.

The preferred languages listing in Mac OS X

Figure 11.2. The preferred languages listing in Mac OS X

From here, you can rearrange the list to change your preferred language order. For example, you could move German (Deutsch) up to be your preferred language, as shown in Figure 11-3.

If you refresh the Application Builder page after changing your preferred language, you should see the browser language change from en-us to de, as shown in Figure 11-4.

However, at this point, all that has changed is the browser language string displayed in the Application Builder. The Application Builder itself (and SQL Workshop and other user interfaces) is still being displayed in English, not German as you might expect. This is because, by default, only the English translations are installed with APEX. If you want to display other languages, you need to manually install those language translations yourself.

Enabling German as the primary language

Figure 11.3. Enabling German as the primary language

Browser language now set to German (de)

Figure 11.4. Browser language now set to German (de)

Installing a Language File

You can see which languages are currently installed in APEX by logging into APEX as the instance administrator and navigating to Manage Service Installed Translations, as shown in Figure 11-5.

Viewing the installed languages for the instance

Figure 11.5. Viewing the installed languages for the instance

As you can see, the instance has only the en (English) language installed by default. So even if your browser language is set to German, APEX will determine that the German language is not installed and will fall back to showing the English translation instead.

So, how do you install the additional languages? Unfortunately, you can't just click and do it through a nice browser interface. You need to manually execute the SQL files yourself to load a specific language.

The SQL files that you need to load are part of the base APEX installation files as long as you download the apex_4.0.zip file. If you chose the apex_4.0_en.zip, the additional, non-English language files will not be available. If you did download the English-only file, you will have to download the apex_4.0.zip file as the files are not available separately. All you need to do in this case is unzip the file and change to the builder directory to locate the

Listing 11-1 shows a listing of the builder subdirectory in the directory where we downloaded APEX; in other words, if we downloaded and extracted APEX into the /tmp directory, it would be the /tmp/apex/builder directory.

Example 11-1. Language Files in the builder Subdirectory

[oracle@oim builder]$ ls -al
total 64556
drwxr-xr-x 11 oracle dba     4096 Nov 19 13:25 .
drwxr-xr-x  8 oracle dba     4096 Mar  2 01:53 ..
drwxr-xr-x  2 oracle dba     4096 Nov 19 13:25 de
drwxr-xr-x  2 oracle dba     4096 Nov 19 13:25 es
-r--r--r--  1 oracle dba 35273236 Nov 19 13:25 f4000.sql
-r--r--r--  1 oracle dba  3197748 Nov 19 13:25 f4050.sql
-r--r--r--  1 oracle dba   129878 Nov 19 13:25 f4155.sql
-r--r--r--  1 oracle dba   837932 Nov 19 13:25 f4300.sql
-r--r--r--  1 oracle dba  3427440 Nov 19 13:25 f4350.sql
-r--r--r--  1 oracle dba  6376151 Nov 19 13:25 f4400.sql
-r--r--r--  1 oracle dba  2092367 Nov 19 13:25 f4411.sql
-r--r--r--  1 oracle dba  7305685 Nov 19 13:25 f4500.sql
-r--r--r--  1 oracle dba   185510 Nov 19 13:25 f4550.sql
-r--r--r--  1 oracle dba   985838 Nov 19 13:25 f4600.sql
-r--r--r--  1 oracle dba   181215 Nov 19 13:25 f4700.sql
-r--r--r--  1 oracle dba  2613201 Nov 19 13:25 f4800.sql
-r--r--r--  1 oracle dba  3328979 Nov 19 13:25 f4900.sql
drwxr-xr-x  2 oracle dba     4096 Nov 19 13:25 fr
drwxr-xr-x  2 oracle dba     4096 Nov 19 13:25 it
drwxr-xr-x  2 oracle dba     4096 Nov 19 13:25 ja
drwxr-xr-x  2 oracle dba     4096 Nov 19 13:25 ko
drwxr-xr-x  2 oracle dba     4096 Nov 19 13:25 pt-br
drwxr-xr-x  2 oracle dba     4096 Nov 19 13:25 zh-cn
drwxr-xr-x  2 oracle dba     4096 Nov 19 13:25 zh-tw

You may have noticed in Listing 11-1 that some of the SQL files relate to specific applications in APEX. For example, the f4500.sql file relates to the Application Builder application itself, and the f4050.sql file relates to the internal administration interface.

The important thing to notice in Listing 11-1 is that the directory contains a number of subdirectories, each corresponding to a particular language. For example, if you look inside the de subdirectory, you will see the SQL files that correspond to that language, as shown in Listing 11-2.

Example 11-2. SQL Scripts to Install the German (de) Language

[oracle@oim de]$ ls -al
total 65088
drwxr-xr-x  2 oracle dba     4096 Nov 19 13:25 .
drwxr-xr-x 11 oracle dba     4096 Nov 19 13:25 ..
-r--r--r--  1 oracle dba 35691183 Nov 19 13:25 f4000_de.sql
-r--r--r--  1 oracle dba  3199609 Nov 19 13:25 f4050_de.sql
-r--r--r--  1 oracle dba   126568 Nov 19 13:25 f4155_de.sql
-r--r--r--  1 oracle dba   845207 Nov 19 13:25 f4300_de.sql
-r--r--r--  1 oracle dba  3409380 Nov 19 13:25 f4350_de.sql
-r--r--r--  1 oracle dba  6423183 Nov 19 13:25 f4400_de.sql
-r--r--r--  1 oracle dba  2143639 Nov 19 13:25 f4411_de.sql
-r--r--r--  1 oracle dba  7352214 Nov 19 13:25 f4500_de.sql
-r--r--r--  1 oracle dba   181763 Nov 19 13:25 f4550_de.sql
-r--r--r--  1 oracle dba   983752 Nov 19 13:25 f4600_de.sql
-r--r--r--  1 oracle dba   178292 Nov 19 13:25 f4700_de.sql
-r--r--r--  1 oracle dba  2621278 Nov 19 13:25 f4800_de.sql
-r--r--r--  1 oracle dba  3334726 Nov 19 13:25 f4900_de.sql
-r--r--r--  1 oracle dba     2868 Nov 19 13:25 load_de.sql
-r--r--r--  1 oracle dba      717 Nov 19 13:25 null1.sql
-r--r--r--  1 oracle dba     2005 Nov 19 13:25 rt_de.sql
-r--r--r--  1 oracle dba     2510 Nov 19 13:25 unload_de.sql

As you can see, the subdirectory contains a separate file for each application that makes up APEX. This allows you to have an English and a German version of the Application Builder, for example.

If you examine the load_de.sql file (or the equivalently named file for the other languages), you will find the beginning of the file contains some notes, which make a couple of very important points:

  • It assumes the APEX owner.

  • The NLS_LANG must be properly set in the environment prior to running this script; otherwise, character set conversion may take place. The character set portion of NLS_LANG must be set to AL32UTF8, as in AMERICAN_AMERICA.AL32UTF8.

The first point means that you need to load these scripts as the schema you installed APEX into (for example, the APEX_040000 schema), rather than your own application schema.

The second point is extremely important and is easy to overlook. If you fail to properly set the NLS_LANG environment variable before running the script, you may end up with some character set conversion, leading to corrupted characters being stored.

You can set the NLS_LANG environment variable using the export command in Linux/Unix, assuming you are using the Bash shell. If you are on a Windows system, you can use the SET command. Listing 11-3 shows the environment variable being set and then queried to check if it has been set correctly.

Example 11-3. Setting the NLS_LANG Environment Variable

[oracle@oim de]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
[oracle@oim de]$ echo $NLS_LANG
AMERICAN_AMERICA.AL32UTF8

Next, to install the language translations, you need to connect via SQL*Plus (or some other tool if you prefer) as the APEX_040000 user and run the load_<language>.sql script. Since you don't normally log in to the database as APEX_040000, you can log in as another user and then alter the session to use the APEX_040000 schema. Listing 11-4 shows the session being altered, the load_de.sql script being run, and an abbreviated version of the script output.

Example 11-4. Running the load_de.sql Script

SQL> alter session set current_schema=APEX_040000;

Session altered.

SQL> @load_de.sql
...LOTS OF OUTPUT REMOVED
...shared queries
...report layouts
...authentication schemes
......scheme 108165525079033088.4703
...done
Adjust instance settings

PL/SQL procedure successfully completed.

Note

Depending on the speed of your machine, it may take a while to run this script. If you receive an error, or just wish to de-install a language, you can use the unload_language.sql script.

After installing the language file, if you again look at the Installed Translations section in the administration pages, you should see that the language was installed and is correctly detected. Figure 11-6 shows this section after running the load_de.sql script.

The German (de) language is now installed.

Figure 11.6. The German (de) language is now installed.

In earlier versions of APEX, you would have to change the preferred language of the browser to enable APEX builder page to display in a different language. In APEX 4.0, you don't have to change the browser settings to change languages. On the APEX home page, you will see a new sidebar menu called "Language," as shown in Figure 11-7, which displays all of the loaded translations. To change the language of the builder, simply click on German, or Deutsch, as in our example. The APEX home page, as shown in Figure 11-8, is now displayed in German. If you log out of APEX, you would see that even the APEX login page is displayed in German.

Application Builder page displayed in German

Figure 11.7. Application Builder page displayed in German

Application Builder page displayed in German

Figure 11.8. Application Builder page displayed in German

It is very much worthwhile to install the additional language translations reflecting the languages that your developers speak. This is particularly useful if you are running a public APEX instance that can be accessed by many developers around the world. It will present them with pages in their own language if that language is available; otherwise, it will fall back to using English.

Localizing Your Applications

The previous section described how to localize the Application Builder itself, but even if you don't need to localize the Application Builder (if all your developers are native English speakers, for example), you may still need to localize your own applications.

Obviously, it does not make sense to localize every single application, and it will very much depend on your own requirements (and resources) whether it makes sense to localize any given application. We won't get into a debate about whether it's sufficient to just use English and assume that all your site visitors will be able to understand it; however, there is often a benefit in including localization even if the end users also speak English. And remember that localization doesn't just mean the language that your applications are displayed in, but also refers to how numbers and currencies and dates and times are displayed. It also can mean application logic that is country-specific. So, by localizing your application, you are making it behave in the way that the end users expect it to behave, rather than forcing them to adopt a different style of working to fit in with your "hard-coded" (as far as they are concerned) ways of representing data.

A Simple Currency Example

Let's look at a simple example of how you can display currencies in your application in the locale of the end user. First, we need to introduce a currency field into the Buglist application. It's a bit of a contrived example, but we'll add a column to the buglist table called cost. In theory, this would allow a manager to assign an estimated cost to a reported bug that could then be used to prioritize and manage the bugs (as we said, it's a contrived example, but bear with us!).

We have also modified the report on the home page to include the Cost column in the report, and the Update Bug screen to allow the cost to be entered. We are not going to reproduce all the steps we performed, since by this point, you should be comfortable enough doing the work yourself. The end result is that if you enter a value for a cost against a bug and look at the report, you will see a page similar to Figure 11-9.

Retrieving a currency value in the report

Figure 11.9. Retrieving a currency value in the report

The currency value is being displayed in US dollars (USD) because we specified the following format mask for the Cost column in the report:

FML999G999G999G999G990D00

This mask will use the default currency symbol based on the NLS parameters for the session. In this case, the NLS parameters are being picked up from the setting in the DAD, typically AMERICAN_AMERICA.UTF8, which is why the dollar sign is displayed.

Now suppose that the application will be used exclusively in the United Kingdom, so we want to use pound sterling as the currency symbol. To use another currency symbol, we need to override the NLS setting each time we make a web request. We must do this each time because we want to make sure that the NLS setting is correct no matter which connection we get from the mod_plsql connection pool. In other words, if we just changed the setting when we authenticated to the application, we might get another connection from the connection pool that is still using the default NLS settings.

As explained in Chapter 4, the ideal place to make this sort of session setting is in the VPD section of the application security attributes. Any code that you place in the VPD section (or call from this setting) is executed each time you make a request.

You can add the following code to the VPD section to set the NLS_TERRITORY setting to United Kingdom:

BEGIN

  EXECUTE IMMEDIATE
    'ALTER SESSION SET NLS_TERRITORY="UNITED KINGDOM"';
END;

Note that you need to wrap UNITED KINGDOM in double quotes due to the space in the string.

If you now run the report page again, you should see that the Cost column uses the pound sign as the currency symbol, as shown in Figure 11-10.

Displaying the pound sign

Figure 11.10. Displaying the pound sign

Similarly, if you modify the NLS_TERRITORY setting in the VPD section to use Germany instead, as follows:

BEGIN

  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_TERRITORY="GERMANY"';
END;

you would see the euro symbol displayed, as shown in Figure 11-11.

Displaying the euro sign

Figure 11.11. Displaying the euro sign

Notice that all we're doing here is displaying the cost using different currency symbols. We are not making any attempt to convert between currencies. In other words, if we are storing the currency as 100 USD but displaying it as 100 euros, our costs are going to be very wrong (unless, of course, the exchange rate changes such that 1 USD = 1 euro).

If you wanted to convert between currencies automatically, you would need to store the value in a fixed currency (for example, USD), and then maintain a table of exchange rates with which you could convert to the correct value depending on which NLS_TERRITORY setting you were using. (We'll leave the currency conversion as an exercise for the reader.) You could then modify this example so that rather than using a fixed NLS_TERRITORY setting in the VPD section, you instead picked up a setting specific for the user, as we'll demonstrate next.

User-Dependent Localization

In the previous example, we had one hard-coded NLS setting for all users. Now let's take a look at extending that simple example to allow for the end users being in different countries.

First, we need a way to store the NLS settings for each user. We could add an extra column to the user_repository table and then create a user profile type of page, where users can set their preferred time zone and region. However, we're going to keep this example simple, and just let users set the time zone on the report page. Then we will display the times according to the time zone they choose.

We've added a select list (called P1_TIMEZONE) to allow each user to select a time zone. We've created a new region on the right side of the page and added the select list to that region. The select list uses a query against a table called v$timezone_names:

select
  tzname||'-'||TZABBREV d,
  tzname r
from
  V$TIMEZONE_NAMES

The new select list is shown in Figure 11-12.

Selecting a time zone

Figure 11.12. Selecting a time zone

If a user selects an entry from the P1_TIMEZONE select list, we need to use that value to modify the session time zone. We can do this by adding a before-header page process that executes the following block of code. To ensure that your code is protected against a SQL Injection hack (possible any time you use execute immediate) you first need to ensure that the value of :P1_TIMEZONE is actually a time zone.

The code is as follows:

begin
  for cur in (select null from v$timezone_names
              where  tzname-:P1_TIMEZONE)
  loop
    alter session set time_zone = '''||:P1_TIMEZONE||''''),
  end loop;
end;

However, we need to ensure that this code is executed only when P1_TIMEZONE has a value; otherwise, the execute immediate statement will fail. We can use a Value of Item in Expression 1 Is NOT NULL condition on the new page process and use P1_TIMEZONE as the value of Expression 1.

We still will not see any visible difference in the report, even if we try selecting some different geographical time zones from the list. This is because the reported date stored against the bugs is just using a DATE data type. In order for our dates to be time zone-aware, we need to use a different data type. The data type we need to use depends on what we want to show. We have the following two main options:

TIMESTAMP WITH TIME ZONE:

Allows you to store a timestamp using a particular time zone (defined from the client connection), preserving the time zone as part of the data for later reference.

TIMESTAMP WITH LOCAL TIME ZONE:

Converts a timestamp to a baseline time zone (defined on the server), and then allows conversion of that timestamp upon retrieval for your particular session time zone. Columns of this type do not store any time zone information from the client; timestamps are stored in the local time zone, where local refers to the server itself.

The choice between which format you should use comes down to whether it's important to know the time zone with which the data was created. For example, if you need to know that a record was created with a timestamp of 9:00 a.m. in the US Eastern Time zone, you'll want to use TIMESTAMP WITH TIME ZONE to store that time zone. If you really don't care about the original time zone, you can use TIMESTAMP WITH LOCAL TIME ZONE to essentially convert all the date/time values into to the time zone of the server.

If you use the TIMESTAMP WITH TIME ZONE format, you can still convert between different time zones by using the built-in time zone functions. The following example finds the time zone offset between the local server time and a particular time zone. The offsets you see will depend on your time zone.

jes@DBTEST> SELECT TZ_OFFSET('Europe/London') from dual;


TZ_OFFSE
---------
+00:00

jes@DBTEST> select tz_offset('Australia/Darwin') from dual;

TZ_OFFSET
---------
+09:30

You can also convert between time zones.

1  select
2    systimestamp at time zone 'Asia/Singapore' as remote_time
3  from
4*   dual

REMOTE_TIM
---------------------------------------------
03-DEC-07 04.21.07.050327 PM ASIA/SINGAPORE

Here, we are converting the current time (as the server sees it) into the current time in a particular time zone (in this case Singapore, but you can use any valid time zone string). The syntax AT TIME ZONE looks a little strange at first, but it's an incredibly powerful way to easily convert time zone information to find out the date and time in one area relative to another. Obviously, you could adapt this code to use data stored in a table, rather than using the current server timestamp.

For our example, we will add a time zone-aware column to the buglist table and set it to use the TIMESTAMP WITH LOCAL TIME ZONE data type. Since we will set the value of this new column to the old reported column, and the reported column does not store any time zone information, it would not make sense to use the TIMESTAMP WITH TIME ZONE data type. Listing 11-5 shows the new column being added to the buglist table.

Example 11-5. Adding a Time Zone-Aware Column

SQL> desc buglist;
 Name                 Null?    Type
 ----------------- -------- -------------
 ID                         NUMBER
 BUGID                      NUMBER
 REPORTED                   DATE
 STATUS                     VARCHAR2(30)
 PRIORITY                   VARCHAR2(30)
 DESCRIPTION                VARCHAR2(255)
 REPORTED_BY                VARCHAR2(30)
 ASSIGNED_TO                VARCHAR2(30)
 COST                       NUMBER

SQL> alter table buglist
2  add (reported_ts timestamp with local time zone);
Table altered.

SQL> update buglist set reported_ts = reported;
19 rows updated.

SQL> commit;
Commit complete.

This might not seem like much of an improvement over the original DATE data type, but the following shows what happens if we query the data while changing our session time zone information:

1  select
2    to_char(reported_ts, 'dd/mm/yyyy hh24:mi:ss') as ts
3  from
4    buglist
5  where
6*   rownum < 5
SQL> /

TS
-------------------
27/01/2006 00:00:00
01/02/2006 00:00:00
02/08/2006 00:00:00
03/02/2006 00:00:00

So first, we see that the hour, minute, and second components are set to 00:00:00. Because when we originally created the data, we just specified a date for the reported field, without specifying a time, the time part has defaulted to midnight.

Now, the following shows what happens if we change our session time zone to be in a different part of the world:

SQL> alter session set time_zone = 'Australia/Darwin';


Session altered.

1  select
2    to_char(reported_ts, 'dd/mm/yyyy hh24:mi:ss') as ts
3  from
4    buglist
5  where
6*   rownum < 5
SQL> /

TS
-------------------
27/01/2006 15:30:00
01/02/2006 15:30:00
02/08/2006 15:30:00
03/02/2006 15:30:00

Notice how the time component has now changed to reflect the time difference between the server's (in this case, the server uses UTC) and the client's session time zone. Just to prove it, let's try another time zone.

SQL> alter session set time_zone = 'America/Los_Angeles';

Session altered.

1  selec
2    to_char(reported_ts, 'dd/mm/yyyy hh24:mi:ss') as ts
3  from
4    buglist
5  where
6*   rownum < 5

TS
-------------------
26/01/2006 22:00:00
31/01/2006 22:00:00
01/08/2006 23:00:00
02/02/2006 22:00:00

Notice how not only are the times different, but the dates are also different to reflect the time zones. Also notice that for the 01/08/2006 date, the time is actually different from the other times, this is due to the daylight saving time switchover.

As you can see, displaying the dates and times in the local format that your end users would expect to see can make the data much more readable and immediately understandable. This way, they don't need to do time comparisons and conversions themselves.

We can now adapt the report to include the new reported_ts column (using a suitable format mask to display the column in dd/mm/yyyy hh24:mi:ss format), so that the user can select a time zone and have the dates and times correctly shown according to that particular time zone, as shown in Figure 11-13.

Displaying time zone-aware columns in the report

Figure 11.13. Displaying time zone-aware columns in the report

In a real-world situation, you would probably want to allow users to define their time zone, NLS territory, and so on in their profiles, and then set these settings in the VPD section of your application. However, this simple example shows just how powerful these relatively cheap-to-implement techniques can be.

NLS Parameters

The previous sections demonstrated how to set two session parameters that influence how data is displayed: NLS_TERRITORY and TIME_ZONE. Many more NLS parameters are available. You can use the nls_session_parameters view to see which settings are available (and their values) for your current session:

jes@DBTEST> select * from nls_session_parameters


PARAMETER                      VALU
------------------------------ ------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE

So, for example, you can use the NLS_TIME_FORMAT setting to modify the way that times are displayed to the user. Also, you can use the NLS_CURRENCY setting to modify the currency symbol that is used, rather than modifying the entire NLS_TERRITORY, which affects more than just the currency symbol.

Using these simple techniques, you can transform the way your application is perceived by end users.

Tip

It is much more user-friendly to display dates and times in the end users' time zone, rather than forcing the users to manually calculate any offsets. Using the time zone data types requires very little extra coding. In fact, because they are still capable of storing regular dates, we almost always use the time zone data type rather than the plain-old timestamp data type. The advantage of this is that even if today we do not need to provide localized versions of our application, we can easily use the techniques described here to do so later.

Fully Translating Your Applications

The previous section demonstrated how you can easily localize dates and currencies in your application. But what if you want to provide a fully translated version of your application so that end users can access your site in their native language? Fortunately, the team behind APEX has made this a relatively straightforward process. And, obviously, you can combine this with the techniques shown previously to display dates and currencies in the correct format.

A core concept in the translation is that for each translated version of your application (for example Spanish, French, and so on), there is a separate copy of your application behind the scenes. In other words, you don't have one application that contains all the translations, but rather multiple applications. The user is taken to the correct application depending on the criteria you use to detect the language settings for that user.

Now, this multiple versions approach might sound like a huge overhead in terms of maintenance. For example, each time you change a piece of code in your application, do you need to also change it in every translated version of your application? Fortunately, that's not necessary. So if you have ten different translated versions of your application, you don't need to make the change in eleven (ten translations plus the original application) different applications. The mechanism for the translated application is much smarter than that.

Essentially, you can consider your original application as the master application, from which all the translated versions inherit the code, look and feel, logic, and so on. You never need to directly modify the translated versions. Instead, you modify the primary application and let those changes filter into the translated applications. In fact, you will not see those translated applications listed in the main Application Builder interface (to prevent you from editing them directly).

Defining the Primary Application Language and Derived From Language

The first step in providing a multilingual application is to decide what the primary language of your main application is going to be. You define this at the application level, in the Shared Components Edit Globalization Attributes section, as shown in Figure 11-14.

Defining the application's primary language

Figure 11.14. Defining the application's primary language

Why is it so important to define the primary language? Well, the APEX environment will use the Application Primary Language and Application Language Derived From settings to determine which application the end user should be directed to (remember that behind the scenes, there will be multiple applications—one for each translation).

In this example, we are telling APEX that the primary language of this application is en-us and that all users should always see the Application Primary Language version of the application (in this example, application 101). Even if we have translated versions of the application, every user would see the en-us version.

Since we wish users to see different translated versions of the application, we need to change the Application Language Derived From setting to something more appropriate. We have the following choices:

No NLS (Application not translated):

This is used if you are not planning to translate the application at all (the primary application will always be used).

Use Application Primary Language:

Very similar to the first option, except it allows you to use translated applications; however, all users will see the same translated application. For example, you can switch between English and Spanish, and all users will see the same change.

Browser (use browser language preference):

This will use the end user's browser locale setting to determine the application's primary language. For example, if the user's browser is set to German, that user will see the German version of the application.

Application Preference (use FSP_LANGUAGE_PREFERENCE):

This will use the value of the FSP_LANGUAGE_PREFERENCE application item, which can be set via the application using the APEX_UTIL.SET_PREFERENCE procedure. Since this is a user preference, the same setting will apply each time the user logs in to the application.

Item Preference:

Similar to the Application Preference option (also uses FSP_LANGUAGE_PREFERENCE); however, this will be evaluated each time the user logs in to the application.

In this example, we are going to use the locale setting of the user's browser to determine the language to present. To that end, we need to change the Application Language Derived From setting to Browser, as shown in Figure 11-15.

Using the browser language preferences

Figure 11.15. Using the browser language preferences

Now whenever the user connects to the application, APEX will automatically detect the browser language preference and will use that to determine which translated version of the application to show the user.

Creating Translated Versions of an Application

So, how do you create a translated version of the application? Like many things in APEX, it is done via the Shared Components section. Figure 11-16 shows the Translate Application wizard in that section.

Translate Application wizard

Figure 11.16. Translate Application wizard

As you can see from Figure 11-16, you need to go through a number of steps to turn your application into a multilingual one. You can simply click each link to go to the appropriate step in the wizard.

Mapping a Translation

The first step is to map your original primary language application to a translated application. You do this for every translated version of the application—the Spanish version, the French version, the German version, and so on.

This mapping allows APEX to create a new version of your application that corresponds to a particular language. The first time you do this, there will be no existing mappings. Figure 11-17 shows creating a new mapping.

Defining a new application language mapping

Figure 11.17. Defining a new application language mapping

Notice how you need to define an application ID for the mapping. This is the application ID that will be used for the behind-the-scenes application. You can pick any unused application ID that you like; however, there is one caveat that might seem a bit odd at first: you cannot use an application ID that ends in zero. For example, if you entered an ID of 10010, you would see the error message "Translation application ID must not end in zero."

The issue here is that APEX doesn't really use this as the application ID. Instead, this is used as the decimal portion of the application ID, with the original application ID used in front of the decimal point. In our example, the primary application has an ID of 101, so if we choose an ID of 1003 for the translated application, APEX will use the value of 101.1001 for the translated application. This is why you cannot use an ID that ends in zero: it would not be clear if 103.10010 referred to the ID of 10010 or 1001. This also explains why you don't see the translated applications in the traditional Application Builder interface.

For the mapping, you also choose the language that maps to this application ID. In the example in Figure 11-17, we are saying that when the language code is de, application 1003 should be used (or more precisely, application 101.1001 is used).

The end result of creating this mapping is nothing spectacular, as you can see in Figure 11-18. We created just one language mapping, but we could have created multiple mappings.

Translated application mapping

Figure 11.18. Translated application mapping

We now have a copy of the application that will be used if the user's browser is set to the de language code. However, we have not translated anything in the application yet, so the end user wouldn't see any difference (the text would still look like the original).

Seeding and Exporting the Translation Text

The first part of step 2 of the wizard lets you seed the translatable text, as shown in Figure 11-19. Here, you choose which language mapping you wish to use for your translation.

Seeding the translatable text

Figure 11.19. Seeding the translatable text

Seeding is the prerequisite for generating an XML Localization Interchange File Format (XLIFF) file. An XLIFF file contains all of the text in your original application and allows you to obtain the translations for the different language mappings you have created. XLIFF is an industry-standard file format that is used by many translation services to enable text in a document to be easily identified and isolated for translation purposes.

Note

The XLIFF file doesn't contain everything that would be seen in the application, such as some error messages. Also, it does not contain any data from underlying tables. You need to handle these items yourself. Handling messages is described in the "Translating the Standard Messages" section later in this chapter.

It is important to realize that APEX does not have the facility to automatically translate your applications for you. It just enables you to easily generate a list of all the text (or much of it) used in your application, which you will then need to translate (either yourself or through a translation service). Figure 11-20 shows the result of the translation list generation.

Translation List Generation

Figure 11.20. Translation List Generation

Once you have performed the seeding step, you can generate an XLIFF file, either for the entire application or for a specific page, as shown in Figure 11-21.

Generating XLIFF files

Figure 11.21. Generating XLIFF files

You can see in Figure 11-20 that you get some output from the seeding process that tells you the number of attributes (separate text strings) that might require translation (4821 in this example). The term attribute here refers to things like item labels, column headings, and so on.

You can also see in Figure 11-21 that you can either export all of the elements or export just those that require translation (if you've previously translated some, for example). The change in terminology between the word element and attribute can be a bit confusing, but essentially they're both referring to the individual pieces of text in the application.

So, let's take a look at a typical XLIFF file. The format of the XLIFF file follows a standard, so once you see how one XLIFF file works, you should understand how any XLIFF file works (regardless of the languages involved).

The header of the XLIFF file contains some comments that describe which application the file was produced for, the languages involved, and so on.

<?xml version="1.0" encoding="UTF-8"?>
<!--
  ******************
  ** Source     :  103
  ** Source Lang:  en-us
  ** Target     :  1003
  ** Target Lang:  de
  ** Page       :  1
  ** Filename:     f103_1003_p1_en-us_de.xlf
  ** Generated By: ADMIN
  ** Date:         03-DEC-2008 12:34:47
  ******************
-->

The rest of the file contains an XML document (all XLIFF files are XML documents that obey the XLIFF Document Type Definition) that describes the translatable text, as shown in Listing 11-6. For this example, we exported just a specific page (page 1), for the application mapping that we created earlier (103 >> 1003 (de)).

Example 11-6. Exported XLIFF File for a Page

<?xml version="1.0" encoding="UTF-8"?>
<!--
  ******************
  ** Source     :  101
  ** Source Lang:  en-us
  ** Target     :  1001
  ** Target Lang:  de
  ** Page       :  1
  ** Filename:     f101_1001_p1_en-us_de.xlf
  ** Generated By: TGF
  ** Date:         03-MAR-2011 06:59:40
  ******************

-->
<xliff version="1.0">
<file original="f101_1001_p1_en-us_de.xlf" source-language="en-us" target-language="de"
datatype="html">
<header></header>
<body>
<trans-unit id="S-5-1-101">
<source>Report Page</source>
<target>Report Page</target>
</trans-unit>
...

Note that we've included only the first lines of the XLIFF file in Listing 11-6. The actual file is more than 300 lines long, and that is just for one page, so you can imagine how big an XLIFF file for an entire application could be.

You can see that the file contains an XML fragment that describes each piece of text used in the application. (You don't really need to understand XML to see how this works; however, it does help if you have some XML knowledge.) For example, taking a snippet from Listing 11-6 (formatted as follows so it's easier to read):

<trans-unit id="S-5-1-101">

  <source>Report Page</source>
  <target>Report Page</target>
</trans-unit>

We have a section called trans-unit, which is our translation unit. Each unit has a unique ID (this allows APEX to associate a particular translation to the element in APEX). Inside each trans-unit section, we have the source text (the original text) and the target text (the translated version).

The interesting thing to note is that even if we use the exact same text in multiple places in the application, the XLIFF file will contain separate instances of that text. Take, for example, the "Report Page" text:

<trans-unit id="S-5-1-101">
<source>Report Page</source>
<target>Report Page</target>
</trans-unit>
<trans-unit id="S-5-1-101">
<source>Report Page</source>
<target>Report Page</target>
</trans-unit>

So even though the text is the same, each occurrence of the text is treated distinctly. You might think this is a bit wasteful. Why can't APEX just output the text once so that we would just translate it once? The reason is that it could be dangerous in certain circumstances to assume that just because the source text is the same, the target text will also be the same. Depending on your application, there might be other contextual information on the page, which means that given the same source text, you might want to provide different target text translations. By listing each occurrence of the text individually, APEX gives you the flexibility to either use the same translation or to provide a different one, depending on your exact situation.

The id attribute in the file also follows a standard convention. For the example, the id="S-5-1-101" breaks down as follows:

  • The first part of the id is typically always S.

  • The 5 is derived from the id column of the wwv_flow_translatable_cols$ table, which contains all translatable elements.

  • The 1 comes from the translate_from_id column of the wwv_flow_translatable_text$ table (from step 2 in the wizard).

  • The 101 is the application ID that is being translated.

So now we have the XLIFF file, and we need to translate the text in some way. As we mentioned earlier, you can either do it yourself (by using a standard text editor or a program that understands XLIFF files) or give the XLIFF file to another party to perform the translations for you. After you've translated the text, you will import the XLIFF file back into APEX. So, this is a three-step process, carried out as follows:

  1. Export all the source and target text from APEX.

  2. Modify the target text to whatever you choose (this is the manual process).

  3. Import the source and target text back into APEX.

Translating Text

If you're going to translate the files yourself, you can use a regular text editor, as long as it can save in Unicode. For example, you could modify

<trans-unit id="S-14-4239963891701920-103">

  <source>Search</source>
  <target>Search</target>
</trans-unit>

so that it reads

<trans-unit id="S-14-4239963891701920-103">

  <source>Search</source>
  <target>Suche</target>
</trans-unit>

This method of using a plain text editor works very well. Actually, you can use good old Notepad on your Windows machine as it can store files in Unicode format. However, we highly recommend using a program that actually understands XLIFF format, which makes the process much easier. For example, the LocFactory Editor (www.triplespin.com/en/products/locfactoryeditor.html), a Mac OSX tool, lets you easily see how many translations you have left to do, as shown in Figure 11-22.

Using an XLIFF editor

Figure 11.22. Using an XLIFF editor

After you provided the translations, you can resave the XLIFF file. You are then ready for the next step in the translation process, which is to import the XLIFF file back into APEX.

Applying Your Translation File and Publishing

After your XLIFF file is updated with translations, you move to step 4 of the wizard, as shown in Figure 11-23. (Note that we translated only one word in this example.)

Importing the modified XLIFF file

Figure 11.23. Importing the modified XLIFF file

Figure 11-23 shows the XLIFF file being uploaded. Note that we provide a title for the uploaded file so that we can identify the file to publish (which means to apply the XLIFF translation file to a particular translation mapping). Before you actually "publish" the application, you need to apply the translation using the link in the Tasks section, which is in the bottom right corner of the page, as shown in Figure 11-24.

Note

Although it seems a little out of place, you must use the "Apply Translations" link before publishing the application. If you skip this step, the wizard will complete without issue, but your translations will not show up in the application.

Apply Translation Link

Figure 11.24. Apply Translation Link

The Apply Translations page, as shown in Figure 11-25, allows you to select the XLIFF file you just uploaded and to identify to which application translation it should be applied. In this example, the XLIFF file is called f101_1001_en_de.xlf and the application translation is labeled 101 >> 1001 (de). When you click the "Apply XLIFF Translation File" link, the translation is actually done.

Apply Translations page

Figure 11.25. Apply Translations page

Following the translation, you are returned to the Publish Application page, as shown in Figure 11-26, where you once again set the Application Translation (101 >> 1001 (de)) and click the "Publish Application" button. This completes the overall translation process.

Applying the XLIFF translation file

Figure 11.26. Applying the XLIFF translation file

Once you have applied the translation file, you can publish the new application with the click of a single button, and you're finished (well almost, you still need to test it)!

Testing the Translation

Now you can change the locale of your browser to test your translation. Depending on the browser you're using, you can either do this directly in the browser itself, independent of the locale of the operating system, or change the locale of the operating system, as described earlier in the chapter. Using Firefox, for example, you can simply go to the Advanced Section in Preferences and say that you want to use the German language as your primary language. Once you have done that, you can view the page and be presented with the new (behind-the-scenes) translated version of the application, which uses the word "Suche" instead of "Search."

Figure 11-27 shows both the translated version and the original version (which uses the primary application by default) for comparison.

Comparing the original and translated versions

Figure 11.27. Comparing the original and translated versions

Now, while Figure 11-23 may not be earth-shatteringly exciting, it does demonstrate the powerful functionality of APEX's built-in translation features. One of the major benefits is that you can easily separate the task of building the application from the task of translating it. You can concentrate on building an application in your own native language, and then at some future point, just export the XLIFF file and send it off to be translated by a third party. You are also free to extend your application to add more translated versions as and when you need them. In other words, you are not forced to pick which languages you want to support at a particular time; you can always go back and retro-translate applications you wrote months or years ago to support new end users' native languages.

Translating On the Fly

As you have seen, you can use XLIFF files to translate much of the text used in your applications. However, your application may have other text that you want to translate on the fly, particularly text that might follow a standard format but include some runtime parameters or contextual information.

For example, let's say that we want to present the users with an information panel that welcomes them to the Buglist application and also displays how many bugs they currently have assigned to them, something like this

Hello John, you have 4 bugs assigned to you.

We could do this in a number of ways, such as using label fields and then translating the fields using the XLIFF method, but this could get a little messy, since we would need to break up the string into the parts that are dynamic (John and 4) and the parts that are static.

An alternative method that is better suited in this case is to use the text message translation feature available in Shared Components, as shown in Figure 11-28. This feature allows you to define a substitution string, along with parameters if needed, and then to define for which language to use that substitution string. You can define multiple language versions of the same substitution string.

Text message translation

Figure 11.28. Text message translation

We begin by creating a new text message that contains the English version of the message, as shown in Figure 11-28.

Creating the US English bug count message

Figure 11.29. Creating the US English bug count message

Notice that in the message itself, we have used %0 and %1 to represent the username and number of bugs assigned to the person, respectively. You can use up to ten of these variables to represent dynamic values in the text. Also notice that we needed to define the language that this message represents.

Now we need to provide the translated version of this message (again, we'll use German, although obviously the same technique applies to any language). We create a new text message, define the language to be German, and use the following text:

Hallo %0, du hast %1 hervorragende wanzen.

Note that the purpose here is not to get an exact translation, but to show the principle. You could actually have an entirely different message for the translated version. It's also worth mentioning that you can swap the %0 and %1 for languages with different grammar. The most important thing is that the message name should match the original name that you created—MSG_BUGCOUNT in this example.

After you've created the message, you can reference it in your application from wherever you want the text (translated or default) to appear. To do that, you can use the APEX_LANG.MESSAGE routine in the APEX_LANG package, which contains many routines related to language translations. The APEX_LANG.MESSAGE routine has the following signature:

APEX_LANG.MESSAGE (
    p_name    IN    VARCHAR2 DEFAULT NULL,
    p0        IN    VARCHAR2 DEFAULT NULL,
    p1        IN    VARCHAR2 DEFAULT NULL,
    p2        IN    VARCHAR2 DEFAULT NULL,
    ...
    p9        IN    VARCHAR2 DEFAULT NULL,
    p_lang    IN    VARCHAR2 DEFAULT NULL)
    RETURN VARCHAR2;

The parameters to this routine are fairly self-explanatory, and are as follows:

  • The p_name parameter is the name of the text message (which you just created).

  • The p0 through p9 parameters are the values you can pass in, which are represented by %0 through %9 in the text.

  • The p_lang parameter is the language you want obtain the text for (by default, this will be obtained through the language setting for the application).

The return result of the function is a string containing the text corresponding to the language (if you've defined text for the language parameter that is passed in) with any of the %0 ... %9 strings replaced by the p0 ... p9 parameters.

We can now create a new PL/SQL region on the page, which contains the following code:

htp.p(apex_lang.message(p_name => 'MSG_BUGCOUNT',

                        p0     => :APP_USER,
                        p1     => :P1_BUGCOUNT
));

We are using the htp.p procedure to output the return result of the APEX_LANG.MESSAGE function. Notice that we are using the APP_USER and P1_BUGCOUNT session state items to pass into the p0 and p1 parameters. (For the P1_BUGCOUNT item, you would just need to use a computation or default or other method to retrieve the number of bugs belonging to that user.)

Now if you run the application with your browser set to en-us, you should see the message displayed in the default language. If you set the browser language to German, you will see the translated version, as shown in Figure 11-30.

Displaying the translated text message

Figure 11.30. Displaying the translated text message

This is a very nice way of displaying very contextual and localized information to your end users. In a production system, you would probably want to cache this region to avoid the overhead of having to make the call to the APEX_LANG.MESSAGE routine until you really need to (for example, when the statuses of bugs are changed). You can also use the APEX_LANG.MESSAGE function in a SQL query to use data from the query to pass as the p0 ... p9 parameters.

If that's not enough, how about another fairly common scenario? Currently in the Buglist application, we use a LOV to display the list of statuses that can be assigned to a bug. However, rather than displaying "Open" or "Closed," we would like to display localized text. We can define a dynamic translation that will be applied to the LOV.

First, we need to create a table to store the list of statuses (so we can use a dynamic LOV instead of a static one).

jes@DBTEST> create table tbl_status(

  2  id number,
  3  status varchar2(20));
Table created.

jes@DBTEST> insert into tbl_status (id, status
  2  values (1, 'Open'),
1 row created.

jes@DBTEST> insert into tbl_status (id, status
  2  values (2, 'Closed'),
1 row created.

jes@DBTEST> commit;
Commit complete.

Next, we set up the dynamic translations, where we must create a mapping between the data that will be returned from the table and a particular language translation. Figure 11-31 shows the dynamic translation for the text "Closed" into the German "Geschlossen" (again, the purpose is not to provide the most appropriate translation, just to show how you can do it).

Creating a dynamic translation

Figure 11.31. Creating a dynamic translation

Now we need to create the LOV using another method in the APEX_LANG package that will map the text used depending on the language. The code used in the LOV is shown in Listing 11-7.

Example 11-7. Using Dynamic Translation

select

  apex_lang.lang(s.status) d,
  s.id r
from
  tbl_status s
order by d

Here, we use the WWV_FLOW_LANG.LANG function, passing in the value of the status column (Open or Closed). The LANG function then uses the dynamic translations we created earlier to retrieve the correct text based on the current language. The LANG function's signature is similar to that of the MESSAGE function (shown earlier):

FUNCTION LANG RETURNS VARCHAR2

Argument Name                  Type               In/Out Default?
------------------------------ ------------------ ------ --------
P_PRIMARY_TEXT_STRING          VARCHAR2           IN     DEFAULT
P0                             VARCHAR2           IN     DEFAULT
P1                             VARCHAR2           IN     DEFAULT
P2                             VARCHAR2           IN     DEFAULT
P3                             VARCHAR2           IN     DEFAULT
...
P9                             VARCHAR2           IN     DEFAULT
P_PRIMARY_LANGUAGE             VARCHAR2           IN     DEFAULT

Like the MESSAGE function, the LANG function allows you to pass in a parameter for the language to use, or else it defaults to the application language.

Now if you view the Buglist application using a German locale browser and look at the values in the status LOV, you should see the localized text, as shown in Figure 11-32.

Dynamic translations in an LOV

Figure 11.32. Dynamic translations in an LOV

In this fashion, you provide translations based on dynamic data, rather than static text. But obviously, you need to provide translations for the data you may wish to translate.

Translating the Standard Messages

As you've learned, you can use the XLIFF method to translate static text in your applications, and you can use routines in the APEX_LANG package to provide dynamic translations for other text. However, what about some of the built-in strings provided by APEX itself, which most applications will display?

For example, on the main page in the Buglist application, where we use a report to display the list of bugs, we have pagination enabled on the report. APEX uses some default text for the Next and Previous link labels. It would be very annoying if we translated every other part of the application and could not translate those messages, wouldn't it? Well, of course we can translate those messages. You do this using the same techniques already shown, but with a slight twist in that you need to know the correct syntax to translate a particular built-in message. The APEX help page lists the following built-in messages as translatable (search in the Managing Application Globalization section for the full list. as there are far too many to reproduce here):

  • FLOW.SINGLE_VALIDATION_ERROR - 1 error has occurred

  • FLOW.VALIDATION_ERROR - %0 errors have occurred

  • OUT_OF_RANGE - Invalid set of rows requested, the source data of the report has been modified

  • PAGINATION.NEXT - Next

  • PAGINATION.NEXT_SET - Next Set

  • PAGINATION.PREVIOUS - Previous

  • WWV_RENDER_REPORT3.SORT_BY_THIS_COLUMN - Sort by this column

  • WWV_RENDER_REPORT3.X_Y_OF_MORE_THAN_Z - row(s) %0 - %1 of more than %2

  • WWV_RENDER_REPORT3.X_Y_OF_Z - row(s)%0 - %1 of %2

  • WWV_RENDER_REPORT3.X_Y_OF_Z_2 - %0 - %1 of %2

To translate these messages, all you need to do is to create a new text message using the hard-coded string that represents the message for which you wish to provide a translation. Figure 11-33 shows an example for PAGINATION.NEXT.

Translating a standard message

Figure 11.33. Translating a standard message

You need to ensure you use the exact string name; otherwise, APEX will not find a match for it when it needs to display a standard message.

Now when you run the application, the standard Next link should be displayed with the relevant translation applied, as shown in Figure 11-34.

Displaying a translated standard message

Figure 11.34. Displaying a translated standard message

Note that you can also use the variables %0, %1, and so on to allow dynamic values to be substituted at runtime, as in this example:

FLOW.VALIDATION_ERROR - %0 errors have occurred

This allows you to completely change the format of the error message, for example

There were %0 errors.

This also means that if you don't like the format of some of the standard messages, you can adapt them, even if you use the same language for the application.

Summary

Generally, you either need to localize your application or you don't. In other words, it is either a requirement of the application or people don't tend to do it. Obviously, if an application is an internal system that won't be exposed to nonnative language speakers (whatever that language is), you probably don't need to even think about localizing it. However, if you are designing commercial systems that can be accessed by a wide variety of people, there can be great benefits in providing localization features in the application. For one thing, it can bring a whole new set of potential customers to your application. Additionally, it can really help to cut down on support issues (in terms of nonnative speakers misunderstanding the text).

APEX provides a lot of different but related features to enable you to translate your entire application. You can use techniques such as NLS settings to customize the way that dates, currencies, and so on are displayed.

However, we do urge you to get the right people to help with the translations when localizing your application. For example, we know of one case where 99.9% of the translation was fine, but one sentence from the original English text had been translated out of context, leading to a completely different meaning in Russian. That one small translation error resulted in a real financial cost to the company concerned, as the text in question was part of a legally binding contract.

So, just because the technology makes it easy, don't cut corners on getting your translations done correctly! If it's critical to your business, do a double translation, whereby you have the original text translated to your target language, then give that translated text to another group to translate back to the original source language (to see if there has been any context lost along the way). Most commercial translation services provide these sorts of double-translation checks.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset