NLS_LANG hell

From: Greg <greg_dorval_at_hotmail.com>
Date: 21 Nov 2002 11:02:20 -0800
Message-ID: <96bbb403.0211211102.7bac13fa@posting.google.com>

This is probably really simple, but I'm tired and it's driving me nuts!

I have an 8.0.5 database on an HP-UX box with the following NLS settings:

SELECT *
FROM V$NLS_PARAMETERS;

PARAMETER                      VALUE


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

NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_CHARACTERSET               WE8ISO8859P1
NLS_SORT                       BINARY
NLS_NCHAR_CHARACTERSET         WE8ISO8859P1

I have two oracle homes installed on my winXP machine. One with 8.1.7 client and the other with Oracle 9i Developer Studio ( with 9.0.2 client ).

The problem is, that running sqlplusw from either of these homes, I do not get proper character translation no matter what I set NLS_LANG to. I've tried all of the following:

AMERICAN_AMERICA.WE8MSWIN1252,
AMERICAN_AMERICA.WE8ISO8859P1,
AMERICAN_AMERICA.WE8DEC

I'm selecting a varchar2 field for which some records contain french characters such as è, é, ç, ê, etc... These characters show up as one of the following characters (depending on NLS_LANG) ?, , ¿. A dump provides the following results:

SQL> SELECT
  2 substr( DESCRIPTION, 15, 1 ) as Description,   3 dump( substr( DESCRIPTION, 15, 1 ) ) as Dump   4 FROM
  5 APPS.FND_FLEX_VALUE_CHILDREN_V
  6 WHERE
  7 DESCRIPTION LIKE '%TRAIT. EAU POTA.%'   8 ;

D
-

DUMP

¿
Typ=1 Len=1: 144

So the character in question is "É". Am I missing something here? Why won't it display properly in sqlplus?

Thanks.
Greg Received on Thu Nov 21 2002 - 13:02:20 CST