Character Set between Unix Oracle 9i Database and Windows2000 IIS Client: oracle, character, set

by: schwertnerPosted on 2005-01-28 at 06:45:03ID: 13163494

If the characters are encrypted you should decrypt them before sending to the client. Or this can be done on the client side.

The main problem is how you migrate to 9i. Were the data encrypted by the migration. If they were encrypted, they will not be converted to UTF8 for obvious reason - they are not legal characters.
If you migrate using Export/Import did you set NLS_LANG=.UTF on both machines?

by: schwertnerPosted on 2005-01-28 at 06:58:13ID: 13163648

You just have finalized your old question. Thanks for your points. This gives me possibility to go back to the history of your current question.
So, your data are encrypted. I will not ask you how, because I realize that this is confidential.
If you use DES or DES3 provided by DBMS_OBFUSCATION_TOOLKIT there is no chance to decrypt on 9i data encrypted on 8i.
DES is American Standart since 1974, but the programming details in every Oracle version are different.
So by the migration you should migrate the data decrypted in additional tables (if they are not row, i.e. hexadec. data).
Only after migration you have to encrypt them again and TRUNCATE the intermediate tables in order do not register the decrypted table contents in archived redo log files.

by: rpwarrenPosted on 2005-01-28 at 07:07:23ID: 13163743

I use the word "encrypt" loosly. The client does the encryption using the following function. This all seems to work ok, but the client can't read it. If relevant, How is "set NLS_LANG=.UTF" done?
****************
Public Function EncriptData(ByVal strValue, ByVal CorpId As Integer)
On Error GoTo ErrorHandler
Dim strTemp As String
Dim intI As Integer
Dim strEncriptData As String
Dim mstrEnteredData As String
Dim corpIdTemp As Double
Dim intEncriptValue As Integer
corpIdTemp = CInt(CorpId) * 17
intEncriptValue = CInt(Right(CStr(corpIdTemp), 2))
If CInt(intEncriptValue) = 0 Then
intEncriptValue = 99
End If
mstrEnteredData = strValue
strTemp = strValue
strEncriptData = ""
For intI = 1 To Len(mstrEnteredData)
strEncriptData = strEncriptData & Chr(Asc(Left(strTemp, 1)) + intEncriptValue)
strTemp = Right(strTemp, (Len(strTemp) - 1))
Next
EncriptData = strEncriptData
Exit Function
************************

Here is an example of how the function is used to write to the database:
*********************
If blnValidMaxUser Then
Max_user = EncriptData(Max_user, SerialNumber)
strSql = "Select Parm_value from Ba_Parameter where Upper(Parm_type) = 'ENTER'"
Set rsMaxuser = Conn.Execute(strSql)

If rsMaxuser.EOF Then
strSql = "Insert into Ba_Parameter (Parm_Id,Parm_value,Parm_type,Parm_mdate,Parm_mUser) Values "
strSql = strSql & "('" & Max_user & "','Maximum Users','ENTER',getdate()," & Logon_UserId & ")"
Else
strSql = "Update Ba_Parameter set Parm_Id ='" & Max_user & "' where Upper(Parm_type) = 'ENTER'"
End If

Set rsUpdateMaxuser = Conn.Execute(strSql)
UpdateMaxUser = 3
End If
**************************

by: schwertnerPosted on 2005-01-28 at 07:52:09ID: 13164232

NLS_LANG=.UTF is set in the Registry ....---->Software---->Oracle---> ...Home (on Windows) or in the environment Variables (Unix,Linux).
If NLS_LANG is not set Oracle Export/Import utilities use by default 7 bit ASCII code ... and if you do not use only English letters you loose all nonEnglish letters.

In my understanding your software do encryption. If the encrypted data are imported in VARCHAR2 columns additionally they will be either 1. truncated, because of missing NLS_LANG parameter 2. additionally transformed due the AL32UTF conversion.

So I will strongly recommend before migration to decrypt the data and to store them in additional table.
After that migrate and check the result. Read the additional table, encrypt the data and put them in the original table. Finally truncate the additional table.

by: rpwarrenPosted on 2005-01-28 at 08:07:27ID: 13164410

Does this have nothing to do with the client end? The fact is, I only have 2 records where I have important "encrypted" data, and in one small field only. I can use my ASP application to change the "encrypted" data in one of those records, but it still doesn't read it which makes me think it is a client side problem.

by: peterside7Posted on 2005-01-28 at 11:16:49ID: 13166610

Do you really need AL32UTF8, if the answer is NO, then change the database back to WE8ISO8859P1 .

Ask us if you need to know how to change the character set of your database back from AL32UTF8 to WE8ISO8859P1.
I've alreadu check the compatibility, and yes it's possible to go from AL32UTF8 to WE8ISO8859P1.

by: rpwarrenPosted on 2005-01-28 at 12:01:02ID: 13167011

unfortunately I need the AL32UTF8. The AL32UTF8 is on the customer Unix/Oracle 9i machine that I don't have access to, nor are they willing to change. I do have the original database here on my windows/8i database server (WE8ISO8859P1) with a frontend/client of Windows2000 (IIS 5.0).

On the frontend/client machine: when I change the NLS_LANG from to .UTF (from AMERICAN_AMERICA.WE8ISO8859P1), I can no longer connect through my ASP and it "acts" similar to my original problem on the customer network.

by: schwertnerPosted on 2005-01-30 at 05:39:04ID: 13175684

You can read the records on the server side and see how they look like there. If they are OK then look at the certification of the client side software.
Also be aware the AL32UTF8 is brand new character set in 9i and clients (Oracle and other software) oriented to 8i can not read this new character set. So you have to figure out if your client's software (including ADO, ODBC) is certified to be used with 9i in regard of AL32UTF8 character set. I am sure you can find on the Internet many remarks.

by: rpwarrenPosted on 2005-01-30 at 06:14:11ID: 13175810

Thanks, your answer is useful, but haven't found much related info on the web to be as useful. My customer is using Oracle 9i Client for the ODBC connection from the IIS Server hosting my Active Server Pages. I assume I should have them check and ensure NLS_LANG = AL32UTF8. Correct? Sorry, but what is "ADO", and how does one determine whether it is certified for 9i?

by: schwertnerPosted on 2005-01-31 at 00:47:35ID: 13180858

If your client uses 9i client you have no problems, because ODBC comming with 9i client can use AL32UTF8 client.

Now you have to check if ASP is certified (look at Microsoft Site for this) to work with AL32UTF8.
ADO is connection tool to connect VB, C++ with Oracle, but it seems you do not use it.

Also ask the client to select the data you need and to check if they are imported correctly.

You have to try for NLS_LANG sertting either .UTF8 or AL32UTF8. For the last I am not sure.
On my instances (about 6 - 9i, 10g) NLS_LANG is set to .UTF8 and works fine.
But we use JDBC, not ODBC.

by: rpwarrenPosted on 2005-01-31 at 11:18:07ID: 13185637

No go. Had client set Oracle 9i client on the Windows IIS server to

NLS_LANG=.UTF8 That changed the characters being returned from the .asp from "???" to "?s?" (still not right).

Next tried NLS_LANG=AL32UTF8 and got connect error:

Microsoft OLD DB Provider for ODBC Drivers (0x80004005)
[Oracle][ODBC][Ora]ORA-12705: invalid or unknown NLS
paramater value specified
/teammetrix/testconnect3.asp, line 67

Tried NLS_LANG=.AL32UTF8 and same thing, so syntax must be off. Note, the setting was initially (this morning):
UTF_LANG=AMERICAN_AMERICA\WE8MSWIN1252

Do you know what the syntax should be?

by: schwertnerPosted on 2005-02-01 at 01:02:31ID: 13190796

UTF_LANG=AMERICAN_AMERICA.WE8MSWIN1252 is the correct.
I found something on the Net, but please also try to find some advices specific to ASP and AL32UTF8. Use Google.

The correct NLS_LANG in a Windows Environment

Content:
--------
1. Key concepts/terminology.
2. How to set up my NLS_LANG
3. The correct NLS_LANG for my Windows ANSI Code Page
4. The correct NLS_LANG for my DOS / Command Prompt OEM Code Page
5. How to check the NLS_LANG
6. List of common NLS_LANG to be set in Windows registry
7. List of common character sets to be used in a command prompt
8. How Windows uses Fonts to display the different charactersets

1. Key Concepts/terminology:
----------------------------

NLS_LANG consist of: NLS_LANG=<Language>_<Territory>.<clients characterset>

This note covers the <clients characterset> part of NLS_LANG and provide
windows specific information in addition of
[NOTE:158577.1] NLS_LANG Explained (How does Client-Server Character Conversion Work?).
Please read that note first to have an idea how NLS_LANG works..

1.1 Windows and Dos Code Pages:
-------------------------------

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

1.2 Fonts:
----------

A font is a collection of glyphs (from "hieroglyphs") that share common
appearances (typeface, character size). A font is used by the operating system
to convert a numeric value into a graphical representation on screen.

A font does not necessarly contain a graphical representation for all numeric
values defined in the code page you are using.
That's why you get sometimes black squares on the screen if you change fonts and the new
that font has no representation for a certain symbol.

The Windows "Character Set Map" utility can be used to see which glyphs are part
of a certain font.
On Windows 2000:
Start -> Programs -> Accessories -> System Tools -> Character Map
or
Start -> Run...
Type "charmap", and click "ok"

A font also implements a particular code page or set of code pages.
For example, the Arial font implements the code pages 1252, 1250, 1251, 1253,
1254, 1257.

For more in-depth info on fonts see point 8 in this note.

2. How to setup my NLS_LANG:
----------------------------

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.

For a short overview, it uses the following format:

NLS_LANG = LANGUAGE_TERRITORY.CHARACTERSET

where:
LANGUAGE specifies:
- language used for Oracle messages,
- day names and month names
TERRITORY specifies:
- monetary and numeric formats,
- territory and conventions for calculating week and day numbers
CHARACTERSET:
- controls the character set used by the client application
* or it matches your Windows code page
* or it set to UTF8 for an unicode application

The list of supported character sets,languages and territory
can also be found in the Oracle9i Globalization Support Guide,
Appendix A, Locale Data
Available online at the following URL:
http://otn.oracle.com/pls/db92/db92.docindex?remark=homepage

4 important remarks:

* Setting the NLS_LANG to the characterset of the database MAY be correct
but IS NOT ALWAYS correct. Please DO NOT assume that NLS_LANG needs to be
ALWAYS the same as the database characterset. THIS IS NOT TRUE.

* The characterset defined with the NLS_LANG parameter does NOT CHANGE
your client's characterset, it is used to let Oracle know what characterset
you are USING on the client side, so Oracle can do the proper conversion.
You cannot just set NLS_LANG to the characterset you WANT.
If you need Hebrew support (for example) on an Cyrillic windows
then that windows need to be changed to have an 1255 ACP (see point 3),
just setting the nls_lang to hebrew will NOT allow you to retrieve/store hebrew.

* Another myth is that if you don't set the NLS_LANG on the client
it uses the NLS_LANG of the server. This is also NOT true!
The characterset part of the NLS_LANG parameter is never inherited from the server.
Please also see: [NOTE:241047.1] The Priority of NLS Parameters Explained.

* Note that LANGUAGE and TERRITORY have nothing to do with the abillity 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 know Japanese characters.

For <Language> and <Territory> part of NLS_LANG see referenced notes
at the end of this note and [NOTE:158577.1].

2.1 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:
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMExx\
where "xx" is the unique number identifying the Oracle home.
There you have an entry with as name NLS_LANG

When starting an Oracle tools, like sqlplusw, 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.

2.2 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 Computer' icon -> '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 of the parameters already set
in your Registry, you should not set Oracle parameters at this location unless you
have a very good reason.
Particularly note the "ORACLE_HOME" parameter that is set on unix but NOT on windows.

2.3 As an Environment variable defined in the command prompt:
-------------------------------------------------------------

If you set the NLS_LANG as an environment variable in a Command prompt,
be aware that it will overwrite the current NLS_LANG setting in the Registry
and also the System Properties.
In an MS-DOS command prompt, use the set command, for example:
C:\> set NLS_LANG=american_america.WE8PC850

3. The correct NLS_LANG for my Windows ANSI Code Page:
------------------------------------------------------

3.1 Determine your Windows 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.
see [NOTE:199926.1] How to change the ANSI Code Page (ACP) on Windows.

You'll find its value in the registry:
Start -> Run...
Type "regedit", and click "ok"
Browse the following registry entry:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\CodePage\
There you have (all the way below) an entry with as name ACP
The value of ACP is your current GUI Codepage, see the table in point 3.2
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.

Again, if you need to change the "ACP" please see:
[NOTE:199926.1] How to change the ANSI Code Page (ACP) on Windows
Do NOT simply change it 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

3.2 Find the correspondent Oracle client character set:
-------------------------------------------------------

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

ANSI CodePage (ACP) Oracle Client character set (3rd part of NLS_LANG)

1250 EE8MSWIN1250
1251 CL8MSWIN1251
1252 WE8MSWIN1252
1253 EL8MSWIN1253
1254 TR8MSWIN1254
1255 IW8MSWIN1255
1256 AR8MSWIN1256
1257 BLT8MSWIN1257
1258 VN8MSWIN1258
874 TH8TISASCII
932 JA16SJIS
936 ZHS16GBK
949 KO16MSWIN949
950 ZHT16MSWIN950

You can use UTF8 as Oracle client character set (=NLS_LANG) on Windows NT, 2000 and XP but
you will be limited to use only client programs that explicitly support this
configuration.
This is because the user interface of Win32 is not UTF8, therefore the client
*program* have to perform explicit conversions between UTF8 (used on Oracle
side) and UTF16 (used on Win32 side).
An example of such a program is Oracle Forms in version 5 and later on NT 4.0.
[NOTE:105809.1] Character Set Support for Developer Tools
or iSQLplus (from 817 onwards).
see [NOTE:231231.1] Quick setup of iSQL*Plus as unicode (UTF8) client on windows.

Contact Microsoft if you have questions about writing a unicode application.
a little (non-official, so nothing is endorsed by Oracle or Microsoft) intro:
http://www.jorendorff.com/articles/unicode/windows.html

From the other side, programs relying on ANSI Win32 API, like SQL*Plus,
older Oracle Forms , etc. cannot work with an NLS_LANG set to UTF8.

For Export / Import please see:
[NOTE:227332.1] NLS considerations in Import/Export

For euro support see:
[NOTE:68790.1] RDBMS Support for the Euro Currency Symbol

3.3 Set it in your Registry:
----------------------------

Use the Windows Registry Editor to set up the NLS_LANG in your Oracle Home
with the value you have just find above.
Section 2.1 gives you more details on how to use the Registry Editor for that
purpose.

4. The correct NLS_LANG for my DOS / Command Prompt OEM Code Page:
------------------------------------------------------------------

MS-DOS mode uses, with a few exceptions like CJK, a different code page
(called OEM code page) than Windows GUI (ANSI code page).
Meaning that before using an Oracle command line tool such as SQL*Plus
(sqlplus.exe/ plus80.exe / plus33.exe ) en svrmgrl in a command prompt
then you need to MANUALLY SET the NLS_LANG parameter as an environment
variable with the set DOS command BEFORE using the tool.

For Japanese, Korean, Simplified Chinese, and Traditional Chinese,
the MS-DOS OEM code page (CJK) is identical to the ANSI code page meaning that,
in this particular case, there is no need to set the NLS_LANG parameter in
MS-DOS mode.

In all other cases, you need to set it in order to overwrite the NLS_LANG
registry key already matching the ANSI code page. The new "MS-DOS dedicated"
NLS_LANG needs to match the MS-DOS OEM code page that could be retrieved by
typing chcp in a Command Prompt:

C:\> chcp
Active code page: 437
C:\> set NLS_LANG=american_america.US8PC437

If the NLS_LANG parameter for the MS-DOS mode session is not set appropriately,
error messages and data can be corrupted due to incorrect character set
conversion.

Use the following list to find the Oracle character set that fits to your MS-DOS
code page in use on your locale system:

MS-DOS code page Oracle Client character set (3rd part of NLS_LANG)
437 US8PC437
737 EL8PC737
850 WE8PC850
852 EE8PC852
857 TR8PC857
858 WE8PC858
861 IS8PC861
862 IW8PC1507
865 N8PC865
866 RU8PC866

For tools like sqlloader you need to set the NLS_LANG
to the characterset of the FILE you loading.

For Export / Import please see:
[NOTE:227332.1] NLS considerations in Import/Export

There is no euro support in the Command Prompt OEM Code Pages, see:
[NOTE:68790.1] RDBMS Support for the Euro Currency Symbol

5. How to check the NLS_LANG:
-----------------------------

To check the NLS_LANG, you need to open a command prompt and to run sqlplus
in command line mode.

First, 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.WE8PC850

If NLS_LANG is not set in the enviroment, you should check the value
in the registry:

SQL> @.[%NLS_LANG%].

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

(This is NOT an error but just a "trick" to get the NLS_LANG value)

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%]. "trick" reports the NLS_LANG known by the sqlplus
executable, it will not read the registry itself.
But then you are not sure if the variable is set in the enviroment or in the
registry. That's the reason of checking with the host commando first.

6. List of common NLS_LANG's used in the Windows Registry:
----------------------------------------------------------

Operating System Locale NLS_LANG Value

Arabic (U.A.E.) ARABIC_UNITED ARAB EMIRATES.AR8MSWIN1256
Bulgarian BULGARIAN_BULGARIA.CL8MSWIN1251
Catalan CATALAN_CATALONIA.WE8MSWIN1252
Chinese (PRC) SIMPLIFIED CHINESE_CHINA.ZHS16GBK
Chinese (Taiwan) TRADITIONAL CHINESE_TAIWAN.ZHT16MSWIN950
Croatian CROATIAN_CROATIA.EE8MSWIN1250
Czech CZECH_CZECH REPUBLIC.EE8MSWIN1250
Danish DANISH_DENMARK.WE8MSWIN1252
Dutch (Netherlands) DUTCH_THE NETHERLANDS.WE8MSWIN1252
Dutch (belgium) DUTCH_BELGIUM.WE8MSWIN1252
English (United Kingdom) ENGLISH_UNITED KINGDOM.WE8MSWIN1252
English (United States) AMERICAN_AMERICA.WE8MSWIN1252
Estonian ESTONIAN_ESTONIA.BLT8MSWIN1257
Finnish FINNISH_FINLAND.WE8MSWIN1252
French (Canada) CANADIAN FRENCH_CANADA.WE8MSWIN1252
French (France) FRENCH_FRANCE.WE8MSWIN1252
German (Germany) GERMAN_GERMANY.WE8MSWIN1252
Greek GREEK_GREECE.EL8MSWIN1253
Hebrew HEBREW_ISRAEL.IW8MSWIN1255
Hungarian HUNGARIAN_HUNGARY.EE8MSWIN1250
Icelandic ICELANDIC_ICELAND.WE8MSWIN1252
Indonesian INDONESIAN_INDONESIA.WE8MSWIN1252
Italian (Italy) ITALIAN_ITALY.WE8MSWIN1252
Japanese JAPANESE_JAPAN.JA16SJIS
Korean KOREAN_KOREA.KO16MSWIN949
Latvian LATVIAN_LATVIA.BLT8MSWIN1257
Lithuanian LITHUANIAN_LITHUANIA.BLT8MSWIN1257
Norwegian NORWEGIAN_NORWAY.WE8MSWIN1252
Polish POLISH_POLAND.EE8MSWIN1250
Portuguese (Brazil) BRAZILIAN PORTUGUESE_BRAZIL.WE8MSWIN1252
Portuguese (Portugal) PORTUGUESE_PORTUGAL.WE8MSWIN1252
Romanian ROMANIAN_ROMANIA.EE8MSWIN1250
Russian RUSSIAN_CIS.CL8MSWIN1251
Slovak SLOVAK_SLOVAKIA.EE8MSWIN1250
Spanish (Spain) SPANISH_SPAIN.WE8MSWIN1252
Swedish SWEDISH_SWEDEN.WE8MSWIN1252
Thai THAI_THAILAND.TH8TISASCII
Spanish (Mexico) MEXICAN SPANISH_MEXICO.WE8MSWIN1252
Spanish (Venezuela) LATIN AMERICAN SPANISH_VENEZUELA.WE8MSWIN1252
Turkish TURKISH_TURKEY.TR8MSWIN1254
Ukrainian UKRAINIAN_UKRAINE.CL8MSWIN1251
Vietnamese VIETNAMESE_VIETNAM.VN8MSWIN1258

7. List of common NLS_LANG's used in the Command Prompt (DOS box):
------------------------------------------------------------------

Operating System Locale Oracle Client character set (3rd part of NLS_LANG)

Arabic AR8ASMO8X
Catalan WE8PC850
Chinese (PRC) ZHS16GBK
Chinese (Taiwan) ZHT16MSWIN950
Czech EE8PC852
Danish WE8PC850
Dutch WE8PC850
English (United Kingdom) WE8PC850
English (United States) US8PC437
Finnish WE8PC850
French WE8PC850
German WE8PC850
Greek EL8PC737
Hebrew IW8PC1507
Hungarian EE8PC852
Italian WE8PC850
Japanese JA16SJIS
Korean KO16MSWIN949
Norwegian WE8PC850
Polish EE8PC852
Portuguese WE8PC850
Romanian EE8PC852
Russian RU8PC866
Slovak EE8PC852
Slovenian EE8PC852
Spanish WE8PC850
Swedish WE8PC850
Turkish TR8PC857

8. How Windows uses Fonts to display the different charactersets:
-----------------------------------------------------------------

We assume you have an UTF8 database with correctly stored UTF8 codepoints.

On Windows there are two kinds of tools / applications:

1)A fully Unicode enabled applications which accepts Unicode codepoints and
which can render them. It's the application that needs to deal with the Unicode,
Windows provides the unicode API but the GUI system itself is NOT Unicode
"by nature".
A fully Unicode application can only show one glyph for a given Unicode
code point. So there is NO confusion possible here, this application will need
to use a full unicode font. If you have a full unicode application, then you
need to set the NLS_LANG to UTF8.

Note that there are currently NOT many applications like this and if it's not
explicitly mentioned by the vendor it's most likely an ANSI application (see
below). So DON'T set the NLS_LANG to UTF8 if you are not sure!

The only Unicode capable client that is included in the database is iSQLPLus.
See [NOTE:231231.1] Quick setup of iSQL*Plus as unicode client on windows.
for a guide on how to setup this.

2) An standard ANSI application (like sqlplusw.exe) cannot use Unicode
code points. So the Unicode code point stored in the database needs to be
CONVERTED to a ANSI code point. This is done by setting NLS_LANG (as described
in further on in this note and in [NOTE:158577.1].
This allows oracle to map the unicode point to the characterset of the client,
(and here comes the tricky part)but this is NOT the same as a font.

If you want to display Arabic for example then you need to set the Windows
characterset to Arabic. That way Windows knows what are valid codepoints and
can use the FONT engine to DISPLAY the codepoints (this results in glyphs).
Windows passes the codepoint and the "page" to the rendering engine.
This "page" defines the glyphs for the codepoints for a certain
characterset/codepage.

Because there are only 256 possible positions for a ANSI application, and one
font contains normally glyphs for different languages this "page" is used to
select from a FONT that has (for example) all the glyphs for Cyrillic, Arabic
and West-European the "page" for arabian.

So lets say you have a Arabic setup that works, you change manually the "Page"
of a FONT and ask to display the glyph for ANSI codepoint XX. Now 1 of 2 things
can happen:

1) There is a character defined on that position for the CHARACTERSET of that
"Page", so the creator of the font has forseen a glyph and this is displayed
(but this is NOT the character expected or wanted as its stored as a different
character in the database!).

2) There is NO character defined on that position for the CHARACTERSET of that
"Page" so the creator of the font has NOT forseen a glyph and you get "garbage"
or black squares (normally you should see a black square but a ? or ? are also
possible, this depends on the error handling defined in the FONT).

The above is also possible if you have an non-Unicode characterset for the
database.

For more information see also:
[NOTE:137127.1] Character Sets, Code Pages, Fonts and the NLS_LANG Value

Related Documents:
==================
[NOTE:241047.1] The Priority of NLS Parameters Explained.
[NOTE:158577.1] NLS_LANG Explained (How does Client-Server Character Conversion Work?)
[NOTE:137127.1] Character Sets, Code Pages, Fonts and the NLS_LANG Value
<Note.199926.1> How to change the ANSI Code Page (ACP) on Windows NT 4.0 and Windows 2000

[NOTE:226558.1] An example inserting cyrillic data into a database on west european windows.

[NOTE:223706.1] Using Locale Builder to view the definition of character sets
[NOTE:132453.1] How to Change the Displayed Font in SQL*PLUS (GUI) on WinNT
[NOTE:231231.1] Quick setup of iSQL*Plus as unicode (UTF8) client on windows.
[NOTE:165259.1] How to set NLS Variables for different Applications using the same ORACLE_HOME

- Oracle8i Installation Guide for Windows NT, Part Number A85302-01
Appendix D - National Language Support
http://otn.oracle.com/documentation/oracle8i.html

- Oracle9i Database Installation Guide for Windows, Part Number A90162-01
Appendix E - Globalization Support
http://otn.oracle.com/documentation/oracle9i.html

- This Microsoft web site:
http://www.microsoft.com/globaldev/reference/oslocversion.mspx
provides a list of the default code pages for all Windows versions.

- updating/changing the NLS_LANG in the registry to the correct value:
this can easely be done by importing a .reg file who will then delete the old and add the new registry value.
like shown in http://www.winguides.com/article.php/8/

like:

REGEDIT4

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\]
"NLS_LANG"=-
[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0]
"NLS_LANG"=-
"NLS_LANG"="DUTCH_BELGIUM.WE8MSWIN1252"

For further NLS / Globalization information you may start here:
[NOTE:150091.1] Globalization Technology (NLS) Library index
.

by: rpwarrenPosted on 2005-02-23 at 06:20:16ID: 13382401

still working this, but will close and start a new question when ready.

Question

Character Set between Unix Oracle 9i Database and Windows2000 IIS Client

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

oracle

character

set

Oracle Database

Font Creator

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Need individual assistance?

Our experts are ready to help.

Want to learn from the best?

Read articles from industry experts.

Working on a long term project?

Store your work and research.

What Makes Experts Exchange Unique?

Trusted by the world's most respected brands.

Faithfully serving IT professionals since 1996.

Related Solutions

Free Tech Articles

Cloud Class Webinars

Join the Community

Give a Little. Get a Lot.

Answers

3 Ways to Join

The Experts

The Experts

Testimonials

Testimonials

Testimonials

Business Clients

Business Clients

In the Press

In the Press

In the Press