With the globalization of economy, more and more companies are doing business outside their home countries. The advent of Internet and broadband media has further improved the awareness about global brands and spurred the expansion of businesses beyond local economies. This creates an interesting challenge on how to deal with multi-national languages, currencies, dates, etc., in the enterprise databases. Fortunately, Oracle has globalization support that helps you to organize and process information in native languages. This will let the database adapt to date, time, money, calendar, numeric conventions and error messages to adapt to the native language of the region.

Oracle has supported some of the globalization requirements as National Language Support (NLS) features in databases prior to Oracle 10g. NLS enables the database to choose a language and store data in a particular character set corresponding to that language. With the latest releases, Oracle’s globalization support lets you develop multi-lingual software applications that can be used in any part of the world. The front-end application will read the data and render content in the local language and native preferences. Oracle uses Oracle NLS RunTime Library (NLS RTL) to provide independent functions that manipulate text and data to appear in native languages.

Globalization supports use of different calendar systems around the world. It also supports sorting according to linguistic definitions and different character sets (single-byte, multi-byte, fixed-width, Unicode). Unicode characters are stored as UTF-8 or SQL CHAR data types. The database character set affects the encoding scheme and its performance.

Single-byte encoding schemes take up the least space, are efficient, and easy to process. The number of bytes and character are the same throughout the string. Multi-byte encoding schemes support pictorial scripts in many Asian languages (Chinese/Japanese), where a large number of characters are used. Oracle Database uses the following naming convention for character sets - <region><number of bits for character><standard character set name><S/C>. S or C indicates optional Server or Client information.

Let us look at a scenario where these are put into application. Consider a Chinese customer using native language computer to place an order for merchandise from an English Unix operating system (OS). The client cannot retrieve the English catalog in Mandarin, unless he uses a client operating system that supports the database character set with appropriate Mandarin fonts and input methods. When you use a database character set different from the client OS character set, Oracle Database will convert the OS character set to the database character set. However, this could lead to data loss during translation as well as overhead on the server. If the client character set and destination character set are different, there will be data loss. Characters that are not available in destination system will be converted to a replacement character, which may not be linguistically related.

To improve the process and enable transaction like the one described above, the database character set should be a superset of the native character set. When you design such an application where different locales access a central database, review the various character sets and use the superset that supports all or most of the languages. So multi-byte character sets are better for supporting many languages than single-byte character set.

Unicode offers more usability, compatibility, and extensibility with legacy systems. National character set is an alternative character set for storing Unicode character data in the database that does not have Unicode database character set. Oracle also offers WE8ISO8859P1 character set that supports many Western European languages and Spanish. The drawback is that your database will have restricted multilingual support for Asian languages and similar ones. For currency calculations, the NLS_ISO_CURRENCY parameter overrides the default value defined implicitly by NLS_TERRITORY for the database.

Regardless of your organization’s business model, if you are planning to market your products or services across the world, your database should be ready to accept the data from customer’s operating system. In order to achieve a true global business model, make sure that your centralized database has the capabilities to handle and support the transactions in intended native languages and locales.

About the Author:

Dr. Arun Kumar R., data architect at Cingular Wireless, has over a decade of experience in Oracle technologies. He has authored two Oracle Database 10g books and over 50 technical papers. He can be reached at arundbta@dbatrends.com.