Disclaimer: This article was updated with new content on July 11, 2022.

No matter where you live and what language you speak, it’s very probable you’ll sooner or later need to create a system or application that’ll be used by users in different countries. This is the way of things now – the world is much more globalized than a few decades ago. Moreover, the rise of the importance of remote work during the Covid-19 pandemic only emphasized what was already quite apparent before, as some companies started hiring people from different parts of the world on a massive scale.

This means that we all need tools that can help with app localization (as it’s not just a matter of the language, but also cultural norms) and translation. Sometimes they can even allow you easily accommodate more than one language at the same time. What are they? How to use them? Read on to find out.

Do you really need to deal with this?

This is a question you might ask yourself quite often. It might be tempting to just create your apps in English and be done with it. However, there are some scenarios when application localization is needed:

• Law requirements – even if your app is designed for one region, in some places – like Canada, Switzerland – law requires systems to work in more than one language
• International companies – companies with branches all over the world – Amazon, Google, etc. – need IT solutions that can accommodate their business
• Projects for international organizations – i.e. ONZ, UE. It’s a mix of the previous two points
• SaaS – these kinds of services are usually offered globally

Considering all this, I’d go as far as to say that translating/localizing your app isn’t a choice – it’s a must. Especially if you factor in the statistics of language use among Internet users.

Different approaches to translating and localizing apps

In general, there are three different approaches to making your application accessible for people from different parts of the world:

• Translation – you can simply translate the language
• Localization – you can try to make your app more local-like, which means adhering to various cultural norms and rules of a given country or region
• Globalization – you can opt to make your solution universal, so that it’ll be well understood and user-friendly for people from different parts of the world

Each of them has its benefits and drawbacks.

Translating applications in APEX

Technically, Oracle Application Express (APEX) supports all the languages in the world (including the ones with right-to-left text direction) but out of the box, it’s translated into 31 languages, including English, German, Spanish, French, Italian, Japanese, Korean, Brazillian Portuguese, Simplified Chinese, and Traditional Chinese, among others. You can find the full list on APEX’s website.

If you want to use APEX in another language, there’s a way to do that – I’ll tell you how to do it in a moment.

The entire concept of translating apps in APEX is based on so-called shadow applications. To put things simply, you have one “main” application (in English, for example), and you create a copy of your application each time you translate it into a new language. The rest of the app stays exactly the same. APEX knows the mapping which allows it to change the version of the displayed application depending on language settings.

You define this mapping using the Define application languages option in Shared Components->Translate. Aside from the language and document direction, it also allows you to specify a different image directory, so that you can use translated images in your shadow applications.

You can also use the Seed translatable text option in the same menu to copy the text provided inside applications to the APEX directory. You can download it later in the XLIFF format and translate it – either outside of APEX (with a Computer Assisted Translation tool) or inside the platform, with the help of the dedicated translation options.

It’s actually an .XML file that uses a relatively simple format (you can see it on the screen above). It gives you an ID that refers to a particular element of your application, along with the source message and the target message.

You can also find all the text that you can translate in the translation repository in the Shared Components->Translate section, but personally I prefer to use specialized external CAT (Computer Assisted Translation) tools, such as memoQ or Bureau Works.

After you translate everything you want, you still need to publish your translated application – otherwise, these changes won’t take effect. Use the last option – Publish – under How to translate in the Shared Components->Translate section, check the box next to the version or versions you want to publish, and click the blue Publish button.

Translating APEX internal messages

If you’re not developing in one of the 31 main supported languages, you’ll also need to translate all of the information provided by APEX itself – the platform’s internal system messages. Things such as options that you have available in the Interactive Report, or in the Interactive Grid. These won’t be translated out of the box if you’re not using one of the officially supported languages.

APEX’s documentation (for APEX 22.1, at the time of writing this article) provides a list of all those strings that have to be translated, but you must do it all manually. You need to go to the Shared Components->Translate section and select the Text Messages option under Translation Utilities. You’ll see a repository of messages that you can translate and add to your application. The problem is, there are over a thousand of these messages, so doing it manually is painstakingly slow. It works, sure, but it’ll cost you a lot of precious time.

There’s a better way, however. Over five years ago now, Pretius developers have created something called Translate APEX. It’s a repository that contains APEX internal messages in 52 popular languages (translated by the incredible APEX community) that aren’t officially supported by Oracle. Thanks to this resource, finishing this part of the translation will be very easy – it’s just a matter of downloading the translation script and running it in your database or APEX SQL Workshop.

Translating strings from the database

At this point, I’ve shown you how to translate your app’s internal messages and the content of the app itself. But what about strings from the database? Like messages from functions, and information about table constraints or validation that can be taken from your PL/SQL code. You can handle this with APEX’s text messages – the same option you can use out of the box to translate internal messages manually.

This is because you’re not limited to codes defined by Oracle – you can create custom ones. So, when you create an international application, you shouldn’t put message strings inside your PL/SQL code. Instead, you should build a text repository and refer to it with specific message code and language code.

In fact, APEX already offers such a repository, because you can store your messages in the same way the platform does – you can just insert your message into the text messages section or use the APEX lang package to insert it, and get it directly from the PL/SQL. This method will also help you handle some database constraint errors because in APEX 4.1 Oracle gave us the option to create our own error handling functions. You can use this approach to handle messages from database errors. For example, you can write an error handling function that’ll search for the text message with the same name as the name of the constraint that you have at the database level. Thanks to this, it’s a very coherent and elegant way to support these kinds of messages.

Of course, you also have data that’s stored inside the DB tables and you may not want to translate all of it – for example, if you have a report of employees, you’ll probably wish to keep their names as they are, but you might want to translate the names of their positions or departments they work in. You have two options in this regard.

First, you can design dedicated tables. They’re like dictionaries with information about the language (like on the screenshot below) and you can simply use them in your query.

The second option is to use another of APEX’s features – dynamic translations. It’ll translate your strings during the query execution. You can see this in action on the following screenshot – when you click the Click me! button, you’ll see a Hello from PL/SQL! the message that was generated by the PL/SQL code.

If you looked at this in my demo app, you’d have seen that I have this Click me! button and I also have a simple test message process that prints this “Success” message. It’s nothing sophisticated, but it uses apex_lang.message function to return the text with the code “PLSQL_MESSAGE” from the APEX text repository. It’s translated using exactly the same method that APEX uses to translate internal messages.

If I go back to Shared Components->Text Messages, and look for “plsql”, I’ll see the exact same message but with dedicated versions for all my translations.

The second thing is a translation that can be done at the level of your data. To show you this, I click the employee table you’ve seen before with position names that change when you change the language.

I then quickly edit the table responsible for the position (“Stanowisko” in Polish). There’s a dedicated list of values provided for this particular data and editing this component shows a simple query based on the APEX_LANG function. It uses the information from the Shared Components->Translate application->Dynamic translations option – you can provide a list of strings that you’d like to translate dynamically based on your data.

Taking translation to the next level

However, if things were that simple, Google Translator and Translate APEX would be the only required tools, and I could just as well end this article right here. Well, if you want to go one step further and localize your application, you’ll have to beyond that.

You see, a good translation is about more than just words – it’s about making whole ideas understandable for people from different countries and cultures. Such a translation can’t be too literal, word for word – it needs to take the context into account.

For example, do you know Winnie the Pooh? In Poland, the bear is known by a name that sounds completely different – “Kubuś”, which could be translated as “Jacob”. It might seem weird, but thanks to this, various elements of A.A. Milne’s book – such as rhymes, and verbal games – could be better understood by Polish readers.

Such small changes are fundamental for a good book localization, and it works the same way with applications.

There are three big problems you might face when you try to localize your system or application. Let’s look at them now.

Culture

You can define culture as a set of rules, habits and restrictions follow in a specific part of the world. Cultures also often use different currencies, numbers, and measures of weight, distance, and so on.

They also have their own, specific symbols and icons, and they may assign a different meaning to colors than people from outside their culture. For example, in our cultural region white is seen as the color of good, purity, and innocence, but in China, it’s also associated with mourning and death.

Language

What is the meaning of the word “go”? Depending on who you ask, you might get widely different answers. It can mean “to start”, “to travel”, “to search” or even “to become”. All of these answers will be correct, depending on the cultural and linguistic context – it’s one of these things that can make a translator’s job a living hell.

There’s also the matter of fonts, characters, encodings, and grammar: lexical-semantic issues, tags, special marks, and so on. The following image summarizes some of the problems a software developer might encounter while working with different languages (of course, there’s more of that).

However, that’s not all – there’s one more thing. To illustrate it, I want to ask you to do a simple test. Look at the image below, and tell me – which of the boxes is the third one?

Of course, depending on where you live, it might be either of the two boxes in the middle. For example, in Europe and the US, we’d say it’s the right-middle box. However, in, say, some countries in the Middle-East, they’d answer it’s the left-middle. We don’t consider this too often, but different cultures read from either left or right.

This matter is actually quite important for software developers because you have to organize code sequences differently depending on LTR/RTL language.

Time

Time is also problematic. You see, while many of us use the Gregorian calendar and therefore believe it’s the year 2022, in reality, different cultures often use their own. That means some of us actually might live in the years 1401 (Persian), 1443 (Islamic), or even 5782 (Hebrew).

You may also encounter problems with date and time formats. For example, 10.07 will mean the 10th of July in Poland, but the very same date will be read as the 7th of October in the US. You also have to consider whether to use the 12 or 24-hour clock. There may be other differences like that, depending on the region.

And finally, we also live in different time zones, which – of course – complicates the matter of time even further.

How to localize and globalize apps in APEX

At the very beginning, you need to answer one question: which elements of your application need to be localized? In most cases you’ll need to translate:

• strings – column names, menus, etc. They may come from different sources, and some of them may require translation, while others don’t
• numbers and dates – the format will depend on the region where the application will be used
• UI – some elements, like buttons, are generated by APEX’s engine and stored as internal messages

To handle all these variations you need to have a strong foundation in your database. Oracle supports creating international applications in several ways:

• You can use different charset encodings
• You can change the date/number formats, as well as currencies and separators by modifying the regional settings, or dynamically through ALTER SESSION command
• You can also set different time zones using the TIMESTAMP and TIME_ZONE parameters

APEX also offers you several options through the Globalization Settings menu in the Shared Components section of the Application Builder. The following things can be set through the builder:

• Primary language – you can set which language will be the primary one
• Information about the language – you can decide how it’ll be delivered. It can be set declaratively or by the user’s browser
• Default document direction – either LTR (left-to-right) or RTL (right-to-left)
• Default date formats – I recommend leaving these fields blank if you want to go global. APEX will then set them automatically depending on the chosen language
• Information about automatic time zones – it can be taken directly from the browser
• Other options related to the NLS settings that’ll be overwritten during each request

One thing that’s missing here is the calendar options – unfortunately, Oracle APEX doesn’t support different calendars for the time being. You can change it, but not through the Application Builder. It’s a bit more complicated – you’ll need to play a little with the NLS parameters. You need to go to Shared Comopnents->Security Attributes where you can put in some code related to the initialization, for example:

BEGIN
  IF :BROWSER_LANGUAGE = 'fa' THEN
    EXECUTE immediate 'ALTER SESSION SET NLS_CALENDAR=PERSIAN';
  END IF
END;

Every time the request generates the APEX site, this code will be invoked at the very beginning. Thanks to this, you can control the NLS parameters, outside of those available declaratively at APEX’s level. It’s also very useful when you want to make some changes to options that by default are stored in the Oracle Database. For example, if in your language the coma separator is a dot, and you want to enforce that behavior.

Geolocation

Going global also means that you’ll have to find a way for your entire application, some of its parts, or some content that you have in the app, to be available only in selected countries or regions. You probably know this well from Netflix, which offers different movies and TV series depending on when you access it. To have this functionality in APEX, we must somehow detect when our user is located.

Previously, there was more work required to add geolocation to APEX, but currently the feature is available on the platform. If you want more information about it, here’s an article written by my colleague, Matt Mulvaney: Oracle APEX new features – the low-code platform keeps evolving.

Conclusion

As you can see, making applications ready for the needs of users from different parts of the world is a very complex topic. If you’re interested in creating global apps – and if you aren’t, you probably soon will be – APEX is a pretty good choice. As you can see, it offers many tools, features you can use to translate and localize the various elements of your application. The platform is very flexible in this regard, so use this to your advantage.

And if you need someone to make a global application for you, write us at hello@pretius.com – we’ll get back to you in 48 hours and tell you how we can help. As you can see, between Translate APEX and our international experience, we know quite a bit about making solutions ready for markets in different countries – so why not use that knowledge?

If you need more APEX-related reading material, check out the other posts on our blog:

1. How to integrate Stripe with an Oracle APEX application: Step-by-step guide
2. Oracle APEX new features – the low-code platform keeps evolving
3. Google Identity Services – a quick guide on using the new library in Oracle APEX applications
4. What is Oracle APEX? Possibilities and career paths for low-code developers