RE: Oracle Character sets

Justin,

Pretty interesting. Let me just get it straight. Are
you saying that ...

IF
(
1 - Client's NLS_LANG char set portion properly
matches OS code page AND
2 - This char set is capable of rendering the data
stored in the DB AND
3 - Retrieved data doesn't look ok
)

THEN
(
There is a corruption in the DB and it is time to run
csscan?
)


In your example DB is not capable of storing extended
characters properly. What if it can?

Here's a scenario:
DB is on Linux, char set is MSWin-1252

Should I set my Linux client NLS_LANG to MSWin-1252 or
"more suitable" to the OS, say ISO-8859P1 (or P15 for
Euro)?



Here's a test-case:
-- 0) DB settings:

SQL> select dbms_utility.port_string from dual;

PORT_STRING
------------------------------------------------
Linuxi386/Linux-2.0.34-8.1.0

SQL> sho release
release 1001000200

SQL> select value$ from props$ where
name='NLS_CHARACTERSET';

VALUE$
-----------------------------------
WE8MSWIN1252


-- 1) MS Win client settings:

SQL> @%NLS_LANG%
SP2-0310: unable to open file
"AMERICAN_AMERICA.WE8MSWIN1252"

SQL> @%SQLPLUS_FONT%
SP2-0310: unable to open file "courier new.sql"


-- 2) Create table, populate data from MS Win
CREATE TABLE char_set(
       id            INT,
       c1_char       CHAR(10),
       c2_var        VARCHAR2(10),
       CONSTRAINT char_set_pk PRIMARY KEY(id)
)
/

-- Accented char:
INSERT INTO char_set VALUES( 1, 'é', 'é');
INSERT INTO char_set VALUES(201, '?', '?');


-- Special char:
INSERT INTO char_set VALUES(211, '?', '?');
INSERT INTO char_set VALUES(221, '¼', '¼');
commit;

-- 3) Query back from the same MS Win:

SQL> select * from char_set;

        ID C1_CHAR    C2_VAR
---------- ---------- ----------
         1 é          é
       201 ?          ?
       211 ?          ?
       221 ¼          ¼


-- 4) Query back from Linix wo/ char set conversion /
"as is" / to match client's NLS_LANG w/ DB char set:

SQL> @$NLS_LANG
SP2-0310: unable to open file
"AMERICAN_AMERICA.WE8MSWIN1252.sql"

SQL> select * from char_set;

         ID C1_CHAR    C2_VAR
 ---------- ---------- ----------
          1 é          é
        201 ?          ?
        211 ?          ?
        221 ¼          ¼


-- 5) Set client's NLS_LANG to a "more native" *nix
char set:

SQL> @$NLS_LANG
SP2-0310: unable to open file
"AMERICAN_AMERICA.WE8ISO8859P15.sql"

SQL> select * from char_set;

         ID C1_CHAR    C2_VAR
 ---------- ---------- ----------
          1 é          é
        201 ¤          ¤
        211 ¿          ¿
        221 ¿          ¿


Thanks,
Boris Dali.

 --- Justin Cave <justin@xxxxxxxxxxx> wrote: >  
> Note that setting the client NLS_LANG to match the
> database character set is
> a rather risky thing to do, since it tells Oracle
> not to do any character
> set conversion between the client and the server. 
> This can permit you to
> get corrupt data into your database, which is a
> royal pain to fix.
> 
> Imagine that you have a database with a character
> set of US7ASCII.
> Logically, there are only 128 distinct values that
> can be encoded in this
> character set and those are all English characters. 
> 
> 
> Now, imagine that I have a Chinese client machine
> that has some Big-5
> encoded data to insert.  Obviously, though, you
> can't insert Chinese data
> into a US7ASCII database.  If you set the NLS_LANG
> on the Chinese system to
> US7ASCII, though, Oracle will happily allow you to
> insert the Big-5 data and
> will keep it fully intact.  
> 
> If another client machine with an NLS_LANG of
> US7ASCII comes along and
> queries the data, they will get valid Big-5 data
> out.  If that is what the
> application expects, everything will appear to work
> and you can have a
> system that stores Chinese data in an ASCII database
> seemingly without a
> problem.  As long as everyone lies to Oracle in
> exactly the same way, life
> is good.
> 
> Some months or years after these sorts of systems go
> into production,
> though, problems are bound to occur.  For example,
> if you want to replicate
> the data from the ASCII database to a UTF-8
> database, you're out of luck,
> since the data in the ASCII database is corrupt.  If
> you have different
> clients that lie to the database differently-- i.e.
> one set of clients is
> passing in Big-5 data, another is passing in UTF-8
> data, but everyone has
> their NLS_LANG set to US7ASCII-- you will generate
> massive problems as data
> will appear OK only if your application was the last
> to write it.  This sort
> of multiple character set corruption is much, much
> harder to fix than the
> simple corruption problem, which is itself a royal
> pain.
> 
> The client NLS settings should properly identify the
> character set of the
> data that particular client machine is sending and
> expecting in return.
> Generally, it should match the code page of the
> local operating system (i.e.
> Windows-1252 on an English Windows machine).
> 
> Justin Cave
> Distributed Database Consulting, Inc.
> http://www.ddbcinc.com/askDDBC
> 
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 
> Sent: Monday, June 07, 2004 1:45 PM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: RE: Oracle Character sets
> 
> Ron,
> 
> I'm not aware of any papers, however, I always set
> NLS_LANG in my
> environment to match the character set of my
> database.
> 
> So, I have a database that has WE8ISO8859P1
> character set, and I set
> NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1.  I do that
> In the environment before
> running exp or imp.
> 
> -Mark
> 
> Mark J. Bobak
> Oracle DBA
> ProQuest Company
> Ann Arbor, MI
> "Post Hoc Ergo Propter Hoc"
> 
> 
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
> Smith, Ron L.
> Sent: Monday, June 07, 2004 3:24 PM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Oracle Character sets
> 
> 
> I am trying to get rid of export and import messages
> concerning NLS
> character and language sets.  
> 
> Can anyone point me to a paper that explains which
> one(s) to use?
> 
> Thanks!
> Ron
>
----------------------------------------------------------------
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
>
----------------------------------------------------------------
> To unsubscribe send email to: 
> oracle-l-request@xxxxxxxxxxxxx put
> 'unsubscribe' in the subject line.
> --
> Archives are at
> http://www.freelists.org/archives/oracle-l/
> FAQ is at
> http://www.freelists.org/help/fom-serve/cache/1.html
>
-----------------------------------------------------------------
>
----------------------------------------------------------------
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
>
----------------------------------------------------------------
> To unsubscribe send email to: 
> oracle-l-request@xxxxxxxxxxxxx put
> 'unsubscribe' in the subject line.
> --
> Archives are at
> http://www.freelists.org/archives/oracle-l/
> FAQ is at
> http://www.freelists.org/help/fom-serve/cache/1.html
>
-----------------------------------------------------------------
> 
> 
> 
> 
>
----------------------------------------------------------------
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
>
----------------------------------------------------------------
> To unsubscribe send email to: 
> oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at
> http://www.freelists.org/archives/oracle-l/
> FAQ is at
> http://www.freelists.org/help/fom-serve/cache/1.html
>
----------------------------------------------------------------- 

______________________________________________________________________ 
Post your free ad now! http://personals.yahoo.ca
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: