The Globalization of Language in Oracle - National Language SupportMarch 3, 2005 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.
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.
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.
Example of Using NLS_LANGIf 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.
C:\>exp jkoopmann/jkoopmann@pinehorse.lager 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/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 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. |
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
