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_at_askddbc.com> 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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]
> Sent: Monday, June 07, 2004 1:45 PM
> To: oracle-l_at_freelists.org
> 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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of
> Smith, Ron L.
> Sent: Monday, June 07, 2004 3:24 PM
> To: oracle-l_at_freelists.org
> 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_at_freelists.org 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_at_freelists.org 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_at_freelists.org
> 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_at_freelists.org
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
-----------------------------------------------------------------
Received on Tue Jun 08 2004 - 18:27:24 CDT
