The Globalization of Language in Oracle – The NLS_LANG variable

Let’s zero in on the most important variable for
implementing a global database in Oracle and some lessons learned.

Firstly I need to thank the professionals at Oracle for
their help in assisting me in getting the facts straight on these globalization
articles. If you haven’t noticed, the facts and methods to setting the NLS_LANG
variable, in the last article of this series, has been revised. I would
encourage you to go back and read this last article as the examples are now
more complete. This article is geared toward setting things straight around
the NLS_LANG variable and some of the pitfalls I fell into.

If I have not already said it yet, and I will say it again
in the future, if there were one variable that dictated how globalization in
Oracle works, doesn’t work, or just makes a plain mess of things, it would be
the NLS_LANG variable. I have never had a single variable that could play such
havoc on my testing methodologies and utterly confuse me at times. Just take a
look at the next two exhibits from Oracle documentation. They seem quite
harmless and straight to the point in how to set NLS_LANG. But after hours–yes
hours–of scratching my head, and finally talking to the good people at Oracle
I learned two things here. One, Windows does not natively support UTF8 (exhibit
1
) and this may be considered an error in the Oracle documentation. In exhibit
2
, even though I had set the NLS_LANG to a valid setting, if I ran SQL*Plus
MS-DOS mode (sqlplus), I still had an issue with running .sql scripts into my
database. Now let me say that exhibit 2 IS NOT WRONG but I just took it at face
value for running SQL*Plus. If I had run SQL*Plus Windows Mode (sqlplusw) I
would have had no problems. Actually I am very glad I was using SQL*Plus MS-DOS
mode as I have truly learned more that way.

Exhibit 1

Source : SQL*Plus® User’s Guide and Reference Release 10.1
(Part Number B12170-01)

Setting NLS_LANG

You can set
the NLS_LANG environment variable to control globalization features.

Example
12-1 Configuring Japanese Support in SQL*Plus on Windows

1. Ensure you have exited
your current SQL*Plus session.

2. Open System from Start
> Settings > Control Panel.

3. Click the Advanced tab
and select Environment Variables.

4. Create a new environment
variable, NLS_LANG, with a value of Japanese_Japan.UTF8.

5. You may need to restart
Windows for this setting to take effect.

Exhibit 2

Source:
Oracle®
Database Installation Guide 10g Release 1 (10.1.0.2.0) for Windows
(Part No. B10130-02)

D.3 NLS_LANG Settings in MS-DOS Mode and
Batch Mode

Before you can use Oracle
utilities such as SQL*Plus, SQL Loader, Import, and Export in MS-DOS mode, make
sure that you have set the character set field of the NLS_LANG parameter for the session to the correct value.

Table D-2 lists the Oracle character
sets that correspond to the MS-DOS mode for various operating system locales:

Table
D-2 Oracle Character Sets for Operating System Locales

Operating System Locale         Character Set
Japanese                             JA16SJIS

Truly, there does not have to be mass confusion when working
with this NLS_LANG variable. Here are some of my lessons learned. I hope you
benefit from them, I am sure there are more to come, but here is what I have to
date. These lessons are from me running in a total Windows environment.
Although, since I have discovered that all my lessons learned, do not rely upon
the database at all, as NLS_LANG is really a client side parameter.

1.  The
Oracle character set is synonymous with the Windows code page. Basically, they
mean the same thing.

2.  For
all intents and purposes, in most Windows’ environments, the Windows command
line mode has a different code page (uses a different character set) than the
Windows GUI. This in particular was a huge thing for me to get use to as I have
always used the DOS command mode SQL*Plus (sqlplus). When I switched to the
Windows GUI SQL*Plus version (sqlplusw) all my problems went away. This is
extremely important if you wish to run some scripts through sqlplus. Don’t get
into the trap of editing SQL commands through a GUI editor such as notepad and
expect them to be recognized at the DOS command mode. You can easily verify
this yourself by opening up notepad, typing in an international character such
as ‘Ä’, saving the file, and then using the DOS command ‘TYPE’ to display the
contents of the file. You will get something totally different. You can get
around this by using the DOS command EDIT for your files but I don’t know
anyone who would want to. I would suggest to everyone to start using the
Windows version of SQL*Plus. I have also been told that the web based iSQL*Plus
is even better for globalization and representing international characters.

3.  The
NLS_LANG setting must reflect the character set (code page) that you are using
on your client. You cannot switch to a different language, such as German, just
by setting the NLS_LANG to GERMAN_GERMANY.

4.  A
client machine’s locale is determined by the Regional options. My locale was
set for English. So if you want to change to a different language you need to
switch your locale at the operating system level to that region.

5.  You
can change your client locale very easily by going into the Regional options
and changing to a different locale. Start -> Settings -> Control Panel
-> Regional Options

6.  NLS_LANG
does not reflect the character set of the target database server or instance.
Setting your NLS_LANG to the target database character set is a decision that
will cause you issues down the road.

7.  The
NLS_LANG must reflect the character set (code page) that you are using on your
client and is solely responsible for assisting Oracle in the conversion of
characters from your client character set to the database server character set.

8.  The
LANGUAGE and TERRITORY parts of the NLS_LANG parameter do nothing for defining
the character set. They are solely used for the presentation formats of such
items as messages, dates, times, currencies, and numbers.

9.  Read
the manuals on this one. I have often been able to hack through setting Oracle
parameters and quickly determining what is happening. Character sets, code pages,
and getting a true understanding of the history and current implications is not
trivial at times. You will no doubt need to visit not only Microsoft’s web site
for code page information but also try out www.unicode.org
to assist you in your understanding.

In this ever-growing global economy and with the number of
mergers and consolidations going on, it will become more and more important for
us to provide for a more open database environment that allows for this
consolidation and proper movement of data between disparate systems. The
underling link for this to happen is opening up our databases for international
character sets and allowing for the globalization of our databases. The two
most important steps for this to happen is for us to first choose a character
set at the database level that supports the regions we will be expected to
support and secondly getting a handle on those different regions and verifying
that we can set NLS_LANG such that those regions can provide and retrieve
information from the database properly.

»


See All Articles by Columnist
James Koopmann

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles