Re: NLS Part III - i really need help ... !!!

Hi Martin,

thanks A LOT for your help, I am glad you found the time to answer. Oracle is not exactly the easiest chapter (my opinion) when it comes to troubleshooting. I also hate their website since one can't even search for a simple string. Oh well, back to topic.

It was very interesting to learn the 'select dump (col) ....' command, that really helped. The problem is solved as I realized that I will have to set the NLS_LANG environment variable on the client to something that matches the characterset of the database. This way I was able to successfully enter data into a WE8ISO8859P9 charset database.

One thing is still interesting:
- If I insert 'ä-ae' into a WE8ISO8859P9 database, the dump looks like that: 228,45,97,101
- If I do THE SAME on a US7ASCII database, the dump looks exactly the same, 228,45,97,101

Now this makes me doubt my own logic, 7bit goes up to 228? Aeehh... very interesting. I believe that oracle somehow still saves up to 255 chars? What do you think.

Now for the sorting, I was only able to get correct sorting results when using the WE8ISO8859P9 charset (with correct data in it of course).

By the way, it didn't matter what charset I used for the database, an 'ä' was always 228. In UTF8, in WE8ISO8859P9 and in US7ASCII.

Depending on the different character sets used on the client (that is, the unix shell) and the server, sometimes umlauts simply showed up as 'a' instead of 'ä', sometimes as '?'. Interesting. The numeric values where always the same, either a 228 for ä or a 100 for d (when the ä was converted to a d).

Ahem, regarding the 'trace' command, I have to admit that I have no idea where the trace information is saved or where I access it, can you give me a hint. I entered the command ( and I don't even know if it delete the whole db :-) ) but again, I don't know where the information would be.

Thank you again a lot for your help, not even my coworkers knew about the 'select dump ...' !!!!

Have a great day :-)

--

Regards,

Ingmar.

==========================================================
Get Eventwatch NT 2.0 FOR FREE at http://www.netikus.net !
==========================================================

"Martin Haltmayer" <Martin.Haltmayer_at_d2mail.de> wrote in message
news:3B3C3C4B.34DF5244_at_d2mail.de...


> Hi Ingmar,

>

> did you also specify the national character set when creating the

 database? Can


> you please post the result of "alter database backup controlfile to

 trace;"?


>

> My guess on the US7ASCII working: many 7-bit systems map the umlaut codes

 (which


> are clearly 8-bit) to [\]{|}~ (for ÄÖÜäöüß) but display them as 7-bit.

 What does


> a "select dump (COL) from TABX" give? Then you will see as what value

 Oracle


> stores the beast.

>

> This would explain the "working" US7ASCII and the wrong sorting (because

 [\]{|}~


> are true 7-bit characters that are sorted as such). You may try to sort

 umlaut


> upper A, umlaut upper O, umlaut upper U, [, \ and ]. What gives?

>

> You should create your database with any 8-bit character set (like

 WE8ISO8859P1


> or UTF8). Then each client should set its environment characterset to

 whatever


> codes *it* provides. Oracle will notice that the character sets are

 different


> and it will try to convert between them provided the codes match the

 declared


> character set.

>

> Martin

>

>

>

> Ingmar wrote:

> >

> > OK, I hope there is somebody out there that is familiar with NLS. I

 already


> > posted a message today.

> > I have Solaris 7 running Oracle 8.1.7

> >

> > I created three databases with three different character sets:

> >

> > 1.) US7ASCII

> > 2.) WE8ISO8859P9

> > 3.) UTF8

> >

> > The goal: Realize what settings influence the ability to create European

> > chars and have a correct sort order

> >

> > OK, so what's the problem. This is what I tested

> >

> > Chars entered   at                          Characters seen (visible)

> > Database

> >

 ============================================================================


> > ==========

> > öäü             server (local)              öäü

> > US7ASCII (1.)

> > äöü             remote win32 sqlplus client oau

> > US7ASCII (1.)

> > öäü             server (local)              vd|

> > WE8ISO8859P9 (2.)

> > äöü             remote win32 sqlplus client äöü

> > WE8ISO8859P9 (2.)

> > öäü             server (local)              vd|

> > UTF8 (3.)

> > äöü             remote win32 sqlplus client äöü

> > UTF8 (3.)

> >

> > If I enter umlauts directly at the server with sqlplus with an insert,

 then


> > the chars will look

> > correctly only in the first db. For whatever reason, theoretically I

 would


> > think that a 7bit

> > database character set shouldn't even be able to save it as an "ä" - but

 it


> > does.

> > If I connect to the same DB from a remote win32 machine, the chars get

> > converted to the same

> > char without the dots. This is what I would expect the DB to do.

> > The other databases, that should support the umlauts, only support them

 when


> > I enter them from

> > the remote client.

> > Exception: If I enter "öäü" on db 1. or 2. and view them locally at the

> > server, I see "oau". If

> > I enter them at the server, I see "vd|". That shows me that the server

 is


> > converting when working

> > over the listener - right?

> >

> > Sort order:

> > The sort order will only be correct (after setting NLS_LANGUAGE or

 NLS_SORT)


> > if I use one of the

> > latter databases, not the first one.

> >

> > The problem:

> > We have a server based daemon that writes on behalf of the clients, so

 the


> > umlauts work only

> > in the US7ASCII db where, in turn, the sort order won't work. Another

 thing


> > is, besides that

> > problem, that I don't even remotely understand why this works for the

> > US7ASCII db. Is it because

> > my Solaris box is configured for US ASCII? I tried different 'locale'

> > settings with 'export LANG=...'

> > with no effect.

> >

> > Now as you can see I already set up a funky little test environment here

 and


> > I am getting more

> > desperate EVERY DAY. So please help if your familiar with NLS or maybe

 just


> > explain to me silly boy

> > why things are the way they are.

> >

> > Thanks a lot in advance,

> >

> > Ingmar.

> >

> > --

> >

> > Regards,

> >

> > Ingmar.

> >

> > ==========================================================

> > Get Eventwatch NT 2.0 FOR FREE at http://www.netikus.net !

> > ==========================================================