Re: Character Set Problems

On Jun 14, 1:48 pm, Paul <paulwragg2..._at_hotmail.com> wrote:
> Hi,
>
> I am having real problems with Character Sets in a 9.2.0.1.0 database.
> Here is the situation, with what I think is happening. Any help with
> this would be greatly appreciated.
>
> 1) The Database Server is using the WE8ISO8859P1 character set. The
> client setting for the server is the same.
> 2) We have an application that generates XML data, based on various
> things, and stores this data into a LONG column.
> 3) One particular part of this is that step 2 reads the '°C'
> characters from a table in the DB.
> 4) When the XML data is generated within our application, it is
> encoded using the UTF8 format. The '°C' characters become '°C' which
> I believe is correct as the UTF representation of the '°' character is
> '°'. I have used a hex editor to check the representation of these
> characters, and all seems to be well. I think everything is stored
> correctly. I also believe that as the database and client have the
> same character set setting, no conversion is done, and so the data is
> stored in UTF8 format.
> 5) Another application comes along and reads the data in. The data is
> being rejected, as it does not know what the '°' is.
> 6) I suspect that this 2nd application needs to know that the data is
> stored in UTF8 format.
> 7) I have tried outputting the data in SQL Plus using various NLS_LANG
> settings, but I always get the 'Â' character displayed.
>
> If I could get the display working correctly in SQL Plus, then I think
> I could recommend something to get the 2nd application to import the
> data successfully.
>
> I am at a bit of a loss as how to proceed. I am convinced that the
> data is stored correctly within the DB, and the problem is with
> reading it back, but I do not know how to solve this. Any input into
> this that anybody may be able to offer would be good.
>
> Thanks in advance,
>
> Paul

I don't think it is very likely the data is stored correctly. WE8ISO8859P1 is a *single* byte characterset. UTF8 isn't.
You need to be sure on sql*plus level the data actually gets stored correctly.
You may also want to store the data in an NCLOB, instead of a LONG which was desupported in 8.0.
Also you need to upgrade to 9.2.0.8, as this will be the only version Oracle has in *extended* support after July 31.

--
Sybrand Bakker
Senior Oracle DBA