Building Multi-Lingual Applications

Dr. Paul Dorsey, Dulcian, Inc.

I. Overview

As we move into an environment of increased globalization and centralized systems, there is more pressure to support groups of users speaking different languages. This has been a problem in the IT community for a long time; but there does not seem to be any particularly good industry standard for building and supporting multi-lingual applications. This is not to say that there have not been any successful multi-lingual systems, but, most often, these systems have a narrow scope, are of limited functionality (few languages, few multi-lingual fields, etc.) and are very difficult to build and maintain.

This paper will discuss strategies for supporting the development of multi-lingual applications with examples of utilities that Dulcian has built to solve some of the associated problems. It will also include ways in which you can adapt the methods and ideas presented here to create your own multi-lingual applications.

Supporting multi-lingual applications actually requires solving two completely independent problems:

A. Creating the look and feel of the multi-lingual application itself including labels, help and error messages.

B. Making the underlying data itself multi-lingual. For example a product name must be stored in multiple languages so that it can be purchased easily by different language speakers.

Both of these problems will be discussed along with alternative strategies and solutions for each.

II. Application Look and Feel

This section will discuss the application look and feel issues that arise when creating multi-lingual applications.

A. Strategies

There are two basic strategies that can be employed to handle the look and feel issues associated with building multi-lingual applications. First, different versions of the application can be created for each language. Second, you can create a single application that modifies itself based upon the active language. Both strategies have their respective pros and cons.

1. Multiple Application Strategy

The multiple application approach allows for complex control of screen layouts. You can completely customize the application to have the desired look and feel. This is not to say that you need to build completely independent applications. Products such as Oracle’s Translation Builder allow you to take a single application and pass it through a translation dictionary to generate the multi-lingual version. (Note: Translation Builder has not met with great user acceptance or commercial success and may no longer be supported.)

The applications developed using this approach tend to have better performance than those created using a single applications approach since an application that modifies itself based on the selected language requires some amount of overhead when the form is accessed.

The disadvantages of this approach are numerous and greatly outweigh the advantages:

· Need to maintain a separate version of each application for each language supported – For example, this means that for two languages, the maintenance requirements are doubled. Even using Translation Builder, another step must be added to the deployment process in order to translate the application

· Supporting a large number of languages with many applications quickly becomes unmanageable.

In general, this is not an optimal solution. It is much more efficient to build a single application that is modified at runtime for each language.

2. Single Application Strategy

The basic idea behind this approach is to place all of the information that must be translated into a repository. The application accesses the repository on the fly and modifies itself.

NOTE: If performance is of paramount importance, this same repository can be used to generate a library routine to handle text translation with negligible performance impact. However, experience has shown that such a strategy is unnecessary in most cases.

The overwhelming advantage to a single application strategy is that, as you make changes to the repository, no additional effort or deployment is required. The next time that the application is accessed, the repository will automatically update the application. Therefore, the core functionality of the application can be written while all of the labels, help and error messages can be versioned and maintained after the core application is deployed. No changes to any text object will require a re-deployment. In terms of ease of maintenance for developers, this is a far better solution.

The disadvantages of using a single application include some performance impact and the inability to customize the layout based upon the selected language.

There may also be problems when moving from language to language since the number of characters needed to represent an idea in one language may vary greatly across other languages. Unfortunately, our experience is that English tends to be one of the more compact languages. Therefore, a screen layout that attempts to use space frugally and is designed in English may not leave adequate space for labels in other languages. Label height may also differ since some languages use accent marks on capital letters, which require additional vertical space. For all of these reasons, when designing multi-lingual applications, GUI standards must be carefully set in order to allow the system as much flexibility as possible in a multi-language environment.

In weighing all of the pros and cons of both strategies, building a single, flexible application is clearly superior for handling multi-lingual application text.

B. Creating a repository

Using the idea of a repository for any multi-lingual application being built, there are two approaches that can be taken in structuring the language repository: linguistic translation and logical element translation.

1. Multi-lingual Dictionary repository

Using a translation repository involves building the application in a single language and using translation strings, which are looked up in a multi-lingual dictionary and converted to the selected language. Some developers are satisfied with this approach; however, I believe that this strategy is conceptually flawed since one word may represent two logically different items when translated into a different language. It isn’t always clear what word to use in translation without the use of context. For example, the word “record” in English can be used as a verb (meaning to set down in writing or register on some medium), noun (meaning a piece of paper documenting something or a record album) or adjective (something that surpasses others of its kind). The author found an example of mis-translation of this word on a French website where the intended meaning was a record album and the translated word “enregistrement” is the verb use of “record” meaning to document. Using this strategy in a large system with thousands of labels is likely to lead to translation anomalies where different contexts require different translations. Judgment must be used to determine whether or not this is a viable strategy for a specific system, given the number and types of labels and text used.

Another disadvantage to a translation repository is that there is no convenient way to handle error and help messages. Since these messages must be treated as one lexical element which must be looked up, error and help messages are much better suited to a logical element approach. Otherwise, indexing problems can lead to significantly slower performance.

2. Logical Element Translation Repository

Creating a repository of all of the translatable elements in the application along with their translated values solves many of the problems encountered in the linguistic translation repository. The basic data model to support this strategy is shown in Figure 1.

Figure 1: Logical element translation data model

It should be noted that there is one advantage to the dictionary strategy that is lost with this type of approach. A system may include a field with a label such as “Department Name,” which is used in 300 different places and means exactly the same thing each time. Using the model above, this label will need to be translated 300 separate times. To solve this problem, it is useful to be able to logically group the objects so that items referring to the same semantic object will all be labeled in the same way. This approach has another advantage. Once logical objects have been identified, labeling consistency in each language can be enforced throughout the system. To support this extended requirement, the repository needs to be extended as shown in Figure 2.

Figure 2: Extended repository data model

Using this model, if there is no translated value defined for a specific object, the value is derived based upon its logical group.

Translated values for all items in all languages may not exist. If this is the case, displaying the property value in the best language available can be done. For example, a Canadian application to be deployed in Quebec requires a Québécois label set. However, if a particular value is not available in Québécois, then the value should be displayed in French. If a French value is unavailable, it should be displayed in English. Therefore, the language elements in the repository require a recursive link (not shown in the previous diagram) so that the language rollup hierarchy search path can be established if a value is not found.

Generalizing the Repository Strategy

Once we had thought through the multilingual problem for Oracle Forms and multi-lingual values with a relatively minor extension to the repository, it was possible to support any product allowing modification of item properties at runtime. As long as properties were being modified, more than just text could also be modified. In fact, for multi-lingual values, simply modifying text is not enough. The character set must also be changed. In Forms, this entails modifying the visual attributes of the item.

Using this same logic, the repository could be further extended to support the modification of any properties at runtime for any reason (not just multi-lingual translation). Thus, in attempting to support a multi-lingual problem, we inadvertently solved the broader problem of application customization using runtime modification. The details of this solution are beyond the scope of this paper. However, it is not a large leap to move from the repository model shown in Figure 2 to one which would support a much broader solution.

To successfully implement this vision, some properties must be set for a specific item (e.g. label) or a logical group of items. The font for all labels will likely need to be changed all at once. This involves setting properties for items, logical groups of items and types of items using another repository extension as shown in Figure 3.

Figure 3: Repository extension for items, groups of items and types of items

Using this repository, there are three types of items handled differently by Oracle Forms: Labels, Help messages and Error messages.

Labels

When a form is opened, a series of SET-ITEM_PROPERTY commands is executed based upon a single query of the repository. Even large forms require less than 1 second to open. If using a large, multi-tabbed form, the algorithm can be modified to only change the visible fields on the current tab in order to improve performance. The code to support the population of labels is as follows:

PROCEDURE change_attributes(p_System_id NUMBER

,p_Label_Set VARCHAR2

,p_Menu_Label_Set VARCHAR2)

IS

v_Form_Name VARCHAR2(30) := name_in('system.current_form');

v_Form_id ml_obj.obj_id%TYPE;

v_Label_Set VARCHAR2(64) := p_Label_Set||',';

v_Label_Set_1 ml_label_set.label_set_cd%TYPE := '!';

v_Label_Set_2 ml_label_set.label_set_cd%TYPE := '!';

v_Label_Set_3 ml_label_set.label_set_cd%TYPE := '!';

v_Label_Set_4 ml_label_set.label_set_cd%TYPE := '!';

CURSOR cur_Attributes IS

SELECT obj.obj_id obj_id

,def.name_tx object_type

,obj.name_tx object_name

,doa.descr_tx attribute

,ov.value_tx attribute_value

FROM ml_def_obj def

,ml_def_obj_attr doa

,ml_obj obj

,ml_obj_value ov

,ml_label_set ls

WHERE doa.def_obj_id = def.def_obj_id

AND obj.def_obj_id = def.def_obj_id

AND ov.object_id = obj.obj_id

AND ov.def_obj_attr_id = doa.def_obj_attr_id

AND ov.label_set_cd = ls.label_set_cd

AND doa.descr_tx <> 'HINT_TEXT'

AND obj_Id IN (SELECT obj_id

FROM ml_obj

CONNECT BY PRIOR obj_ID = obj_ID_RFK

START WITH obj_ID_RFK = v_Form_Id)

AND (ov.Label_set_cd = v_Label_set_1

OR ov.Label_set_cd = v_Label_set_2

OR ov.Label_set_cd = v_Label_set_3

OR ov.Label_set_cd = v_Label_set_4)

ORDER BY ls.precedence_nr

,decode(doa.descr_tx,'ENABLED',998

,'VISIBLE',999

,1);

BEGIN

default_value('!','global.base_label_set');

default_value('!','global.base_system_id');

IF p_Label_Set = name_in('global.base_label_set')

AND p_system_id = to_number(name_in('global.base_system_id')) THEN

RETURN;

END IF;

IF multilang_pkg.valid_label_set(p_Menu_Label_Set,p_system_id) THEN

Change_Menu_Labels(upper(name_in('global.menu_name'))

,p_Menu_Label_Set

,p_system_id);

ELSE

message('Label Set '||p_Menu_Label_Set||' is not valid for system '||to_char(p_system_id));

message(' ');

END IF;

Populate_Attribute_Table;

v_Form_id := Multilang_Pkg.Form_Id(p_system_id

,v_Form_Name);

IF v_Form_Id IS NOT NULL THEN

v_Label_set_1 := substr(v_Label_Set,1,instr(v_Label_set,',')-1);

v_label_set := substr(v_Label_Set,instr(v_Label_set,',')+1);

IF v_Label_Set_1 = name_in('global.base_label_set') THEN

v_label_set_1 := '!';

ELSIF NOT multilang_pkg.valid_label_set(v_Label_Set_1,p_system_id) THEN

message('Label Set '||v_Label_Set_1||' is not valid for system '||to_char(p_system_id));

message(' ');

END IF;

FOR attr_cur IN cur_Attributes LOOP

IF attr_cur.attribute <> 'HELP_TEXT' THEN

IF attr_cur.object_type = 'FORM' THEN

set_form_attributes(attr_cur.Object_Name

,attr_cur.attribute

,attr_cur.attribute_value);

------ other types of objects

ELSIF

attr_cur.object_type = 'BLOCK' THEN

set_block_attributes(attr_cur.Object_Name

,attr_cur.attribute

,attr_cur.attribute_value);

ELSIF

Instr(attr_cur.Object_Name,'.') <> 0 THEN

set_item_attributes(attr_cur.Object_Name

,attr_cur.attribute

,attr_cur.attribute_value);

END IF;

END IF;

END LOOP;

ELSE

Message('Form '||v_Form_Name||' does not exist in system '||to_char(p_system_id));

Message(' ');

END IF;

END change_attributes;

Help Messages

In the repository, help for an item is treated as any other property. The help property is implemented differently. At runtime, the current item is captured and help for that item is queried. In the repository, help exists at the form, block and item levels. Help is accessed either through a context sensitive help button or primitive query screen

Error Messages

Error messages are treated as items at the form level with a single property (the error message). A simple function traps the error code and calls the appropriate error event to display the appropriate message. The code is shown below:

FUNCTION Get_Error_Message(p_Error_Id VARCHAR2

,p_Label_Set VARCHAR2

,p_systm_ID NUMBER DEFAULT NULL)

RETURN VARCHAR2

IS

v_Return_value ml_obj_value.value_tx%TYPE;

CURSOR cur_obj_value IS

SELECT ov.value_tx

FROM ml_obj_value ov

,ml_def_obj_attr doa

,ml_obj obj

,ml_def_obj dobj

WHERE ov.obj_id = obj.obj_id

AND obj.def_obj_id = dobj.def_obj_id

AND obj.name_tx = p_Error_id

AND dobj.name_tx = 'ERROR_MESSAGE'

AND ov.def_obj_attr_id = doa.def_obj_attr_id

AND doa.descr_tx = 'MESSAGE_TEXT'

AND label_set_cd = p_Label_Set

AND obj.systm_id = nvl(p_Systm_id,systm_id);

BEGIN

OPEN cur_obj_value;

FETCH cur_obj_value

INTO v_Return_Value;

CLOSE cur_obj_value;

RETURN v_Return_Value;

END;

Populating the Repository

Populating the multi-lingual repository is a daunting task. Every item where language will be manipulated must be placed into the repository. Large forms may contain hundreds of items. Therefore, we created a utility to run against a compiled form to look through all of the blocks and items and write their names to the repository. This task could be accomplished more cleanly using Forms APIs, but our utility worked adequately for our needs. An occasional form required slight modifications before the utility was run if complex restrictive data validation or WHEN-NEW-BLOCK-INSTANCE triggers were present. Also, in Forms, neither boilerplate text nor frame labels can be manipulated at runtime. However, display objects work as reasonable surrogates for both items. The only insurmountable limitation in Forms is runtime manipulation of text displayed at an angle.

The code to populate the repository is shown below:

PROCEDURE Populate_Tables_From_Form(p_systm_id NUMBER DEFAULT NULL

,p_label_set_cd VARCHAR2 DEFAULT NULL)

IS

v_Form VARCHAR2(100) := name_in('system.current_form');

v_Block VARCHAR2(100) := get_form_property(name_in('system.current_form'),FIRST_BLOCK);

v_Item VARCHAR2(100);

v_Last_Item VARCHAR2(100);

v_Item_Id ITEM;

v_Canvas VARCHAR2(100);

v_Lov VARCHAR2(100);

v_Tab_Page VARCHAR2(100);

v_Vattr VARCHAR2(100);

BEGIN

pv_systm_id := nvl(p_systm_id,name_in('global.systm_id'));

pv_label_set_cd := nvl(p_label_set_cd,name_in('global.label_set'));

Empty_Tables;

Populate_Attribute_Table;

multilang_pkg.insert_object(v_Form

,'FORM'

,v_Form_Object_Id

,v_Form_Def_Obj_Id

,pv_systm_id

,pv_tool_cd

,v_Form_object_id);

-- Loop through the blocks

WHILE v_Block IS NOT NULL LOOP

v_Item := v_Block||'.'||get_block_property(v_Block,FIRST_ITEM);

v_Last_Item := v_Block||'.'||get_block_property(v_Block,LAST_ITEM);

v_Item_Id := find_item(v_Item);

v_Block_Object_Id := v_Form_Object_Id;

v_Block_Def_Obj_Id := v_Form_Def_Obj_Id;

multilang_pkg.insert_object(v_Block

,'BLOCK'

,v_Block_Object_Id

,v_Block_Def_Obj_Id

,pv_systm_id

,pv_tool_cd

,v_Form_Object_id);

IF nvl(get_block_Property(v_Block,CURRENT_RECORD_ATTRIBUTE),'DEFAULT') NOT IN ('DEFAULT','CUSTOM') THEN

multilang_pkg.Insert_Def_Obj_Attr_and_Value('CURRENT_RECORD_ATTRIBUTE'

,'Y'

,get_block_Property(v_Block,CURRENT_RECORD_ATTRIBUTE)

,v_Block_Def_Obj_Id

,v_Block_Object_Id

,pv_label_set_cd);

END IF;

multilang_pkg.Insert_Def_Obj_Attr_and_Value('INSERT_ALLOWED'

,'Y'

,get_block_Property(v_Block,INSERT_ALLOWED)

,v_Block_Def_Obj_Id

,v_Block_Object_Id

,pv_label_set_cd);

----other privileges

-- For each block, loop through the items

LOOP

-- If item is on a canvas, process the window and canvas

v_Canvas := get_item_Property(v_Item_Id,ITEM_CANVAS);

IF v_Canvas IS NOT NULL THEN

IF Canvas_Is_First_Occurence(v_Canvas) THEN

canvas_table(nvl(canvas_table.last,0) +1) := v_Canvas;

insert_canvas_and_Window(v_Canvas);

END IF;

-- If item is on a tab page, process the tab page

v_Tab_Page := get_item_Property(v_Item_Id,ITEM_TAB_PAGE);

IF v_Tab_Page IS NOT NULL THEN

IF Tab_Page_Is_First_Occurence(v_Tab_Page) THEN

tab_page_table(nvl(tab_page_table.last,0) +1) := v_Tab_Page;

insert_tab_page(v_Tab_Page);

END IF;

END IF;

---other item types

END IF;

Insert_Item(v_Item_id

,v_Item);

END IF; --(v_Canvas IS NOT NULL )

EXIT WHEN v_Item = v_Last_Item;

v_Item := v_Block||'.'||get_item_Property(v_item_id,NEXTITEM);

v_Item_Id := find_item(v_Item);

END LOOP;

v_Block := get_block_property(v_block,NEXTBLOCK);

END LOOP;

END Populate_Tables_From_Form;

C. Look and Feel Implementation

Dulcian spent a lot of time solving this difficult multi-lingual problem. Depending upon your requirements, the full solution described in this paper does not need to be built. A dictionary or modified dictionary may suffice. Assuming that every item with the same name is semantically the same greatly simplifies the repository structure.

Using an item-based approach, the code shown above can be modified to populate your repository. Doing this by hand is not feasible. It is suggested that labels, help and error messages should all be handled at the same time.

III. Multi-Lingual Data

Storing the values of data simultaneously in multiple languages is a difficult problem to solve. Not only does it convolute the data structure, but is also makes application design much more complicated. With multi-lingual data systems, the failure rate is very high. Very skilled development teams are required to successfully implement complex multi-lingual applications.

A. Approaches

In the past, various strategies have been used to build these systems. Three strategies to support multi-lingual data will be discussed. First, you can include a separate column in the physical table for each multi-lingual translation of an attribute. For example, in the traditional Oracle Scott/Tiger Department table, if the DName and Loc attributes are multi-lingual and the languages supported are English, French and German, the DEPT table would look like the following:

DeptNo

DName_ENG

DName_FRN

DName_GRM

Loc_ENG

Loc_FRN

Loc_GRM

For every multi-lingual attribute, there would be one column for each possible language of interest. This could mean that physical tables might include hundreds of columns. Since the 128 column limit was removed as of Oracle8, this is no longer an issue. Simply placing translated columns into tables is not a real solution. The issue is how to get the application to function appropriately.

Dulcian handled this by embedding this capability into our rules engine.

The multiple application strategy has the advantage of fast performance but application development is more challenging. Also, if you want to access all of the translated values for a particular logical item, this information would need to be hard coded into the application. Alternatively, if the number of columns and languages is not too large, the table can be treated as any other table and the information can be displayed and maintained in a form.

If you want to hide the complexity of multi-lingual information in the form, you can place a view on top of the table that uses DECODE statements to display the appropriate column and INSTEAD-OF triggers to update the column in the table. This solution may sound simple; however, there are some significant problems with it such as the situation when a user operating in a single language accesses a department record and wants to change a department name. A change made to a field can be of two types:

1. The logical value of the attribute can be changed.

2. The translated value can be changed (leaving the logical value unchanged).

In the first case, the appropriate behavior is simply to change the value of the field. In the second case, there will be inconsistent data in the database requiring either an immediate translation of the new value, or some type of flag to mark values as invalid so that they can be updated at a later time.

This situation raises the question: How will these multi-lingual attributes be maintained? The answers are the same alternatives as the ones for handling labels and help/error messages. Should you use a dictionary lookup and translation strategy or manually change the values? If the manual alternative is selected, is the person who sets the initial value the same person as the one who selects the translated value? What is the process for doing this? Depending upon how these questions are answered, you may want to add a Valid Language Code column to the table to declare that one value is correct and others are suspect.

The second approach is to maintain a separate copy of the record for each language in which it is stored. However, this approach seems to be logically flawed. Adding a single column to the table for the language code attached to the primary key of the table means that when the record is entered with multi-lingual values (by whatever process), a different record for each language is entered into the table. For example, using the Scott/Tiger Dept-Emp structure, three records would be created: one each for English, French and German. Any non-multilingual values would be repeated in each record.

This mechanism has obvious locking issues. Any time that a non-multi-lingual value is updated, it must be done in multiple places. Tables will have many more records. For example, using five languages, there would be five times as many records in each table. Queries also become for complex in this scenario.

Supporting applications for a single language is easy, as is accessing the alternate language values for any particular object. The same multi-lingual update issue still exists but this strategy is designed to make application development simpler. In most cases, this strategy is superior to the first from an application development perspective.

In the third approach, multi-lingual attributes are removed from the table entirely and placed into their own associated fact table. The data model to support this strategy is shown in Figure 4.

Figure 4: Generic Data Model

With this model, the advantage is that all of the values are nicely encapsulated. The multi-lingual values, single records and multi-record blocks can all be easily displayed. A view can be created to support an active language. The main drawbacks involve querying and reporting. In a large table with many multi-lingual columns, running a query that filters by a half-dozen different multi-lingual values is filtering on a 7-table join where a multi-lingual value is referenced six times is not ideal. Queries needing to join to the Dept. table displaying all relevant columns require retrieving from a very large joined value table. Even though this solution is conceptually cleaner, it often results in a system architecture with unacceptable performance for some critical operations.

A remedy for this problem is to combine Strategies 1 and 3 and store the information redundantly using a materialized view. This information would be stored officially using Strategy 3 but the table structure of Strategy 1 would be retained as a materialized view. Unfortunately, materialized views in this situation did not work. Such an alternative could be employed using database triggers. Application developers need to be careful to perform updates through the generic structure and only use the “flattened” Strategy 1 structure for query performance.

B. Implementation

The solution to the multi-lingual application problem that Dulcian uses involves our business rules engine. A full description of this engine is beyond the scope of this paper. What is generated from our rules engine is the environment created by the combination of Strategies 1 and 3. All of the standard developer DML goes through views written on the Strategy 1 structure which creates object scripts processed by our rules engine. Behind the scenes, the engine updates the flat and generic structures, placing all objects into a handful of tables. An overview of the architecture can be found in the article “Business Rules: The Next Paradigm Shift” on the Dulcian website (www.dulcian.com).

The conclusion reached regarding multi-lingual applications with data stored redundantly using flat (Strategy 1) and generic (Strategy 3) structures is the only way to deliver both a reasonably civilized development environment and adequate performance. By selecting Strategy 1 or 3 alone, one of these factors will suffer. By using a combined strategy, you can provide an environment where developers can build applications without ever considering the multi-lingual aspect. The multi-lingual dimension of the application can be added at a later time.

How Can You Use This Approach?

For space reasons, this discussion has mainly used Oracle Forms as an example. However, with some modifications, the concepts presented here can he easily adapted to other environments such as Java or other products such as Oracle Reports.

Without following the entire integrated rules engine strategy used at Dulcian, you can handle multi-lingual data by using combined Strategies 1 and 3 with flat and generic data co-existing. The steps that need to be followed include:

· Forcing all DML through the generic structure (INSTEAD-OF triggers on the alternate views can also do this)

· For the first system version, building applications without considering the multi-lingual aspect.

· Assigning a senior developer to provide a generic Forms widget to maintain the multi-lingual values for an object’s particular attribute.

About the Author

Dr. Paul Dorsey is the founder and president of Dulcian, Inc. (www.dulcian.com), an Oracle consulting firm that specializes in business rules-based Oracle Client-Server and Web custom application development. Paul is co-author with Peter Koletzke of Oracle Press’ Oracle JDeveloper 3 Handbook, Oracle Designer Handbook, Oracle Developer Forms and Reports: Advanced Techniques and Development Standards and with Joseph Hudicka of Oracle Press’ Oracle8 Design Using UML Object Modeling. Paul won the Chris Woolridge Outstanding Volunteer Award at IOUG-A-Live! 2001 and was one of six initial recipients of an Honorary Certified Oracle Master award from Oracle Corporation at OOW 2001. Paul is the Executive Editor of SELECT Magazine and is President of the NY Oracle Users’ Group.