Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Character confusion

Re: Character confusion

From: Marcel Stör <marcel>
Date: Fri, 5 Sep 2003 11:27:32 +0200
Message-ID: <3f585704$0$236$4d4ef98e@read.news.ch.uu.net>


Rainer Herbst wrote:
> Marcel Stör schrieb:
>> My environment is RedHat and 9i. If I enter German umlauts into a
>> table through a GUI tool like DBAStudio and retrieve the same values
>> with DBAStudio everything seems to be ok. However, if I query the
>> same table from console with SQLPlus the umlauts are no umlauts
>> anymore (ü turns to u, ö turns to o).
>>
>> So I checked v$nls_parameters of the session:
>> NLS_CHARACTERSET is WE8ISO8859P1
>> NLS_NCHAR_CHARACTERSET is AL16UTF8
>> with AMERICAN/AMERICA
>>
>> I just doesn't look right...
>
> Looks ok, WE8ISO8859P1 includes German umlauts, but not the Euro sign.
> WE8ISO8859P15 includes the Euro sign. AMERICAN/AMERICA influences the
> sort order, money signs etc., but should not change the umlauts.

Ok, this means that I can store a Euro sign in the database, but I might run into trouble retrieving the value when using a tool that is not equal to the tool I used to store the value. Am I mistaken? In order to be completely off the hook, I would have to convert the database to WE8ISO8859P15.

>>
>> So I also checked sys.props$. As expected it returns the same values.
>>
>> Linux' $LANG environment parameter is de_CH.UTF-8.
>>
>> I'm stuck with all those locale's and charactersets. I also changed
>> the Linux locale to en_US.UTF-8, which - as expected - has no
>> influence on the umlaut behaviour. Not surprisingly, a CGI web app
>> running on Oracle returns the umlauts like they appear on the
>> console.
>>
>> I would realy appreciate if you could tell me how to sync all those
>> settings.
>>
>> Best regards,
>> Marcel
>>
>>
> German umlauts (and other non-ASCII characters) can be the source of
> great confusion.
>
> At first, check the character set of your database:
> select * from nls_database_parameters;

I did. Sorry for the messed up format.

SQL> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               WE8ISO8859P1
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              9.2.0.1.0
NLS_CSMIG_SCHEMA_VERSION       2

What influence do the NLS parameters have on DB level? Let's say: what's the difference whether I use NLS_LANGUAGE american or german? As fas as I understand, those parameters are only used if a client opens a session with no specific NLS parameters set, right? Except for the charset parameter those DB parameters have no influence on the way the data is stored. To cut a long story short, if a client (e.g. SQL*Plus) connects from a system with NLS_LANG set as a (client) environment variable the NLS parameters of the DB are ignored?

> Sometimes the database is created with US7ASCII character set, and
> your umlauts depends on the mercy of the server and the client tools.
>
> Second, set the NLS_LANG environment variable as described in earlier
> postings, e.g. export NLS_LANG=GERMAN_GERMANY.WE8ISO8859P1;

That helped big time! CGI is fine now. On problem that remains is, that my xterm console somehow doesn't know anything about ISO Latin 1 and displays a blank instead of an umlaut.

Thanks for your explanations.
Marcel Received on Fri Sep 05 2003 - 04:27:32 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US