The Globalization of Language in Oracle – National Language Support

In the previous
article
, we looked at the terminology around globalization, mostly from
outside Oracle. This article will take a look at the internal parameters
available within Oracle that we will need to become familiar with.

It is very important when talking about
the globalization of language to understand the settings that you currently
have on the database server and what you expect at the client side application.
To do this, Oracle allows National Language Support (NLS) at the instance level
as well as at the session level providing the ability to default the NLS
parameters for our end users’ client PCs to the instance settings or override
them when needed because they are truly in a different locale. This article
will introduce you to the settings that are available, how to determine what
the values of these variables are and an export/import example that should
prove enlightening.

Oracle Views that holding the different levels of NLS parameters.

Oracle keeps three levels of NLS parameters within the database
that are set and apply to the database, instance, and session levels. Here is a
brief synopsis of those levels. As you will see, only the instance and session
levels are of any real concern, as there is nothing we can do to change the
database parameter settings other than a rebuild of the database. The database
view is presented here because it will impact the settings at the instance and
session level as the defaults if not explicitly set at the lower levels.

NLS_DATABASE_PARAMETERS

When you create your database, you tell it how you are
going to handle or not handle globalization of the database. The
NLS_DATABASE_PARAMETERS view will display what these settings were at
database creation time. These are fixed at the database level and cannot be
changed. The good thing is that while they do set up some of your options
down the road with regard to having your database talk globally, they are
only used when check constraints are enforced in the database. Therefore, you
will not really need to worry about what these settings are after database
creation time.

NLS_INSTANCE_PARAMETERS

As you know, you can change a variety of parameters for
your instance through either the INIT.ORA file or the SPFILE. The
NLS_INSTANCE_PARAMETERS view will display those settings that are set at the
instance level.

WARNING:

If you expect your sessions to use the
NLS_INSTANCE_PARAMETERS, they must not have set the NLS_LANG environment
variable. This parameter may be set as an operating system variable or within
the registry. More on the NLS_LANG operating system variable latter. Just be
aware that it affects other NLS parameters if set and can cause you pain if
you forget about it and expect something different after connecting to the
database.

NLS_SESSION_PARAMETERS

In addition, you have the ability to set each individual
session’s globalization parameters and the NLS_SESSION_PARAMETERS view will
show you what the current settings are. This view is specific to the session
querying from it.

The NLS parameters that are available for setting at the
database, instance or session level can be extracted via the following simple
SQL statement. The results, for my instance, follow in Table 1. As you can see,
three specific parameters cannot be set at the instance and session levels. The
characterset denotes the types of characters that can be stored within the
database and thus we cannot change those, without some tricks, at the instance
and session level. In addition, it is impossible to change the database
version.


select d.parameter Dparameter, i.parameter Iparameter,
s.parameter Sparameter
from nls_database_parameters d, nls_instance_parameters i,
nls_session_parameters s
where d.parameter = i.parameter (+)
and d.parameter = s.parameter (+)
order by 1

Table 1.

NLS parameters supported at database, instance, or
session level.

Database

Parameter

Instance

Parameter

Session

Parameter

NLS_CALENDAR

NLS_CALENDAR

NLS_CALENDAR

NLS_CHARACTERSET

NLS_COMP

NLS_COMP

NLS_COMP

NLS_CURRENCY

NLS_CURRENCY

NLS_CURRENCY

NLS_DATE_FORMAT

NLS_DATE_FORMAT

NLS_DATE_FORMAT

NLS_DATE_LANGUAGE

NLS_DATE_LANGUAGE

NLS_DATE_LANGUAGE

NLS_DUAL_CURRENCY

NLS_DUAL_CURRENCY

NLS_DUAL_CURRENCY

NLS_ISO_CURRENCY

NLS_ISO_CURRENCY

NLS_ISO_CURRENCY

NLS_LANGUAGE

NLS_LANGUAGE

NLS_LANGUAGE

NLS_LENGTH_SEMANTICS

NLS_LENGTH_SEMANTICS

NLS_LENGTH_SEMANTICS

NLS_NCHAR_CHARACTERSET

NLS_NCHAR_CONV_EXCP

NLS_NCHAR_CONV_EXCP

NLS_NCHAR_CONV_EXCP

NLS_NUMERIC_CHARACTERS

NLS_NUMERIC_CHARACTERS

NLS_NUMERIC_CHARACTERS

NLS_RDBMS_VERSION

NLS_SORT

NLS_SORT

NLS_SORT

NLS_TERRITORY

NLS_TERRITORY

NLS_TERRITORY

NLS_TIMESTAMP_FORMAT

NLS_TIMESTAMP_FORMAT

NLS_TIMESTAMP_FORMAT

NLS_TIMESTAMP_TZ_FORMAT

NLS_TIMESTAMP_TZ_FORMAT

NLS_TIMESTAMP_TZ_FORMAT

NLS_TIME_FORMAT

NLS_TIME_FORMAT

NLS_TIME_FORMAT

NLS_TIME_TZ_FORMAT

NLS_TIME_TZ_FORMAT

NLS_TIME_TZ_FORMAT

The specific NLS parameters values that are set at the
database, instance or session level can be extracted via the following simple
SQL statement. The results follow in table 2. Keep these two scripts handy, as
they will serve you well as we get further into talking about these values. You
will no doubt want to run this script after making various changes, to see what
your current NLS settings are opposed to what you might think they are. I
personally run this script in-line with my test scripts so that I know exactly
what my session settings are. Also, notice that there are quite a few instance
parameters that do not have a value. These are potential opportunities to set a
value at the instance level and have it take effect at the session level. Also,
notice that my session parameters values are quite different from the database
and instance values. This is because I set my operating system environment
variable NLS_LANG equal to GERMAN. Notice the differences in date, currency,
and time formats. By setting this simple environment variable on the client
side, you can drastically affect the appearance of how some very simple data is
displayed to your end user. This is very important if they are truly in a
locale that uses the German language. I would encourage you to set this on your
client PC just before entering into SQL*Plus and running a few simple queries
to check out the formatting.


select d.parameter Dparameter,
d.value Dvalue,
i.value Ivalue,
s.value Svalue
from nls_database_parameters d, nls_instance_parameters i,
nls_session_parameters s
where d.parameter = i.parameter (+)
and d.parameter = s.parameter (+)
order by 1

Table 2.
NLS parameters VALUES at database, instance, or session
level.

Database

Parameter

Database

Parameter

Value

Instance

Parameter

Value

Session
Parameter Value

NLS_CALENDAR

GREGORIAN

 

GREGORIAN

NLS_CHARACTERSET

UTF8

 

 

NLS_COMP

BINARY

 

BINARY

NLS_CURRENCY

$

 

NLS_DATE_FORMAT

DD-MON-RR

 

DD.MM.RR

NLS_DATE_LANGUAGE

AMERICAN

 

GERMAN

NLS_DUAL_CURRENCY

$

 

NLS_ISO_CURRENCY

AMERICA

 

GERMANY

NLS_LANGUAGE

AMERICAN

AMERICAN

GERMAN

NLS_LENGTH_SEMANTICS

BYTE

BYTE

BYTE

NLS_NCHAR_CHARACTERSET

AL16UTF16

 

 

NLS_NCHAR_CONV_EXCP

FALSE

FALSE

FALSE

NLS_NUMERIC_CHARACTERS

.,

 

,.

NLS_RDBMS_VERSION

10.1.0.2.0

 

 

NLS_SORT

BINARY

 

GERMAN

NLS_TERRITORY

AMERICA

AMERICA

GERMANY

NLS_TIMESTAMP_FORMAT

HH.MI.SSXFF
AM

 

Hh34:MI:SSXFF

NLS_TIMESTAMP_TZ_FORMAT

DD-MON-RR
HH.MI.SSXFF AM

 

DD.MM.RR
Hh34:MI:SSXFF

NLS_TIME_FORMAT

DD-MON-RR
HH.MI.SSXFF AM TZR

 

DD.MM.RR
Hh34:MI:SSXFF TZR

NLS_TIME_TZ_FORMAT

HH.MI.SSXFF
AM TZR

 

Hh34:MI:SSXFF
TZR



Example of Using NLS_LANG

If you are like me, you probably have a multitude of
database instances spread over just as many database servers. Doing any form of
maintenance on these boxes, such as exports, can pose an administrative problem
if you need to log on or schedule individual exports on each of the boxes. What
typically will happen is that we will designate a single database environment
that connects via TNS to each of the databases and performs an export. When you
start introducing different character sets and languages into the mix, having a
single script that can export can cause problems. This is because the client
machine doing the export has a specific NLS_LANG environment variable defined,
or none at all, which when used for exporting can cause errors. Listing 1 gives
the top part of an export that has put up some errors because of the improper
setting of this NLS_LANG variable. The client is not using the same character
set as the server so a warning is displayed. Sometimes an export will
completely error out because there is no way for a conversion between character
sets to happen. Please note line seven in this listing for the description of
the problem, i.e. ‘server uses UTF8 character set (possible charset
conversion).

Listing 1.

Warnings caused by client not using the same character
set.


C:>exp jkoopmann/[email protected] file=expdat.dmp

Export: Release 10.1.0.2.0 – Production on Thu Feb 17 13:53:38 2005

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 – Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
server uses UTF8 character set (possible charset conversion)

About to export specified users …
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user NRI
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user NRI
About to export NRI’s objects …
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export NRI’s tables via Conventional Path …
. . exporting table TABLEA 0 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table TABLEB 0 rows exported
EXP-00091: Exporting questionable statistics.


You cannot see in Listing 2 that if we set the
NLS_LANG environment variable to what is expected the export will occur without
error.


C:>set NLS_LANG=AMERICAN_AMERICA.UTF8

C:> exp jkoopmann/[email protected] file=expdat.dmp

Export: Release 10.1.0.2.0 – Production on Thu Feb 17 13:56:31 2005

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 – Production
With the Partitioning, OLAP and Data Mining options
Export done in UTF8 character set and AL16UTF16 NCHAR character set

About to export specified users …
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user NRI
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user NRI
About to export NRI’s objects …
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export NRI’s tables via Conventional Path …
. . exporting table TABLEA 0 rows exported
. . exporting table TABLEB 0 rows exported

The secret to understanding and setting up database and
client side PCs that are dependent on a locale is to first understand the
database NLS parameters and how they are set. Only then can you make a logical
decision about where to set the variables that will allow your end users the
ability to view information in a format that is acceptable to them. Next time
we will begin looking at some of these parameters and their affect on both the
server and client side connections.

»


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