What is the difference b/w NLS_NCHAR_CHARACTERSET and NLS_NCHAR_CHARACTERSET?

NLS_CHARACTERSET is used for CHAR, VARCHAR2, CLOB columns and NLS_NCHAR_CHARACTERSET is used for NCHAR, NVARCHAR2, NCLOB columns.

One can find the value of NLS_NCHAR_CHARACTERSET used by the database by running the following SQL:
select value from NLS_DATABASE_PARAMETERS where parameter=’NLS_NCHAR_CHARACTERSET’;

VALUE
———————-
AL16UTF16

The possible values for NLS_NCHAR_CHARACTERSET can be UTF8 or AL16UTF16.

How to find the NLS_LANG to set for a database?

One can use the following v$nls_parameters view to find the database LANGUAGE, TERRITORY and CHARACTER SET.

select DECODE(parameter, 'NLS_CHARACTERSET', 'CHARACTER SET',
'NLS_LANGUAGE', 'LANGUAGE',
'NLS_TERRITORY', 'TERRITORY') name,
value from v$nls_parameters
WHERE parameter IN ( 'NLS_CHARACTERSET', 'NLS_LANGUAGE', 'NLS_TERRITORY')
/

NAME VALUE
————- —————–
LANGUAGE AMERICAN
TERRITORY AMERICA
CHARACTER SET WE8ISO8859P1

export NLS_LANG=<language>_<territory>.<character set>

Setting NLS_LANG for export/import

Last updated: 17-Nov-2008

When exporting/importing one can minimize risk of losing data during import/export by setting NLS_LANG.

- Before starting export set NLS_LANG to be the same character set of the database being exported which means no conversion takes place, all the data will be stored in the export file as it was stored in the database.
- Before starting import set NLS_LANG to be the same value as the it was set during export which means no conversion will take place in the import session, but if the character set of the target database is different the data will automatically be converted when import inserts the data in the database.

OR

- Before starting export set NLS_LANG to be the same character set of the database being imported to which means conversion takes place at this step it will automatically convert during export.
- Before starting import set NLS_LANG to be the same value as the it was set during import which means no conversion will take place as it was already converted during export.

To find the NLS_LANG click on the following link

Setting NLS_LANG for Oracle

NLS_LANG=<language>_<territory>.<character set>
Example: export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

Setting NLS_LANG tells Oracle what characterset the client is using so Oracle can do conversion if needed from client’s characterset to the database characterset and setting this parameter on the client does not change the client’s characterset. Setting Language and Territory in NLS_LANG has nothing to do with storing characters in database, it’s controlled by the characterset and of course if the database can store that characterset.

To check session NLS session parameters, note this doesn’t return the characterset set by NLS_LANG
SELECT * FROM NLS_SESSION_PARAMETERS;
SELECT USERENV (‘language’) FROM DUAL;

To find the NLS_LANG of your database one can run the following SQL:
SQL> SELECT * from NLS_DATABASE_PARAMETERS WHERE parameter IN ( ‘NLS_LANGUAGE’, ‘NLS_TERRITORY’, ‘NLS_CHARACTERSET’);

PARAMETER VALUE
—————————— —————————————-
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CHARACTERSET WE8MSWIN1252

To get the possible value for language, territory and characterset you can check the view V$NLS_VALID_VALUES.
– parameter – possible values LANGUAGE, TERRITORY, CHARACTERSET
SELECT parameter, value FROM V$NLS_VALID_VALUES;

To change the client language
export NLS_LANG=BELGIUM_.WE8ISO8859P1
To change the client territory
export NLS_LANG=_BELGIUM.WE8ISO8859P1
To change the client characterset
export NLS_LANG=.AL32UTF8

French accents character when using SQL*Plus

For SQL*Plus to process french accents characters when using SQL*Plus, you would need to set NLS_LANG

On Windows
set NLS_LANG=.AL32UTF8
set NLS_LANG=.UTF8
On Unix (ksh)
export NLS_LANG=.AL32UTF8
set NLS_LANG=.UTF8

Now when you start SQLPlus from Unix or Windows it will keep the accents on the french characters.