Oracle кодировка базы как посмотреть

A locale is a set of information addressing linguistic and cultural requirements that corresponds to a given language and country. Traditionally, the data associated with a locale provides support for formatting and parsing of dates, times, numbers, and currencies, etc. Providing current and correct locale data has historically been the responsibility of each platform owner or vendor, leading to inconsistencies and errors in locale data.

Setting the NLS_LANG environment parameter is the simplest way to specify locale behavior for Oracle software. It sets the language and territory used by the client application and the database server. It also indicates the client's character set, which corresponds to the character set for data to be entered or displayed by a client program.

NLS_LANG is set as a local environment variable on UNIX platforms. NLS_LANG is set in the registry on Windows platforms.

The NLS_LANG parameter has three components: language, territory, and character set. Specify it in the following format, including the punctuation:
NLS_LANG = language_territory.charset

Each component of the NLS_LANG parameter controls the operation of a subset of globalization support features:

Language
Specifies conventions such as the language used for Oracle messages, sorting, day names, and month names. Each supported language has a unique name; for example, AMERICAN , FRENCH , or GERMAN . The language argument specifies default values for the territory and character set arguments. If the language is not specified, then the value defaults to AMERICAN

Territory
Specifies conventions such as the default date, monetary, and numeric formats. Each supported territory has a unique name; for example, AMERICA , FRANCE , or CANADA . If the territory is not specified, then the value is derived from the language value.

Charset
Specifies the character set used by the client application (normally the Oracle character set that corresponds to the user's terminal character set or the OS character set). Each supported character set has a unique acronym, for example, US7ASCII , WE8ISO8859P1 , WE8DEC , WE8MSWIN1252 , or JA16EUC . Each language has a default character set associated with it.

Note:

All components of the NLS_LANG definition are optional; any item that is not specified uses its default value. If you specify territory or character set, then you must include the preceding delimiter [underscore (_) for territory, period (.) for character set]. Otherwise, the value is parsed as a language name.

For example, to set only the territory portion of NLS_LANG , use the following format: NLS_LANG=_JAPAN

The remainder of this document will focus on the charset component of the NLS_LANG setting, as it is the least understood and most important piece to set correctly.

Common NLS_LANG Myths

Setting the NLS_LANG to the character set of the database MAY be correct but IS often not correct. DO NOT assume that NLS_LANG needs to be the same as the database character set. THIS IS OFTEN NOT TRUE.
The character set defined with the NLS_LANG parameter does NOT CHANGE your client's character set. It is used to let Oracle know what character set you are USING on the client side, so Oracle can do the proper conversion. You cannot change the character set of your client by using a different NLS_LANG!
If you don't set the NLS_LANG on the client it uses the NLS_LANG of the server. This is also NOT true! For example, if the Oracle Installer does not populate NLS_LANG , and it is not otherwise set then its value by default is A MERICAN_AMERICA.US7ASCII . The language is AMERICAN , the territory is AMERICA , and the character set is US7ASCII.
Setting the LANGUAGE and TERRITORY parameters of NLS_LANG has nothing to do with the ability to store characters in a database. A NLS_LANG set to JAPANESE_JAPAN.WE8MSWIN1252 will not allow you to store Japanese, as WE8MSWIN1252 doesn't support Japanese characters. However a NLS_LANG set to AMERICAN_AMERICA.JA16SJIS will allow you to store Japanese providing the input data is truly JA16SJIS and if the database is also in a character set that can store Japanese like UTF8 or JA16SJIS)

Checking the current NLS_LANG Setting

In many cases the NLS_LANG has been already set during the Oracle install or thereafter manually. To be sure you can use these methods to get back the value of NLS_LANG for SQL*Plus:

SQL> HOST ECHO $NLS_LANG

This returns the value of the parameter.

On Windows:
On Windows you have two possible options, normally the NLS_LANG is set in the registry, but it can also be set in the environment, however this is not often done. The value in the environment takes precedence over the value in the registry and is used for ALL Oracle_Homes on the server. Also note that any USER environment variable takes precedence over any SYSTEM environment variable (this is Windows behavior, and has nothing to do with Oracle) if set

To check if it's set in the environment:

SQL> HOST ECHO %NLS_LANG%

If this reports just %NLS_LANG% back, the variable is not set in the environment.

If it's set it reports something like

ENGLISH_UNITED KINGDOM.WE8ISO8859P1
If NLS_LANG is not set in the environment, check the value in the registry:

If you get something like:
Unable to open file.[ENGLISH_UNITED KINGDOM.WE8ISO8859P1].
The "file name" between the braces is the value of the registry parameter.

If you get this as result:
Unable to open file ".[%NLS_LANG%]." then the parameter NLS_LANG is also not set in the registry.

Note the @.[%NLS_LANG%]. technique reports the NLS_LANG known by the SQL*Plus executable, it will not read the registry itself. But if you run the HOST command first and the NLS_LANG is not set in the environment then you can be sure the variable is set in the registry if the @.[%NLS_LANG%]. returns a valid value.

All other NLS parameters can be retrieved by a:

SELECT * FROM NLS_SESSION_PARAMETERS;

Note:

SELECT USERENV (‘language’) FROM DUAL; gives the session’s <Language>_<territory> but the DATABASE character set not the client, so the value returned is not the client’s complete NLS_LANG setting!

The Priority of NLS Parameters related to NLS_LANG

This section explains the order in which NLS parameters are taken into account in the database client/server model. (This does NOT cover Thin JDBC connections)

There are 3 levels at which you can set NLS parameters: Database, Instance and Session. If a parameter is defined at more than one level then the rules on which one takes precedence are quite straightforward:

1. NLS database settings are superseded by NLS instance settings
2. NLS database & NLS instance settings are superseded by NLS session settings

Session Parameters

These are the settings used for the current SQL session.

These reflect (in this order):

1) The values of NLS parameters set by "ALTER SESSION "
ALTER SESSION set NLS_DATE_FORMAT = 'DD/MM/YYYY';
2) If there is no explicit "ALTER SESSION " statement done then it reflects the setting of the corresponding NLS parameter on the client derived from the NLS_LANG variable.
3) If NLS_LANG is specified with only the part then AMERICAN is used as default .

So if you set NLS_LANG=_BELGIUM. WE8MSWIN1252 then you get this:

NLS_LANGUAGE AMERICAN
NLS_TERRITORY BELGIUM
NLS_CURRENCY <euro sign here>
NLS_ISO_CURRENCY BELGIUM

Note:

The difference between NLS_LANG=_BELGIUM.WE8MSWIN1252 (correct) and

NLS_LANG=BELGIUM.WE8MSWIN1252 (incorrect), you need to set the «_» as separator.

So if you set NLS_LANG=ITALIAN_.WE8MSWIN1252 then you get this:

NLS_LANGUAGE ITALIAN
NLS_TERRITORY ITALY
NLS_CURRENCY <euro sign here>
NLS_ISO_CURRENCY ITALY

Note:

Note the difference between NLS_LANG=ITALIAN_.WE8MSWIN1252 (correct) and

NLS_LANG=ITALIAN.WE8MSWIN1252 (incorrect), you need to set the «_» as separator.

So if you set NLS_LANG=.WE8MSWIN1252 then you get this:

NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA

NLS_SORT, NLS_DATE_FORMAT, etc. can be set as a "standalone" setting and will overrule the defaults derived from NLS_LANG _ part.

So if you set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252 and NLS_ISO_CURRENCY=FRANCE then you get this:

NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY FRANCE

Defaults:
* If NLS_DATE_LANGUAGE or NLS_SORT are not set then they are derived from
NLS_LANGUAGE.
* If NLS_CURRENCY, NLS_DUAL_CURRENCY, NLS_ISO_CURRENCY, NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT, NLS_TIMESTAMP_TZ_FORMAT, NLS_NUMERIC_CHARACTERS are not set then they are derived from NLS_TERRITORY

<Language>_<Territory>.US7ASCII and the values for the
<Language>_<Territory> part used are the ones found in

NLS_INSTANCE_PARAMETERS. Parameters like NLS_SORT defined as "standalone" on the client side are ignored.

Instance Parameters

These are the settings in the init.ora of the database at the moment that the database was started or set through ALTER SYSTEM.

If the parameter is not explicitly set in the init.ora or defined by ALTER SYSTEM then its value is NOT derived from a "higher" parameter (we are talking about parameters like NLS_SORT that derive a default from NLS_LANGUAGE in NLS_SESSION_PARAMETERS, this is NOT the case for NLS_INSTANCE_PARAMETERS)

Database Parameters

Defaults to AMERICAN_AMERICA if there are no parameters explicitly set in the init.ora during database creation time. If there is parameters set in the init.ora during database creation you see them here. There is no way to change these after the database creation. Do NOT attempt to update system tables to bypass these settings! These settings are used to give the database a default if the INSTANCE and SESSION parameters are not set.

Note:

* NLS_LANG is not an init.ora parameter, NLS_LANGUAGE and NLS_TERRITORY are.

So you need to set NLS_LANGUAGE and NLS_TERRITORY separately.

* These parameters are overridden by NLS_INSTANCE_PARAMETERS and NLS_SESSION_PARAMETERS.

* You cannot define the <clients character set> or NLS_LANG in the init.ora. The client character set is defined by the NLS_LANG on the client OS.

* You cannot define the database character set in the init.ora.

The database (national) character set NLS_(NCHAR)_CHARACTERSET) is defined by the «Create Database» command.

* The NLS_CHARACTERSET and NLS_NCHAR_CHARACTERSET parameters cannot be overridden by instance or session parameters.

They are defined by the value specified in the «CREATE DATABASE command and are not intended to be changed afterwards dynamically. Do NOT update system tables to change the character set. This can corrupt your database and potentially make it impossible to open the database again.

* Setting the NLS_LANG during the creation of the database does not influence the NLS_DATABASE_PARAMETERS.

* The NLS_LANG set during the database creation has NO impact on the database National Characterset.

Additional SELECT statements:
A) SELECT name,value$ from sys.props$ where name like '%NLS%';
This gives the same info as NLS_DATABASE_PARAMETERS.
You should use NLS_DATABASE_PARAMETERS instead of props$.
Note the UPPERCASE '%NLS%'

B) SELECT * from v$nls_parameters;
This view shows the current session parameters and the *DATABASE* characterset as seen in the NLS_DATABASE_PARAMETERS view.

C) SELECT name,value from v$parameter where name like '%NLS%';
This view gives the same information as NLS_INSTANCE_PARAMETERS.
Note the LOWERCASE '%NLS%'

D) SELECT userenv ('language') from dual;
and
SELECT sys_context('userenv','language') from dual;
Both these SELECT statements give the session's _ and the
DATABASE character set. The database character set is not the same as the character set of the NLS_LANG that you started this connection with! So don't be fooled, although the output of this query looks like the value of a NLS_LANG variable, it is NOT.

E) SELECT userenv ('lang') from dual;
This SELECT gives the short code that Oracle uses for the Language defined by NLS_LANGUAGE setting for this session. If NLS_LANGUAGE is set to French then this will return "F", if NLS_LANGUAGE is set to English then this will return "GB"
If NLS_LANGUAGE is set to American then this will return "US", and so on.

F) SHOW parameter NLS%
This will give the same as the NLS_INSTANCE_PARAMETERS

An example of a wrong NLS_LANG setup

A database is created on a UNIX system with the US7ASCII character set. A Windows client connecting to the database works with the WE8MSWIN1252 character set (regional settings -> Western Europe /ACP 1252) and the DBA, use the UNIX shell (ROMAN8) to work on the database. The NLS_LANG is set to american_america.US7ASCII on the clients and the server.

A very important point (as mentioned before):

When the client NLS_LANG character set is set to the same value as the database character set, Oracle assumes that the data being sent or received are of the same (correct) encoding, so no conversions or validations may occur for performance reasons. The data is just stored as delivered by the client, bit by bit.

From Windows insert an ‘é’ (LATIN SMALL LETTER E WITH ACUTE) into a table NLS_TEST containing one column ‘TEST’ of the type 'char'.

As long as you insert into and select from the column on Windows with the WE8MSWIN1252 character set everything runs smoothly. No conversion is done and 8 bits are inserted and read back, even if the character set of the database is defined as 7 bits. This happens because a byte is 8 bits and Oracle is ALWAYS using 8 bits even with a 7 bit character set. In a correct setup the most Significant Bit is just not used and only 7 bits are taken into account.

For one reason or another you need to insert from the UNIX server. When you SELECT from tables where data is inserted by the Windows clients you get a ‘Ò’ (LATIN CAPITAL LETTER O WITH TILDE) instead of the ‘é’.

If you insert ‘é’ on the UNIX server and you SELECT the row at the Windows client you get an ‘Å’ (LATIN CAPITAL LETTER A WITH RING ABOVE) back.

Bottom line is that you have INCORRECT data in the database. You store the numeric value for ‘é’ of the WE8MSWIN1252 character set in the database but you tell Oracle this is US7ASCII data, so Oracle is NOT converting anything and just stores the numeric value (again: Oracle thinks that the client is giving US7ASCII codes because the NLS_LANG is set to US7ASCII, and the database character set is also US7ASCII -> no conversion done).

When you SELECT the same column back on the UNIX server, Oracle is again expecting that the value is correct and passes the value to the UNIX terminal without any conversion.

Now the problem is that in the WE8MSWIN1252 character set the ‘é’ has the hexadecimal value 'E9’and in the Roman8 character set the hexadecimal value for ‘é’ is 'C5'. Oracle just passes the value stored in the database ('E9') to the UNIX terminal, and the UNIX terminal thinks this is the letter ‘?’ because in its (Roman8) character set the hexadecimal value 'E9' is representing the letter ‘Ò’. So instead of the ‘é’ you get ‘Ò’ on the UNIX terminal screen.

The inverse (the insert on the UNIX and the SELECT on the Windows client) is the same story, but you get other results.

The solution is creating the database with a character set that contains ‘é’

(WE8MSWIN1252, WE8ISO89859P1, UTF-8, etc.) and setting the NLS_LANG on the client to WE8MSWIN1252 and on the server to WE8ROMAN8. If you then insert an ‘é’ on both sides, you will get an ‘é’ back regardless of where you SELECT them. Oracle knows then that a hexadecimal value of 'C5’ inserted by the UNIX and an 'E9’ from a WE8MSWIN1252 client are both ‘é’ and inserts ‘é’ into the database (the code in the database depends on the character set you have chosen).

You don't have to switch between UNIX, Windows or other OS clients to run into this kind of problem. The same problem appears if you add Windows clients that are using another character set and have an incorrect NLS_LANG set.

How to setup the NLS_LANG Properly for UNIX

To specify the locale behavior of your client Oracle software, you have to set your NLS_LANG parameter. It sets the language, territory and also the character set of your client. You need to check the locale environment settings to set your NLS_LANG 3rd field (character set) in accordance with it. To do this, use the "locale" command like this:

The output of this command is not exactly the same on all the Unix environments. On some platforms, it can be useful to use the following syntax to have more details about the codepage really used:

$ locale LC_CTYPE | head

In these cases, the NLS_LANG 3rd field should be set to WE8ISO8859P15. On Solaris, AIX, TRU64, this syntax doesn't give interesting complementary information. To find more details about these settings:

On Solaris, look in /usr/lib/locale
On AIX, look in /usr/lib/nls/README
On TRU64, look in /usr/lib/nls
On HP-UX, look in /usr/lib/nls/config
On Linux, look in /usr/share/locale/locale.alias

To set a chosen value for these "locale" settings, it's needed to know which values are available. To know that, use the following syntax:

Then, when you have chosen a value, for example UTF-8 on Linux, you can set it like this:

% setenv LC_ALL UTF-8

In this case, the 3rd field (character set) of NLS_LANG should be set to UTF8.

% setenv NLS_LANG American_America.UTF8

How to setup the NLS_LANG Properly for Windows and DOS Code Pages

On Windows systems, the encoding scheme (character set) is specified by a code page. Code pages are defined to support specific languages or groups of languages, which share common writing systems. From Oracle point of view the terms code page and character set mean the same. Note that in non Chinese-Japanese-Korean environments, the Windows GUI and DOS command prompt do not use the same code page.

As a result Windows uses 2 different character sets for the ANSI (sqlplusw.exe) and the OEM (dos box — sqlplus.exe) environments.

Where to set the NLS_LANG in Windows

In the Registry:
On Windows systems, you should make sure that you have set an NLS_LANG registry subkey for each of your Oracle Homes:
You can easily modify this subkey with the Windows Registry Editor:
Start -> Run.
Type "regedit", and click "ok"
Edit the following registry entry:

For Oracle version 7:

For Oracle Database versions 8, 8i and 9i:

where "x" is the unique number identifying the Oracle home.

HOME0 is the first installation

For Oracle Database 10g:

There you have an entry with name NLS_LANG

When starting an Oracle tools, like SQL*Plusw, it will read the content of the oracle.key file located in the same directory to determine which registry tree will be used, therefore which NLS_LANG subkey will be used.

Note:

Some people are confused by finding a NLS_LANG set to «NA» in HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE when no version 7 was installed. This is used for backwards compatibility, and can be ignored.

As a System or User Environment Variable, in System properties:

Although the Registry is the primary repository for settings on Windows, it is not the only place where parameters can be set. Even if not at all recommended, you can set the NLS_LANG as a System or User Environment Variable in the System properties.

This setting will be used for ALL Oracle homes.

To check and modify them:

Right-click the 'My Computericon -> 'Properties'

Select the 'Advanced Tab -> Click on 'Environment Variables'

The 'User Variables list contains the settings for the specific OS user currently logged on and the 'System variables system-wide variables for all users.

Since these environment variables take precedence over the parameters already set in your Registry, you should not set Oracle parameters at this location unless you have a very good reason.

As an Environment variable defined in the command prompt:

Before using an Oracle command line tool you need to MANUALLY SET the NLS_LANG parameter. In an MS-DOS command prompt, use the set command, for example:

C:\> set NLS_LANG=american_america.WE8PC850

Determine your Windows ANSI code page

Now that you know what the NLS_LANG is currently set to you can check to see if it properly agrees with the current ANSI code page. The ACP (ANSI Code Page) is defined by the "default locale" setting of Windows, so if you have a UK Windows 2000 client and you want to input Cyrillic (Russian) you need to change the ACP (by changing the "default locale") in order to be able to input Russian.

You'll find its value in the registry:

Browse the following registry entry:

There you have (all the way down) an entry with as name ACP. The value of ACP is your current GUI Codepage, for the mapping to the Oracle name. Since there are many registry entries with very similar names, please make sure that you are looking at the right place in the registry.

Additionally, the following URL provides a list of the default code pages for all Windows versions:

http://www.microsoft.com/globaldev/reference/ (under the REFERENCE tab on the left of the page)

OEM = the command line codepage, ANSI = the GUI codepage

Note that the Honk Kong HKSCS is listed here: http://www.microsoft.com/hk/hkscs/

Find the correspondent Oracle client character set:

Find the Oracle client character set in the table below based on the ACP you found above. Note that there is only ONE CORRECT value for a given ACP.

How to get the character set of a database in oracle

A character set determines what languages can be represented in the database.

Oracle recommends Unicode AL32UTF8 as the database character set. Unicode is the universal character set that supports most of the currently spoken languages of the world.

About Admin

One thought on “How to get the character set of a database in oracle”

It is very difficult to change characterset after installation. Using above posted query it is easy to get characterset.

How can I tell if my Oracle system is set to support Unicode or multibyte characters?

I understand that Oracle supports multiple character sets, but how can determine if the current 11g system where I work has that functionality enabled?

2 Answers 2

will show you the database and national character set. The database character set controls the encoding of data in CHAR and VARCHAR2 columns. If the database supports Unicode in those columns, the database character set should be AL32UTF8 (or UTF8 in some rare cases). The national character set controls the encoding of data in NCHAR and NVARCHAR2 columns. If the database character set does not support Unicode, you may be able to store Unicode data in columns with these data types but that generally adds complexity to the system— applications may have to change to support the national character set.

How to find the Character set of the Oracle database.

The database character set in oracle determines the set of characters can be stored in the database. It is also used to determine the character set to be used for object identifiers and PL/SQL variables and for storing PL/SQL program source.

The database character set information is stored in the data dictionary tables named SYS.PROPS$.

You can get the character set used in the database by SYS.PROPS$ table or any other views (like database_properties/ nls_database_parameters) exist in the database. The parameter NLS_CHARACTERSET value contains the database character set name.

As ‘show parameter’ does not tell you your database char set, Here I’m listing some commands to find the database character set:

Oracle кодировка базы как посмотреть