Multilanguage database applications using Oracle APEX

Hello Everyone!

If you are a DBA/PLSQL guru but are not familiar with traditional web development, then Oracle APEX is one of the best option.

In this context, if you are trying to build a multilanguage application with Oracle APEX  then this article should give an overview and understanding of how the translation mechanism that is built-in to Oracle APEX works, and how you should handle it. It is based on Oracle Application Express 18.1 version.

Oracle Application Express

What is it actually?

Oracle Application Express is an Oracle product that enables developers to quickly create web-based applications on an Oracle database by using just a web browser.

Low code: The APEX tool runs in a web browser and allows the creation of web applications in a declarative manner, by simply selecting pre-built components, configuring them and bundling them up in a web application, all without the need to write too much code. Which means you can just focus on the problem at hand most of the development process is managed for you: security, authentication, database interactions, input validation, session state management and many other dependencies work out of the box.

Who is Using Oracle APEX?
Application Express is used by large and small customers alike, across a broad number of industries, and is used to solve a vast array of business problems – from simple spreadsheet replacement to multi-thousand user banking and insurance applications.

Is it expensive?
APEX is a no-cost option for those with an Oracle database license.

Multilanguage database applications using Oracle APEX

It is not a smooth way to design a database application that has to provide text data in several languages. But in Oracle APEX we can find it in easy way when we follow the translation process.

Why do we need to translate an application?

There are lots of reasons for applications to be multilingual. If you have end-users that are located internationally, you will probably end up supporting many languages or at least the most widely used ones.

There is also a lot more to take into consideration when deploying your application in various languages, such as date- and number-formats or languages printed right-to-left.

If you need your data to also be multilingual, then you will need to take care of it in your data-model and your application code.

Now we’ll focus on the translation process.

Oracle APEX handles translations as a copy of the main application. That means that for every language supported by the application, APEX internally creates a “Shadow Application”. These shadow applications aren’t listed in application builder, but they are exact copies of the main application and they exist in the APEX dictionary tables.

All code changes are done in the main application which is then rolled out to the translated shadow applications with the “seed” and “publish” operations. So whenever you need to roll out code-changes you need to seed and publish all translations.

The main step in understanding translations in Oracle APEX is to realize that there is always a copy of the full application (every page, every process, and every single line of code) for each language you want to support.

I am ready with my main application (is in English) and now I want to translate my complete application into desired (German) language.

The following three steps are required to create the multilingual APEX applications:

1. Prepare the application for the translation process
2. Generate user-friendly navigation bar entries for switching languages
3. Follow the translation process in globalization

 

1. Prepare the application for translation process:

  • Go to shared components block of the main application.
  • Go to the globalization attributes under globalization menu and set the property ‘application language derived from’ to ‘item preference (use item containing preference)’.

APEX now knows that it can derive the language to show the page in from a special application item. But this item still has to be created.

Now in order to create an item for the preference, the following steps are required:

  • Go to shared components
  • Under application logic menu
  • Click on application items -> Create
  • Enter the name as FSP_LANGUAGE_PREFERENCE
  • Under Security select Checksum Required -Session Level
  • Click on Create Application Item

The item is called FSP_LANGUAGE_PREFERENCE, because APEX recognizes that name as an item reserved for application languages. When a page is rendered, APEX checks the FSP_LANGUAGE_PREFERENCE item to see in what language the page has to be shown.

Whenever the language is changed, the page has to be reloaded to show the result. To make this happen, we are going to add an application process that will handle the reloading of the page.

Create application process:

  • Return to shared components
  • Under application logic menu -> click on application processes -> Create
  • Enter the name as set_language
  • Set ‘process point’ as ‘On Load: Before Header (page template header)’
  • Click on Next
  • In the source block enter below PL/SQL code
begin
owa_util.redirect_url('f?p='||:APP_ID||':'||:APP_PAGE_ID||':'||:APP_SESSION);
end;
  • Click on Next
  • Select condition type Request = Expression1
  • Enter LANG in Expression 1 text area (‘LANG’ all in upper case)
  • Click on Create Process to finish

Now our application is ready with the application process and with language-specific Item.

 

Let’s add something much easier for users to switch between languages.

 

2. Generate user-friendly navigation bar entries for switching languages:

Now give some user-friendly options in the application like user can change application language with one click, for that we need to create some navigation bar entries with language options.

How to create navigation bar entries?

The best place to create a language switch is a place that is visible at all times. In this case, we will use the navigation bar. Since this bar is in the header of our application, it is visible on all pages and ideal for our purposes.

Create navigation entries:

  • First, go to shared components
  • Under navigation find navigation bar list
  • Click on Navigation Bar List -> Desktop Navigation Bar -> Create Entry

 

List Entry Label: Deutsch (enter the navigation bar name)
Target type: page in this application 
Page: &APP_PAGE_ID. ('.'required)
Request: LANG
Set these items: FSP_LANGUAGE_PREFERENCE
With these values: de (language code)
  • Click on Create List Entry

Repeat same steps to create more navigation menu entries for required languages.

Now that we have a translatable application, we can start on the translation itself. The application is still only available in english, so we will have to create a translated version of the application in another language.

 

3. Follow the translation process in globalization:

The translation workflow consists of a couple of steps, which may be repeated each time you roll out a new version of the application.

Translation workflow:

  • Go to shared components
  • Go to the translate application under globalization menu

Under the translate, one can see the below-mentioned flow of translation process.

a). Define application languages (map the primary and target application IDs)

  • Define application languages -> Create
  • Enter the unique ID for translated application

(Tip: To avoid conflicts with other applications in the same workspace, we recommend you to extend the unique ID of the translated target application from the main application ID.

For example, the ID of the main application is 105 assigned for the target applications as 10501, 10502, etc.)

  • Next select language to which you want to translate then finish with Create click

This will tell APEX the new language that is going to be used.

 

b). Seed translatable text (seed and export the text to a file for translation)

  • Go back to translate page and then click on seed translatable text
  • Check the translated application ID and then click on seed button

This will create entries in the APEX translation repository for every translatable text in the application. These entries will be used later to generate the XLIFF files.

What is XLIFF file?

One of the world’s most recognized standards in localization is the XLIFF format. XLIFF is the abbreviation for XML Localization Interchange File Format. As the name explains, it’s an XML standard and it has been adopted by Application Express as the format in which different language files can be exported from and imported into APEX applications.

c). Download XLIFF translation files

  • Again go back to translate page click on download XLIFF translation files
  • Select desired language from the list of languages

In this page one has two options to export XLIFF file: one can choose a page in the application or entire application

  • Click on Export XLIFF file for the application and save under your local directory (this XLIFF file contains XML data)

The XLIFF file has now been downloaded and is ready to be edited.

The quickest way to translate a lot of text is still using the XLIFF file. Because this is an international standard, an XLIFF file can be handled by any translation service. An XLIFF file is always built the same way: a header and a body. The first part of the header shows some information about the file, the original application, and the translation target application. Because this is commented, it will not be used by a processing application.

The second part of the header shows the version of the XLIFF standard that is used. The latest specification version is 2.0, but APEX uses 1.0 by default.

The third part of the header holds some more information about the file, the source language, and the target language. After the header, the body starts.

The following part is repeated for every text in the APEX translation repository: an identification for the item that holds the text, a source in the original language, and a target in the new language. Finally, the file is closed.

Tags for source and target language coded as <source> <target>

<!-- 
  ****************** 
  ** Source     :  105
  ** Source Lang:  en
  ** Target     :  10501
  ** Target Lang:  de
  ** Filename:     f105_10501_en_de.xlf
  ** Generated By: Developer Name
  ** Date:         06-MAY-2018 08:59:59
  ****************** 
 -->
<xliff version="1.0">
<file original="f105_10501_en_de.xlf" source-language="en" target-language="de" datatype="html">
<header></header>
<body>
<trans-unit id="S-5-1-105">
<source>Department</source>
<target>Department</target>
</trans-unit>
<trans-unit id="S-5-21-105">
<source>Test it?</source>
<target>Test it?</target>
</trans-unit>
</body>
</file>
</xliff>

d). Translate the text in the file (third party job) 

But for now, let’s do some small changes in XLIFF file.

Open your downloaded XLIFF file for editing in your favorite text editor and change the required target text into the German language.

Example:

<source>Department</source>

<target>Abteilung</target>

<source>Test it?</source>

<target>Probier es aus?</target>

Then save it and follow the next step.

e). Apply XLIFF translation files

  • Then click on Upload Files (upload the changed file)
  • Map the target application check it and click on Apply Checked Button

f). Publish the translated file:

  • Go back to translate page and click on Publish translated applications
  • Check the files and click on Seed Button
  • Check the files and click on Publish Button

Now one can check the source and target language data in translation repository. For that again navigate to translate window then click on Translation Repository (Shared Components -> Globalization -> Translate Application -> Translation Repository). There one can edit and reset the target data.  (For that one just need to spend some more time).

Now the application is available in two laguages for the user.

Just Try it and enjoy with Multilingual APEX app!

Conclusion:

When we follow the translation process in APEX  it’s really a smooth way to get a Multilanguage application.

One thing that you will always have to keep in mind is every time something is changed in the application, you will have to repeat the process of seeding, exporting, translating, and importing. Otherwise, not all changes will be visible in the translated version of the application.

So when the translating itself is done by an external company, it would be wise to send them the XLIFF file after the application in the default language is completely done.

Reference Book: Oracle APEX Cookbook Second Edition,  BY Marcel van der Plas, Michel van Zoest

Blog: Multi – Language database applications using Oracle Apex by Peter Raganitsch

Swathi Mary Katakam
Database Developer
Swathi is an Oracle Database and Application Developer. Recently she started developing Database Applications using Oracle APEX and she is always eager to learn and implement new ideas to do her work more productively and innovatively.