Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Character confusion
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