Re: Oracle 10G - character sets.

From: Laurenz Albe <invite_at_spam.to.invalid>
Date: 09 Jan 2007 10:53:47 GMT
Message-ID: <1168340020.813161@proxy.dienste.wien.at>

markpapadakis <markpapadakis_at_gmail.com> wrote:

>> SELECT value FROM nls_database_parameters
>>   WHERE parameter='NLS_CHARACTERSET';

>
> WE8MSWIN1252
>

>> Second, I'd like to see what code points are stored in the database for
>> greek characters:
>>
>> What is the number returned for an omega?

>
> For a '?' ( I get 'omegas' when I specify 'UTF8' as the charset), the
> character code is
> 927
>
> resulting from:
> SELECT ASCII(SUBSTR(ARTTITLE, 0, 1)) FROM articles WHERE artid = 601

I think that I have started understanding.

927 is not a possible character in WE8MSWIN1252.

But you said that your national character set is AL16UTF16, and in this character set 927 is the greek capital letter omicron (but not omega!).

Is it possible that you have selected an omicron and not an omega? Is the column ARTTITLE of table ARTICLES an NCHAR or NVARCHAR column?

> Same here. Perhaps sqlplus is utilizing some sort of functionality
> which ends up getting the text in the proper encoding.

No, it doesn't. There's no magic in sqlplus :^)

If it is like I suspect, and you have NCHAR columns, then the data in your database is correct. This is a good message and explains why sqlplus retrieves them correctly.

>> But maybe you can take a shortcut:
>> If the greek characters mysteriously are correct in sqlplus, why not use
>> sqlplus to extract the data you want?

>
> Because some of the tables contain blobs and I can't spool blobs to
> files (i.e sqlplus will not display them ).

Oh, ok. Of course.

> Re-importing the
> data (this time having set NLS_LANG properly before doing so ) didn't
> make a difference, either.

And now we also know why: because the columns in question are in fact NCHAR and not CHAR.

> Can I provide you with the source code of the PHP and/or the C++ client
> I am using in order to access the data?

You can try posting relevant sections if possible, but I am neither very proficient in PHP nor have I ever used OCCI.

I have a suspicion, though:
I guess that there is a problem in the setting of NLS_LANG in these clients. I don't know how to do it in PHP, but have you tried outputting the current value of that environment variable in the code right before you establish the connection?

Something like printf("%s\n", getenv("NLS_LANG")); (in C)

Then we would know for certain if the variable is set correctly there.

A second approach would be to turn on client tracing and see what actually passes to and from the database. One should be able to determine NLS problems with that. But try the printf first.

Yours,
Laurenz Albe Received on Tue Jan 09 2007 - 04:53:47 CST