Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Mar 3, 2005

The Globalization of Language in Oracle - National Language Support

By James Koopmann

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/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.

» See All Articles by Columnist James Koopmann



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM