NLS Overview-Datatypes-1

As stated earlier, NLS stands for National Language Support. NLS is a very powerful feature of the database, but one that is often not as well understood as it should be. NLS controls many aspects of our data. For example, it controls how data is sorted and whether we see commas and a single period in a number (e.g., 1,000,000.01) or many periods and a single comma (e.g., 1.000.000,01). But most important, it controls the following:
•\ Encoding of the textual data as stored persistently on disk
•\ Transparent conversion of data from character set to character set

It is this transparent part that confuses people the most—it is so transparent, you cannot even really see it happening. Let’s look at a small example.

Suppose you are storing 8-bit data in a WE8MSWIN1252 character set in your database, but you have some clients that connect using a 7-bit character set such as US7ASCII. These clients are not expecting 8-bit data and need to have the data from the database converted into something they can use. While this sounds wonderful, if you are not aware of it taking place, then you might well find that your data loses characters over time as the characters that are not available in US7ASCII are translated into some character that is.

This is due to the character set translation taking place. In short, if you retrieve data from the database in character set 1, convert it to character set 2, and then insert it back (reversing the process), there is a very good chance that you have materially modified the data. Character set conversion is typically a process that will change the data, and you are usually mapping a large set of characters (in this example, the set of 8-bit characters) into a smaller set (that of the 7-bit characters).

This is a lossy conversion—the characters get modified because it is quite simply not possibleto represent every character. But this conversion must take place. If the database is storing data in a single-byte character set but the client (say, a Java application, since the Java language uses Unicode) expects it in a multibyte representation, then it must be converted simply so the client application can work with it.

You can see character set conversion very easily. For example, I hav0e a database whose character set is set to WE8MSWIN1252, a typical Western European character set:
$ sqlplus eoda/foo@PDB1
SQL> select * from nls_database_parameters where parameter = ‘NLS_ CHARACTERSET’;
PARAMETER VALUENLS_CHARACTERSET WE8MSWIN1252

Now, if I ensure my NLS_LANG is set the same as my database character set (Windows users would change/verify this setting in their registry):
SQL> host echo $NLS_LANGAMERICAN_AMERICA.WE8MSWIN1252

I can create a table and put in some “8-bit” data. This data will not be usable by a 7-bit client, one that is expecting only 7-bit ASCII data:
SQL> create table t ( data varchar2(1) ); Table created.
SQL> insert into t values ( chr(224) ); 1 row created.
SQL> insert into t values ( chr(225) ); 1 row created.
SQL> insert into t values ( chr(226) ); 1 row created.
SQL> select data, dump(data) dump from t;D DUMP
à Typ=1 Len=1: 224
á Typ=1 Len=1: 225
â Typ=1 Len=1: 226
SQL> commit;

Note If you do this example yourself and do not see the preceding output, make sure your terminal client software is using a UTF-8 character set itself. Otherwise, it might be translating the characters when printing to the screen! A common terminal emulator for UNIX will typically be 7-bit ASCII. This affects both Windows and UNIX/Linux users alike. Make sure your terminal can display the characters.

Now, if I go to another window and specify a 7-bit ASCII client, I’ll see quite different results:
$ export NLS_LANG=AMERICAN_AMERICA.US7ASCII
$ sqlplus eoda/foo@PDB1
SQL> select data, dump(data) dump from t;D DUMP
a Typ=1 Len=1: 224
a Typ=1 Len=1: 225
a Typ=1 Len=1: 226