On Wed, 30 Oct 2002 09:18:40 +0100, "Kantix"
<francesco.cantisano_at_tin.it> wrote:
>Hi to all,
>we have some problems with the '?' character; we have all db's 8.1.7 (unix
>servers) with che WE8ISO8859P1. We access the db with JDBC thin driver (rel
>8) and we had problems.
>We have migrated one server to 9iR2 and, using the JDBC thin driver (rel
>9.2), the
>developers have problems retrieving clobs containing the '?' character.
>They gets (in a varray) the 'clob' not in 'characters' but in bynary format
>(each character coded in exadecimal).
>Has someone encountered the same problem?Any hints?
>I've read that the character set to use ? should be WE8ISO8859P15 but I'm
>afraid that, to change the charset, I should export/import db's (and, why if
>we are working with the WE8ISO8859P1 since two years without problems?)
>Can someone explain me why this happens?
>Thanks to all
>Francesco
>
>
From Metalink
Bookmark Fixed font Go to End
Doc ID: Note:68790.1
Subject: RDBMS Support for the Euro Currency Symbol
Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 25-FEB-1999
Last Revision Date: 12-AUG-2002
- Overview =========== On the 1st January 1999, the new currency of
the European Union, the "euro" was introduced, this document describes
how the Oracle Database Server provides support for this new symbol.
All characters have both a graphical (visual) and storage
representation, this document mainly focuses on the storage aspects,
you will need to refer to your operating system or application vendor
for visual support considerations. The euro symbol is the same as any
other character, it is a member of a set of elements that make up a
code page (Oracle Character Set) and must be have a well-defined code
point on the code page that is in use. A number of different
standards bodies exist who are each responsible for their own code
pages, they determine which characters are included on which code
pages and at what code point. When an Oracle database is created a
character set is defined (based on one of the standard code pages),
from this point on the only characters that can be stored are those
defined in the character set. It is important to be aware that any of
the available character sets can be used to create the database even
if the server itself is not able to display these characters, as long
as the client application/ operating system can. The following
client/server considerations should be noted when using the euro
symbol: - If the client application attempts to store a character
such as the euro that is not defined in the database character set
a replacement is selected and the clients representation will be
lost. - If the client uses a different code page to the databases
character set the euro symbol can still be stored as long as both
code pages have a code point defined for the euro symbol. The
client must also have the character set component of NLS_LANG set
to the client's character set. 2. Code Page/Character Set support
================================== The following tables define the
code pages that have a code point defined by the relevant standards
body for the euro symbol and can be used as the Oracle database
character set at creation time. All of the following character sets
are available under the 8.1 release, see the 8.0 / 7.3.4 sections of
this article for information pertaining to earlier releases. 2.1 ISO
------- The ISO/IEC 8859 standard parts 1-15 specifies coded
character sets which comprise various different selections of
characters based on the Latin alphabet. Each set is made up of 191
coded graphic characters used for general purpose applications.
+---------------+-------------------------------------------+--------------+
| Oracle | Description | Euro
| | Character Set | | Code
Point |
+---------------+-------------------------------------------+--------------+
| EL8ISO8859P7 | ISO 8850-P7 8-bit Latin / Greek | 0xA4
(164) |
+---------------+-------------------------------------------+--------------+
| WE8ISO8859P15 | ISO 8850-P15 8-bit West European | 0xA4
(164) |
+---------------+-------------------------------------------+--------------+
By the end of January 1999 the ISO standards body had completed a
ballot of its members asking them to vote on the inclusion of the EURO
SIGN into the Latin 1 (P1) and Latin (P4) code pages, this ballot was
unsuccessful. This means that Oracle is unable to provide support for
the EURO SIGN in these character sets, databases have to be created
using Latin 7 or Latin 15 to be able to store the EURO SIGN. 2.2 IBM
Code Pages ------------------ IBM introduced a group of new EBCDIC
code pages to support the EURO SIGN, these are all modelled after
existing IBM/EBCDIC code pages.
+---------------+----------------------------------+-----------+--------------+
| Oracle | Description | Euro Code |
Based-On | | Character Set | |
Point | |
+---------------+----------------------------------+-----------+--------------+
| WE8EBCDIC1140 | EBCDIC 1140 8-bit West European | 0x9F |
WE8EBCDIC37 |
+---------------+----------------------------------+-----------+--------------+
| D8EBCDIC1141 | EBCDIC 1141 8-bit Austrian German| 0x9F |
D8EBCDIC273 |
+---------------+----------------------------------+-----------+--------------+
| DK8EBCDIC1142 | EBCDIC 1142 8-bit Danish | 0x5A |
DK8EBCDIC277 |
+---------------+----------------------------------+-----------+--------------+
| S8EBCDIC1143 | EBCDIC 1143 8-bit Swedish | 0x5A |
S8EBCDIC278 |
+---------------+----------------------------------+-----------+--------------+
| I8EBCDIC1144 | EBCDIC 1144 8-bit Italian | 0x9F |
I8EBCDIC280 |
+---------------+----------------------------------+-----------+--------------+
| WE8EBCDIC1145 | EBCDIC 1145 8-bit | 0x9F |
WE8EBCDIC284 | | | Latin American Spanish |
| |
+---------------+----------------------------------+-----------+--------------+
| WE8EBCDIC1146 | EBCDIC 1146 8-bit West European | 0x9F |
WE8EBCDIC258 |
+---------------+----------------------------------+-----------+--------------+
| F8EBCDIC1147 | EBCDIC 1147 8-bit French | 0x9F |
WE8EBCDIC297 |
+---------------+----------------------------------+-----------+--------------+
| WE8EBCDIC1148 | EBCDIC 1148 8-bit West European | 0x9F |
WE8EBCDIC500 |
+---------------+----------------------------------+-----------+--------------+
| WE8PC858 | IBM-PC 850 8-bit West European | 0xDF |
WE8PC850 |
+---------------+----------------------------------+-----------+--------------+
The "Based-On" code pages above and other EBCDIC code pages available
in earlier releases can not be used to store or use the EURO SIGN, a
new database must be created with one of the new character sets above
to support the EURO SIGN. These character sets can only be used on
operating systems where the compiler supports EBCDIC. 2.3 Microsoft
Code Pages --------------------------------- Microsoft has enhanced
the following windows code pages to include the EURO SIGN.
+---------------+-----------------------------------------------+------------+
| Oracle | Description |
Euro | | Character Set |
| Code Point |
+---------------+-----------------------------------------------+------------+
| EE8MSWIN1250 | MS Windows Code Page 1250 8-bit East European | 0x80
(128) |
+---------------+-----------------------------------------------+------------+
| CL8MSWIN1251 | MS Windows Code Page 1251 8-bit Latin/Cyrillic| 0x88
(136) |
+---------------+-----------------------------------------------+------------+
| WE8MSWIN1252 | MS Windows Code Page 1252 8-bit West European | 0x80
(128) |
+---------------+-----------------------------------------------+------------+
| EL8MSWIN1253 | MS Windows Code Page 1253 8-bit Latin/Greek | 0x80
(128) |
+---------------+-----------------------------------------------+------------+
| TR8MSWIN1254 | MS Windows Code Page 1254 8-bit Turkish | 0x80
(128) |
+---------------+-----------------------------------------------+------------+
| IW8MSWIN1255 | MS Windows Code Page 1255 8-bit Latin/Hebrew | 0x80
(128) |
+---------------+-----------------------------------------------+------------+
| AR8MSWIN1256 | MS Windows Code Page 1256 8-bit Latin/Arabic | 0x80
(128) |
+---------------+-----------------------------------------------+------------+
| BLT8MSWIN1257 | MS Windows Code Page 1257 8-bit Baltic | 0x80
(128) |
+---------------+-----------------------------------------------+------------+
The EURO SIGN can only be stored in databases created with the above
Microsoft code pages. 2.4 Unicode ----------- The EURO SIGN is not
defined in Unicode version 2.0, but has been included in version 2.1.
Unicode 2.1 does not change the conformance criteria described in the
version 2.0 documentation and does not reuse an existing code point
for the euro (unique code point), this has allowed Oracle to provide
EURO SIGN support in all supported Unicode versions.
+---------------+--------------------------------------+------------+
| Oracle | Description | Euro |
| Character Set | | Code Point |
+---------------+--------------------------------------+------------+
| AL24UTFFSS | UTF-8 Unicode 1.1 | U+20AC |
+---------------+--------------------------------------+------------+
| UTF8 | UTF-8 Unicode 2.0 | U+20AC |
+---------------+--------------------------------------+------------+
- Checking the database can store the euro
=========================================== Once the correct database
character set has been selected you can check that the server is able
to store and translate the euro correctly using the SQL CONVERT
function. This section provides some examples : 3.1 Testing
conversion from WE8MSWIN1252 to WE8ISO8859P15
--------------------------------------------------------- The
following SQL statement converts the euro from its code point 0x80
(128) in the Microsoft code page WE8MSWIN1252 to 0xA4 (164) in the ISO
code page WE8ISO8859P15. select
dump(convert(chr(128),'WE8ISO8859P15','WE8MSWIN1252')) from dual;
---------------- Typ=1 Len=1: 164 3.2 Testing conversion from
WE8ISO8859P15 to UTF8
------------------------------------------------- The following SQL
statement converts the euro from its code point 0xA4 (164) in the ISO
code page WE8ISO8859P15 to U+20AC in the Unicode code page UTF8.
select dump(convert(chr(164),'UTF8','WE8ISO8859P15'),16) from dual;
-------------------------------- Typ=1 Len=3: e2,82,ac => U+20AC
3.3 Demonstrating an invalid conversion
--------------------------------------- The following example
demonstrates what would happen if a client machine that was configured
for the WE8MSWIN1252 tried to insert and retrieve the euro from a
WE8ISO8859P1 character set database, which does not contain a code
point for the euro. select dump(convert(convert(chr(128),
'WE8ISO8859P1','WE8MSWIN1252'),
'WE8MSWIN1252','WE8ISO8859P1')) from dual; ---------------- Typ=1
Len=1: 191 The reason 0xBF (191) is returned instead of 0x80 (128) is
because when the translation between the display character set
WE8MSWIN1252 and the storage character set WE8ISO8859P1 occurs the
replacement character for the ISO8859-1 code page has to be used. 4.
What support does 8.1 provide for the euro
============================================= Release 8.1.5
introduces full support for storage of the euro (see previous
sections) and also includes format model support. This section
describes the new format support included from 8.1.5 onwards : 4.1
Territory changes --------------------- Territory support allows the
database to manage different cultural conventions which are specific
to a given geographical location, such as local time, date, numeric
and monetary conventions. To accommodate the euro for the eleven
participating member states and the remaining four that may join at a
later date, their territories have been enhanced to support the euro :
+-------------------+---------------+-------------+ | European
Monetary | Participating | New Oracle | | Union Country |
01-Jan-1999 | Territory |
+-------------------+---------------+-------------+ | Austria
| Yes | No |
+-------------------+---------------+-------------+ | Belgium
| Yes | Yes |
+-------------------+---------------+-------------+ | Denmark
| No | No |
+-------------------+---------------+-------------+ | Finland
| Yes | No |
+-------------------+---------------+-------------+ | France
| Yes | No |
+-------------------+---------------+-------------+ | Germany
| Yes | No |
+-------------------+---------------+-------------+ | Greece
| No | No |
+-------------------+---------------+-------------+ | Italy
| Yes | No |
+-------------------+---------------+-------------+ | Ireland
| Yes | No |
+-------------------+---------------+-------------+ | Luxembourg
| Yes | Yes |
+-------------------+---------------+-------------+ | The Netherlands
| Yes | No |
+-------------------+---------------+-------------+ | Portugal
| Yes | No |
+-------------------+---------------+-------------+ | Spain
| Yes | No |
+-------------------+---------------+-------------+ | Sweden
| No | No |
+-------------------+---------------+-------------+ | United Kingdom
| No | No |
+-------------------+---------------+-------------+ The NLS_TERRITORY
parameter setting is either derived from NLS_LANG or is set explicitly
in the initialisation file. It can also be changed on a per session
basis using the SQL 'alter session' command with the following syntax
: ALTER SESSION SET NLS_TERRITORY = 'str'; 4.2 Monetary changes
-------------------- To better accommodate the emergence of new
currencies Oracle has included dual currency support. For countries in
the European Monetary Union this dual currency is defined as the EURO
currency symbol and for countries not in the European Monetary Union
the symbol is their own local currency symbol. To configure the dual
currency symbol manually a new parameter setting have been included
NLS_DUAL_CURRENCY. 4.3 Format model changes ------------------------
A format model is a character literal that describes the format of
DATE and NUMBER data stored in a character string, this format model
is used as an argument to functions such as TO_CHAR and TO_DATE. It
does not affect the internal representation of the data. To support
the euro the numeric format model has been enhanced to include the new
'U' mask, the complete list of monetary masks are : L - Returns the
local currency symbol ----------------------------------------------
The NLS_CURRENCY parameter specifies the character string returned by
the number format mask L, the local currency symbol, overriding that
defined implicitly by NLS_TERRITORY. The following example assumes
that the territory is set to UNITED KINGDOM : SELECT TO_CHAR(299.99,
'L999.99') FROM DUAL; ------- #299.99 You can alter the default value
of NLS_CURRENCY by changing its value in the initialisation file and
then restarting the instance, or you can alter its value during a
session using an ALTER SESSION SET NLS_CURRENCY command. C - Returns
the international (ISO) currency symbol
--------------------------------------------------- The
NLS_ISO_CURRENCY parameter specifies the character string returned by
the number format mask C, the ISO currency symbol, overriding that
defined implicitly by NLS_TERRITORY. To avoid ambiguity between
currencies such as the US dollar and the Australian dollar the ISO
Specification 4217 1987-07-15 defines unique "international" currency
symbols for the currencies of specific territories. The following
example assumes that the territory is set to FRANCE : SELECT
TO_CHAR(299.99, 'C999.99') FROM DUAL; --------- FRF299.99 You can
alter the default value of NLS_ISO_CURRENCY by changing its value in
the initialisation file and then restarting the instance, or you can
alter its value during a session using an ALTER SESSION SET
NLS_ISO_CURRENCY command. U - Returns the dual currency symbol
---------------------------------------------- The NLS_DUAL_CURRENCY
parameter can be used to override the default dual currency symbol
defined in the territory. The dual currency symbol will be displayed
as the euro as long as : - the database character set supports its
code point - the NLS_TERRITORY parameter is set to one of those
listed in section 4.1 - the client machines character set component
of NLS_LANG is set to the client machines character set - the
client operating system can display the euro The following example
assumes that the territory is set to GERMANY : SELECT TO_CHAR(299.99,
'U999.99') FROM DUAL; -------------- E299.99 You can alter the
default value of NLS_DUAL_CURRENCY by changing its value in the
initialisation file and then restarting the instance, and you can
alter its value during a session using an ALTER SESSION SET
NLS_DUAL_CURRENCY command. 5. What support does 8.0 provide for the
euro ============================================= Releases 8.0.5.0.0
and below were released prior to the July 1998 deadline for standards
bodies to submit euro code page specifications; these releases contain
no euro support at all. To fully support the euro in 8.0.x would
require : a) Format model support: date / time, numeric and monetary
formats. A format model is a character literal that describes the
format of DATE and NUMBER data stored in a character string, this
format model is used as an argument to functions such as TO_CHAR and
TO_DATE. Oracle has deemed it not to be feasible to include formatting
support for the euro in 8.0.x. b) Storage support : character
encoding schemes It has been possible to include storage support for
the EURO SIGN in 8.0.x as long as a backport (platform specific fix)
has been made available to the relevant base bug for you operating
system :
+------------+-----------------------------------------------------+ |
Base BUG | Source | Character Set Supported |
+------------+-----------------------------------------------------+ |
BUG:742929 | Microsoft | EE8MSWIN1250 | |
| | CL8MSWIN1251 | | |
| WE8MSWIN1252 | | | |
EL8MSWIN1253 | | | |
TR8MSWIN1254 | | | |
IW8MSWIN1255 | | | |
AR8MSWIN1256 | | | |
BLTMSWIN1257 | | | Undefined |
US8NOOP | | | Unicode |
AL24UTFFSS | | | |
UTF8 |
+------------+-------------+---------------------------------------+
If your database is not already using one of the above character sets
it must be recreated with the appropriate character set. The
appropriate fix must also be applied to 8.0.x clients so that the
correct character translation can be performed between client and
server. The changes made to the national language support code
between releases 8.0.4 and 8.1 focus on enhancements, development
have stated that it would be possible upon customer demand to include
support for some of the new character sets such as the ISO code pages.
Customers should contact Oracle Support Services if this support is
required, WE8ISO8859P15 has been included in the 8.0.6 release. The
following table indicates current database character set support for
the euro on major platforms :
+------------------+----------------------------------------------------+
| Operating System | Details
|
+------------------+----------------------------------------------------+
| Digital UNIX | No backport filed
| | HP-UX 10.x | No backport filed
| | HP-UX 11.x | 8.0.4.3 backport BUG:809255 complete
| | IBM AIX | 8.0.4 backport BUG:884843 complete
| | | 8.0.5 backport BUG:867501 complete
| | Sun Solaris | 8.0.4 backport BUG:1002878 complete
| | | 8.0.5 no backport filed
| | Windows NT Alpha | No backport filed
| | Windows NT Intel | Backport BUG:742930 available in 8.0.4.3.7
| | | Backport BUG:786320 available in 8.0.5.1.3
|
+------------------+----------------------------------------------------+
- What support does 7.3.4 provide for the euro
=============================================== The 7.3.4.0.0 was
released prior to the July 1998 deadline for standards bodies to
submit euro code page specifications; this release contains no euro
support at all. To fully support the euro in 7.3.4 would require : a)
Format model support : date / time, numeric and monetary formats. A
format model is a character literal that describes the format of DATE
and NUMBER data stored in a character string, this format model is
used as an argument to functions such as TO_CHAR and TO_DATE. Oracle
has deemed it not to be feasible to include formatting support for the
euro in 7.3.4.x. b) Storage support : character encoding schemes It
has been possible to include storage support for the EURO SIGN in
7.3.4.x as long as a backport (platform specific fix) has been made
available to the relevant base bug for you operating system :
+------------+-----------------------------------------------------+ |
Base BUG | Source | Character Set Supported |
+------------+-----------------------------------------------------+ |
BUG:742929 | Microsoft | EE8MSWIN1250 | |
| | CL8MSWIN1251 | | |
| WE8MSWIN1252 | | | |
EL8MSWIN1253 | | | |
TR8MSWIN1254 | | | |
IW8MSWIN1255 | | | |
AR8MSWIN1256 | | | |
BLTMSWIN1257 | | | Undefined |
US8NOOP | | | Unicode |
AL24UTFFSS |
+------------+-------------+---------------------------------------+
If your database is not already using one of the above character sets
either create a new database with the appropriate character set. The
appropriate fix must also be applied to 7.3.4 clients so that the
correct character translation can be performed between client and
server. Many changes were made to the national language support code
between releases 7.3 and 8.0, development have stated that it would be
extremely difficult to provide support for the new 8.1 character sets
in 7.3. Oracle Support Services thus recommends customers requiring
euro support who are unable to use any of the above character sets
upgrade to Oracle 8.0, 8.1 or above. The following table indicates
current database character set support for the euro on major platforms
:
+-------------------+----------------------------------------------+ |
Operating System | Details |
+-------------------+----------------------------------------------+ |
Digital UNIX | No backport filed | |
HP-UX 10.x | Backport BUG:775018 complete | |
HP-UX 11.x | Backport BUG:775010 complete | |
IBM AIX | Backport BUG:865594 complete | |
Sun Solaris | Backport BUG:747214 complete | |
Windows NT Alpha | Backport BUG:785943 complete | |
Windows NT Intel | Backport BUG:785947 available in 7.3.4.4.1 | |
Sequent Dynix/PTX | Backport BUG:878610 complete |
+-------------------+----------------------------------------------+
- Which tools from the server CD support the Euro character
============================================================ Since
the international accreditation of the euro by the standards bodies
back in July 1998 many hardware and operating systems vendors have be
including terminal support in their products. Most Unix vendors are
implementing support for the ISO8859-15 code page, please contact you
vendor for information on using and displaying this code page.
Microsoft has enhanced its operating systems to support the euro
including font and keyboard driver support for the new character :
- Windows 95 - through the euro patch - Windows 98 - base
release supports the euro - Windows NT - either through
application of the euro patch or Service Pack 4
- Windows 2000 - base release supports the euro The two main Oracle
server tools affected by the euro are server manager and SQL*Plus,
both of these tools provide terminal support for the euro character in
8.0.6 / 8.1.5 and above. On Unix platforms where the vendor has
provided full euro character support there will be no restrictions on
the Oracle tools, but due to font limitation in the Microsoft Windows
operating system versions of the tools the following restrictions
exist : - Oracle Server Manager will not be able to display the
euro character because the DOS console that it runs in uses a
different code page (OEM Code-page) from the Windows (ANSI
Code-page) and currently does not include the appropriate code
point. This limitation applies to all DOS applications. -
SQL*Plus DOS is also unable to display the euro character for the same
reasons listed above. - SQL*Plus GUI from 8.0.6 should be able to
display the euro character but unfortunately uses the Fixedsys
font which is unable to display the euro. This problem has been
addressed in enhancement request [BUG:914821] and was fixed in
SQL*Plus 8.1.7. - Enterprise Manager SQLPlus Worksheet can be
configured to support the euro character, but SQLPLUS_SYSTEMROOT
and SQLPLUS_NLS_LANG must be set. For further information please
refer to [BUG:950783] The Microsoft key combination required to
display the euro is determined by Windows locale, for most European
customers it will either be ALT GR + 4 or ALT GR + E or from the
numeric key pad ALT "0128".
Oracle Support Services
.
Copyright (c) 1995,2000 Oracle Corporation. All Rights Reserved.
Legal Notices and Terms of Use.
and
Bookmark Fixed font Go to End
Doc ID: Note:152260.1
Subject: Code Pages, Fonts and Registry Settings to Display EURO in
SQL*Plus on Client PC
Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 15-JUL-2001
Last Revision Date: 12-FEB-2002
PURPOSE ------- The purpose of this article is to help support
analysts and customers in making appropriate settings on a client PC
when need to display the Euro symbol in Oracle client tools like
sqlplus.exe and sqplusw.exe. SCOPE & APPLICATION
------------------- The intended audience is anyone who needs to
understand how to check and possibly modify their client PC settings,
for sqlplus (in MS-DOS and Windows) to display specific characters,
using example of Euro. It is important for the readers to
understand that this is not just "Euro" show case, but a methodology
that applies to ANY special characters or glyphs (special, i.e. the
ones that lie beyond the US7ASCII mapping). The information about
Microsoft code pages and fonts is not complete and limited only to
the particular task of displaying Euro symbol on WinNT and Win2000.
Code pages, fonts, and registry settings to display Euro in sqlplus on
Client PC
I. Introduction. MS-DOS mode uses, with a few exceptions, a different
character set (or code page) from Windows. MS-DOS uses the OEM
(original equipment manufacturer) character set. This character set
varies between computers and depends on the code page ROM (read-only
memory) installed by the computer manufacturer. For the most part,
Windows uses fonts organized according to the ANSI character set
(called ANSI-set fonts). Personal computers manufactured for use in
the United States mostly use a character set called code page 437,
while computers manufactured for use in Portugal use code page 860.
To check / change the MS-DOS active code page, you could use command
CHCP. For example: E:\>chcp Active code page: 437 The
registry entry in
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\CodePage\ACP\
shows the active Windows code page. Character positions 32 through
127 are identical in the ANSI and OEM character sets for most code
pages (including code pages 437, 850, 852, 860, 861, and 865). The
remaining characters of the OEM character set (character positions 0
through 31 and 128 through 255) either do not appear in the ANSI
character set, or exist at a different position in the ANSI character
set. Therefore, some characters in the OEM character set cannot be
displayed in Windows using an ANSI-set font. If an application must
display such characters under Windows, an OEM- set font is required.
In the Windows environment, a user can enter any character in the
character set by holding down the ALT key and typing 0xxx, where
"xxx" is the decimal number of the desired character position in the
font. For example, with an ANSI-set font in use, ALT+0123 will
display the 123thrd character in the ANSI character set. Similarly,
with an OEM-set font in use, ALT+0123 will display the 123rd
character in the OEM character set. Note:137127.1 - Character sets,
code pages, fonts and the NLS_LANG value - explains how to choose
Oracle client character set needed to support a specific symbol
(character), with regards to the OS code pages used/set on the
client PC. At the Oracle client side, the encoding scheme used by
Oracle must match the encoding scheme used by the I/O system itself.
This means that the font definition used by the OS and the character
set used by the Oracle client must use the same character codes for
the same characters. For example, character code for the Euro symbol
(character) is 128 (decimal) in the Windows code page 1252. Character
set WE8MSWIN1252 also has Euro represented by code 128, and therefore
is compatible with the code page 1252. In character set
WE8ISO8859P15, on another hand, Euro is represented by character code
164 (decimal). This means that the OS code page 1252 and Oracle
character set WE8ISO8859P15 do not match, and will not produce a
correct representation for the Euro symbol, if used together. When
the encoding schemes of the font (read: code page) and Oracle client
character set (set via NLS_LANG registry entry) do not match, an
incorrect character code will be sent by the client to store in the
server. Please note that this has nothing to do with the database
character set in use. For a list of character sets that support the
Euro symbol, see Table 3-11 in Oracle9i Globalization Support Guide
Release 1 (9.0.1). II. Examples. The following examples present
compatible settings between the code pages, fonts and Oracle client
character set (the NLS_LANG setting) to display the Euro symbol in
two types of Oracle applications on WinNT 4.0 or Win2000. We will
consider MS-DOS applications like sqlplus.exe and svrmgrl, and
Windows-based application like SQL*Plus Windows GUI - the
sqlplusw.exe. The database could be created with UTF8 or other
character set capable of storing the Euro symbol, and also being a
superset to the client character set. The following common steps will
be performed before invoking oracle tools: 1) set the OS code page to
a valid code page (for the given PC) that supports Euro (we use
code page 1252 in this article); 2) set font to Lucida Console or any
other one that supports Euro symbol and also implements the chosen
code page; 3) set NLS_LANG to a character set that has the same
code value for Euro as the code page does; code 128 is supported
by the code page 1252 and the character set WE8MSWIN1252. Thus
setting NLS_LANG to <language>_<territory>.WE8MSWIN1252 will
provide a matching combination for the Oracle client character set
and the code page. a. MS-DOS --------- (C) Copyright
1985-1996 Microsoft Corp. - E:\>chcp Active code page: 437
E:\>chcp 1252 Active code page: 1252 - < Set font to Lucida
Console (in Properties, Font tab) > - E:\>set
NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252 E:\>sqlplus.exe
<user>/<pwd> SQL*Plus: Release 8.1.6.0.0 - Production on Fri Jul
13 13:22:19 2001 (c) Copyright 1999 Oracle Corporation. All
rights reserved. Connected to: Oracle8i Enterprise
Edition Release 8.1.6.0.0 - Production With the Partitioning
option JServer Release 8.1.6.0.0 - Production SQL> create
table t (a char(3)); Table created. SQL>insert into t
values ('€'); 1 row inserted. SQL>commit; SQL>
select * from t; A --- € Similar, in SVRMGR:
E:\>svrmgrl SVRMGR> connect <user>/<pwd> Connected.
SVRMGR> select * from t; A --- € 1 row
selected. b. Windows ---------- To set the font in 8.1.7
sqlplus GUI for Windows add the following registry entries:
SQLPLUS_FONT and SQLPLUS_FONT_SIZE. Further details can be found in
Chapter 3 of the "SQL*Plus Getting Started Release 8.1.7 for Windows".
If you want to use particular characters, such as the Euro symbol, you
should make sure that the font you choose contains those characters.
In sqlplus GUI versions 8.1.5 and 8.1.6, be aware of Bug:914821
(SQLPLUS GUI IS UNABLE TO DISPLAY THE EURO SIGN). SQL*Plus will use
the new font the next time you start the SQL*Plus Windows GUI.
Registry settings for the sqlplus Windows GUI: - set
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Nls\CodePage\ACP
to 1252; - then NLS_LANG needs to be set to
<language>_<territory>.WE8MSWIN1252; - For the SQLPLUS_FONT value,
enter the font name you want to use that supports Euro, such as
Courier New or Lucida Console, - in the Value Data field. - open
session in sqlplusw.exe to display Euro on select or insert/update.
You could use sql DUMP function to check on the value being stored in
the database: SQL> select dump(a, 16) from t; DUMP(a,16)
Typ=96 Len=3: e2,82,ac 1 row selected. The value "e2,82,ac" is hex
representation for the Euro symbol in UTF8. Summary --------- It
couldn't be stressed enough that the above scenario with the Euro
symbol we considered in this article is only a single example of very
generic issue. With any other special character(s) we have to do
the same "homework" before the needed character(s) could be
entered/saved/displayed correctly in a PC client-server environment.
In many cases it happens to be not the Euro, but some Hungarian,
French, Trade Mark, or other character that users have problems with
on their PC. And the solution is in finding a correct combination of
the MS Windows code page that match the chosen Oracle client
character set encoding for that character. The above problems have
nothing to do with the Oracle database, but rather with the client PC
settings, and the relosution could be time consuming when looking for
the available OS code pages and font information. You could find
some information on code pages at the following Microsoft site:
http://www.microsoft.com/typography/unicode/cscp.htm RELATED
DOCUMENTS ----------------- 1. Note:137127.1 - Character sets, code
pages, fonts and the NLS_LANG value 2. Note:132453.1 - How to Change
the Displayed Font in SQL*PLUS (GUI) on WinNT 3. Microsoft Support
Article ID: Q83461 - Extended Characters Different Under Windows
4. Oracle9i Globalization Support Guide Release 1 (9.0.1), Part Number
A90236-02 5. Oracle8i Installation Guide Release 2 (8.1.6) for
Windows NT. Appendix C, National Language Support 6. Bug: 914821
.
Copyright (c) 1995,2000 Oracle Corporation. All Rights Reserved.
Legal Notices and Terms of Use.
Hth
Sybrand Bakker, Senior Oracle DBA
To reply remove -verwijderdit from my e-mail address
Received on Wed Oct 30 2002 - 11:53:52 CST
