RE: NLS question

From: Michael Schmitt <mschmitt_at_uchicago.edu>
Date: Tue, 15 Mar 2011 10:08:44 -0500
Message-ID: <E37E556CF8A6C44381D2DA9FC354D24102EBFF7E097F_at_EVS03.ad.uchicago.edu>

Thanks All,

I am still having a bit of a problem understanding how the NLS_NCHAR_CHARACTERSET is used though. This is an 11.2.0.1 database, and below you can see the steps that I am running based on some of the feedback. Since this is an nchar column, I was thinking we should get the 'ó' character back. I am guessing there is something that I am not understanding correctly about NLS_NCHAR_CHARACTERSET, or something is not configured correctly

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET               US7ASCII
NLS_NCHAR_CHARACTERSET         AL16UTF16

>env |grep LANG

NLS_LANG=american_america.WE8MSWIN1252

SQL> create table my_table(col1 nchar(10));
SQL> insert into my_table values(N'ó');
SQL> select COL1,dump(col1, 16) col_dump from  my_table;

COL1

---

COL_DUMP

---

o
Typ=96 Len=20: 0,6f,0,20,0,20,0,20,0,20,0,20,0,20,0,20,0,20,0,20

Thanks,
Mike

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of De DBA Sent: Monday, March 14, 2011 7:29 PM
To: jimmybrock_at_gmail.com
Cc: oracle-l_at_freelists.org
Subject: Re: NLS question

Jimmy,

Sorry for the late reply, I only now saw your question. In the past when I had to work with US7ASCII databases (and other situations where the client char set differed from the database character set), I've always found that the only way to retrieve the data as it was entered is to configure the querying client app with the same character set as the inserting client used. This can be quite fun, e.g. when the querying client is deployed on some Unix environment that uses WE8ISO8859P1 by default and the inserting client was a windows based program using WE8MSWIN1252. The solution is to set the NLS_LANG parameter in the environment of the querying client to whatever it defaulted to on the inserting client, e.g.:

   C:> set NLS_LANG=american_america.WE8ISO8859P1    C:> sqlplus /

Hth,

Tony.

Jimmy Brock wrote:
> Jimmy,
>
> NLS_CHARACTERSET=US7ASCII means 256 characters see http://www.asciitable.com/
>
>
> that's not 100% true.
> only the first 127 char are defined.
> That's what the 7 is standing for - ASCII 7-bit America
>
> Yes, you can trick the DB to store some 8-bit values. This will
> bring you into a huge load of fun.
>
> sorry,
> Martin
>
>
>
> Good catch! I don't why I typed 256 characters. Actually, it is 128
> characters - chr(0) to chr(127).
>
> I checked MOS and Oracle recommends using character set WE8MSWIN1252,
> which is a superset of WE8ISO8859P1, P9, P15 and US7ASCII.
>
> --
> Jimmy Brock

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Tue Mar 15 2011 - 10:08:44 CDT