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 |
NLS_INSTANCE_PARAMETERS |
As you know, you can change a variety of parameters for WARNING: If you expect your sessions to use the |
NLS_SESSION_PARAMETERS |
In addition, you have the ability to set each individual |
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 |
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 |
|
Hh34:MI:SSXFF |
NLS_TIMESTAMP_TZ_FORMAT |
DD-MON-RR |
|
DD.MM.RR |
NLS_TIME_FORMAT |
DD-MON-RR |
|
DD.MM.RR |
NLS_TIME_TZ_FORMAT |
HH.MI.SSXFF |
|
Hh34:MI:SSXFF |
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/jkoopmann@pinehorse.lager file=expdat.dmpExport: 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.UTF8C:> exp jkoopmann/jkoopmann@pinehorse.lager 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 setAbout 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.