NLS characterset in oracle

metalink:

https://supporthtml.oracle.com/ep/faces/secure/km/DocumentDisplay.jspx?id=60134.1

https://supporthtml.oracle.com/ep/faces/secure/km/DocumentDisplay.jspx?id=227330.1

https://supporthtml.oracle.com/ep/faces/secure/km/DocumentDisplay.jspx?id=306411.1

https://supporthtml.oracle.com/ep/faces/secure/km/DocumentDisplay.jspx?id=333489.1

This section of the discussion starts with the assumption that the
Oracle database is set up with US7ASCII character set.

Character sets are used to actually STORE locale-dependant data.
When clients from different locales connect to the database, automatic
conversion of character sets takes place in the background to
display the data properly (based on the NLS_LANG setting on the client).
When a client in a different locale sends
data back to Oracle, same conversion takes place before data is actually
stored. Data is ALWAYS stored in the character set specified in the
CREATE DATABASE statement (or default, if not explicitly specified).

During these character set conversions back-and-forth, any characters that
cannot be mapped are replaced with "replacement characters".
The following is an excerpt from the Oracle8i National Language Support Guide,
Release 8.1.5 --

If, for example, a server used US7ASCII and a German client WE8ISO8859P1,
the German character ß would be replaced with "?" and the character ä would
be replaced with "a".

Replacement characters may be defined for specific characters as part of a
character set definition. Where a specific replacement character is not defined,
a default replacement character is used. To avoid the use of replacement characters
when converting from client to database character set, the server character set
should be a superset (or equivalent) of all the client character sets. In the above
example, the server's character set was not chosen wisely. If German data is expected
to be stored on the server, a character set which supports German letters is needed,
for example, WE8ISO8859P1 for both the server and the client.


If a character got STORED as a "?" (or some other incorrect conversion), there is
nothing you can do through init.ora to fix it.
Parameters from init.ora do not touch the structure and/or contents of data.
The only way to fix this issue would be to change the database character set.
Init.ora will allow you to modify the language and the territory but not the character set.


--------------------------------------------------------------------------------
NLS often defaults to American which can cause problems in a European or Global context.
During conversion from one language to another,
any character which has no equivalent is replaced by
a character which is character-set dependent.
--------------------------------------------------------------------------------
The Oracle Parameter to change is NLS_CHARACTERSET,(equivalent to NLS_LANGUAGE).
--------------------------------------------------------------------------------
This can be done in a number of ways, all of which are discussed below.
--------------------------------------------------------------------------------


There are four ways to specify NLS parameters in general --
1. As initialization parameters on the server.
Parameters can be included in the initialization file (INIT.ORA) to specify a default server NLS environment.
These settings have no effect on the client side; they control only the server's behavior.
For example: NLS_TERRITORY = "CZECH REPUBLIC"
2. As environment variables on the client. NLS parameters can be used to specify
locale-dependent behavior for the client, overriding the defaults set for the server
in the initialization file.For example: on a UNIX system:
% setenv NLS_SORT FRENCH
3. As ALTER SESSION parameters. NLS parameters set in an ALTER SESSION statement can be used
to override the defaults set for the server in the initialization file,
or set by the client with environment variables. For example:
SVRMGR> ALTER SESSION SET NLS_SORT = FRENCH
4. As an SQL function parameter. NLS parameters can be used explicitly to hardcode
NLS behavior within a SQL function. Doing so will override the defaults set for the
server in the initialization file, the client with environment variables, or
ALTER SESSION on the client.For example:
TO_CHAR(hiredate, 'DD/MON/YYYY', 'nls_date_language = FRENCH')

* The following NLS parameters can be initialization parameters, environment variables,
and ALTER SESSION parameters: NLS_CURRENCY NLS_DATE_FORMAT NLS_DATE_ LANGUAGE
NLS_ISO_CURRENCY NLS_NUMERIC_CHARACTERS NLS_SORT
* The following parameters can be specified as initialization parameters and
ALTER SESSION parameters, but not as environment variables: NLS_LANGUAGE
NLS_TERRITORY
* The following NLS parameters can be set only as environment variables:
NLS_CREDIT NLS_DEBIT NLS_LANG NLS_LIST_SEPARATOR NLS_MONETARY_CHARACTERS
NLS_NCHAR

The above is a good overview about the NLS environment in Oracle.

Now turning to the problem on hand,
I am assuming that your analysis that you need to change the character set
is correct (there are a few issues with Microsoft's DTS and OLEDB and ODBC drivers
for Oracle, which you can find on MSDN, if you don't hit the proverbial
nail on the head with character sets).

Changing Character Sets cannot be done through init.ora :-(
NLS_LANG is about the only thing that will allow you to use a different
character set for a session.
IDEALLY, the server's character set should be a superset of all client locales.
Since it sounds like your server is currently set up with US7ASCII, and US7ASCII
is actually a SUBSET of WE8ISO8859P1.
It is better to change the server's character set instead of using NLS_LANG
for a client session. It is possible to change character sets if the new
character set is a STRICT SUPERSET of the old character set, without recreating
the database.
In this case, WE8ISO8859P1 is a strict superset of US7ASCII.
With that in mind, try the following -- CHANGE ON THE SERVER
====================
The syntax to do this is through the ALTER DATABASE statement --
ALTER DATABASE [] CHARACTER SET ;
ALTER DATABASE [] NATIONAL CHARACTER SET ;
To change the database character set, perform the following steps. Not all of them are absolutely necessary,
but they are recommended: SQL> SHUTDOWN IMMEDIATE; -- or NORMAL
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET WE8ISO8859P1;
SQL> SHUTDOWN IMMEDIATE; -- or NORMALSQL> STARTUP;
If you want to try using NLS_LANG on the client --CHANGE ON THE CLIENT
====================
To change NLS_LANG for clients only, for example, using MS SQL Server,
this discusses how to do this for Windows :-
Use REGEDIT to modify the registry setting of NLS_LANG
(probably, HKEY_LOCAL_MACHINE\Software\Oracle). Do a "Find" through RegEdit on NLS_LANG
to make sure.)
The recommendation would be to use ALTER DATABASE since for the character
sets involved, it is a SAFE operation.


--------------------------------------------------------------------------------


There are five approaches :-

1) Edit init.ora by changing :-
from NLS_CHARACTERSET=USASCII7
to NLS_CHARACTERSET=WE8ISO8859P1

Then Shutdown Oracle ...
svrmgrl>connect internal as sysdba
svrmgrl>shutdown immediate
Start-up Oracle ...
svrmgrl>connect internal as sysdba
svrmgrl>startup open DatabaseName

Check that this solves the problem, and if not, it may be necessary to re-Import the Database.

2)
svrmgrl>Connect as sys
svrmgrl>update props$
set value$='WE8ISO8859P1'
where name in ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

Be sure to take a cold backup of the database before this change, because if you set it to a Non-existing Character Set, the database will not start after shutdown.

3) Specify the Character Set as part of the Create Database
US7ASCII is the default 'character set' at database creation.
To change this add the line CHARACTER SET WE8ISO8859PI to the CREATE DATABASE command.
In fact, it is good practice to include this line in every create database.
After a Database has been created, it may be necessary to export, recreate the database and re-import it.
In which case, change the NLKS_CHARACTERSET,(equivalent to NLS_LANGUAGE), parameter values in the init.ora file before recreating the database.

4) Since we8iso8859p1 is a strict superset with us7ascii it is possible to alter the Character Set for a Database without recreating it.
This facility is undocumented in Oracle 8.0.5 but try looking on MetaLink for Technical Note :66320.1
(V8: Changing the database or national character set).
This is the command that is required :-
alter database character set we8iso8859p1;
Then do the shutdown and startup again.

5) Alternatively, ALTER SESSION to alter the Character Set for the Session.
Check settings at any time at different levels as follows :-
SELECT * FROM NLS_SESSION_PARAMETERS, NLS_INSTANCE_PARAMETERS and NLS_DATABASE_PARAMETERS.



6) Use this Character Set:

AMERICAN_AMERICA.WE8ISO8859P1

As per Oracle Documentation for Specifying Character Sets :

The character encoding scheme used by the database is defined at database creation as
part of the CREATE DATABASE statement.

All data columns of type CHAR, VARCHAR2, and LONG, including columns in the data dictionary,
have their data stored in the database character set.

In addition, the choice of character set determines which characters can name objects in the database.
Once the database is created, the database character set cannot be changed without re-creating the database.
Hence, it is important to consider carefully which character set to use.

The database character set should always be a superset or equivalent of the operating system’s
native character set.

The character sets used by client applications that access the database will usually determine
which superset is the best choice.

If all client applications use the same character set, then this is the normal choice for
the database character set.

When client applications use different character sets, the database character set should be a
superset (or equivalent) of all the client character sets.
This will ensure that every character is represented when converting from a client character set
to the database character set.

When a client application operates with a terminal that uses a different character set,
then the client application’s characters must be converted to the database character set, and vice versa.

This conversion is performed automatically, and is transparent to the client application.

The character set used by the client application is defined by the NLS_LANG parameter.

WE8ISO8859P1 ISO 8859 - 1 West European
EE8ISO8859P2 ISO 8859 - 2 East European
SE8ISO8859P3 ISO 8859 - 3 South European
NEE8ISO8859P4 ISO 8859 - 4 North and North-East European

Most NLS parameters can be used in three ways:

- As initialization parameters to specify language-dependent
behavior defaults for the server.
For example, in your INIT.ORA file, include
NLS_TERRITORY = FRANCE

- As environment variables on client machines to specify
language-dependant behavior defaults for a session. These
defaults override the defaults set for the server.
For example, on a UNIX system
setenv NLS_TERRITORY FRANCE

- As ALTER SESSION parameters to change the
language-dependent behavior of a session.
These parameters override the defaults set for the session or for the server.
For example:
ALTER SESSION SET NLS_TERRITORY = FRANCE

nice article from.. http://databaseanswers.org/ora_nls_support.htm

NLS_CHARACTERSET is a database parameter that declares what
character set encoding is used to store CHAR, VARCHAR2, LONG,
and CLOB datatypes (including any SQL and PL/SQL source,
which is internally stored and processed more or less as VARCHAR2).
This parameter can be changed only in limited circumstances.

NLS_LANG is a client-side parameter that declares the character
set encoding of text that OCI gets from applications or sends to them
as content of character bind/define variables or as [PL/]SQL statements
to be executed by the DB server.
The NLS_LANG value can be overridden internally by OCI applications.

As most Oracle tools are OCI-based, NLS_LANG declares the character
set encoding in which they work, display error messages, accept
parameter files, etc.

NLS_LANG defines also language and territory conventions to be used
by many Oracle client tools and by database sessions.

Both parameters are crucial for correct working of DB in internationalized
environment. Changing one of them does not influence the other directly
but it does influence the communication between client and server
and the character set conversion that Oracle performs based
on what it thinks an application wants.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/whatsnew.htm