2

I thought I read that NLS_CHARACTERSET has the encoding of the database and NLS_LANG the encoding of the client. Is that correct?

Does that mean that the two encodings can be different?

In some other documentations, I read that the NLS_LANG is defined by the NLS_CHARACTERSET. Which assertion is true?

3

The NLS parameters can be quite tedious to understand and I recommend searching for the term "Setting Up a Globalization Support Environment" in the official documentation.

NLS is short for National Language Support and is the beginning of multiple parameters that are available in the various Oracle products.


NLS_LANG

NLS_LANG is described as a variable that defines the <LANGUAGE>_<TERRITORY>.<CHARACTER_SET> of your client environment.

An example of a valid NLS_LANG setting would be:

NLS_LANG=AMERCICAN_AMERICA.US7ASCII

Language

The individual parts of the variable define how Oracle displays information. If your <LANGUAGE> is AMERICAN then messages, days and months will be displayed using American spelling. Sorting is also affected by this part of the parameter

Territory

Territory affects things like the default date, settings for money values (e.g. $ sign), and number formats (e.g. 3,000,000.12 to display three million point 12)

Character_Set

This is the bit where it becomes pretty interesting. This part of the NLS_LANG parameter is described as the

...character set used by the client application.

Summary NLS_LANG

So in essence you are configuring how you client would display data stored in the database. If your database contains the following data:

 Employee     | Employment Start    | Salary
--------------+---------------------+--------
 Michael Ward | 2018-01-01          | 100240

...then if your client has AMERICAN_AMERICA.US7ASCI as the NLS_LANG setting, then querying this table would return:

 Employee     | Employment Start     | Salary
--------------+----------------------+-------------
 Michael Ward | 01-JAN-18            | 100,240

...whereas having a setting of FRENCH_FRANCE.WE8ISO8859P1 would result in:

 Employee     | Employment Start     | Salary
--------------+----------------------+-------------
 Michael Ward | 01/01/18             | 100'240

Lets follow up with the other parameter...


NLS_CHARACTERSET

The NLS_CHARACTERSET parameter is setup during the creation of your database instance and shouldn't be changed thereafter. (It can be altered but it can cause data corruption even in the DATA DICTIONARY objects). This parameter defines the encoding of the data in the ...

NLS_CHARACTERSET Parameter

... CHAR, VARCHAR2, LONG and CLOB columns of a table.

NLS_NCHAR_CHARACTERSET Parameter

... NCHAR, NVARCHAR2 and NCLOB columns of a table.

Summary NLS_CHARACTERSET

The parameter is used to tell the RDBMS system with which encoding it shold store data in certain columns.

I have intentionally left a large portion of the explanation regarding NLS_CHARACTERSET out, because it gets really complicated when comparing encodings between AL8UTF8, AL32UTF8 and AL16UTF16 (which can only be use in conjunction with the NLS_NCHAR_CHARACTERSET parameter) and all the other possible Unicode settings.


Comparison

When retrieving data from a database column NLS_LANG will add all the bells and whistles (date formatting, time formatting, number formatting) to the data that is being retrieved.

The NLS_CHARACTERSET parameter defines with which encoding the data is stored in the columns. (ASCII, Unicode, ...)

Answering your Question

Does that mean that the two encodings can be different?

Yes, you can have two totally different encodings. You could store you data using a setting of NLS_CHARACTERSET = AL32UTF8 and still display and transport the data to the database with NLS_LANG=American_America.US7ASCII.

Display vs. Storage

0

They can be different. The short explanation is that the characters are stored in the database as defined by the NLS_CHARACTERSET. If the client is not able to use this character-set. The NLS_LANG also holds region. If the client uses a different currency, date format, decimal point/comma as is expected by the database then this can also differ.

0

Yes, your assumptions are right (see other answer)

Does that mean that the two encodings can be different?

Yes, in fact they are always different if you consider that your database has two character sets, the NLS_CHARACTERSET and the NLS_NCHAR_CHARACTERSET and you may use both in one single statement.

NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET are typically different. Thus NLS_LANGis either different to NLS_CHARACTERSET or different to NLS_NCHAR_CHARACTERSET.

You find some useful information at NLS_LANG FAQ

In some other documentations, I read that the NLS_LANG is defined by the NLS_CHARACTERSET. Which assertion is true?

This statement seems to be wrong. However, my impression is that TOAD first opens a DB connections, reads the NLS_CHARACTERSET value and then open the "final" DB connection where NLS_LANG is set according to previously read NLS_CHARACTERSET. But this seems to be a rather sophisticated approach of doing it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.