This discussion is archived
11 Replies Latest reply: Oct 21, 2009 9:46 AM by 493827 RSS

NLS_LANG Question

493827 Newbie
Currently Being Moderated
Ok, I'm trying to understand the article here http://www.oracle.com/technology/tech/globalization/htdocs/nls_lang%20faq.htm#_Toc110410543

And the one noted on Tom's book. What I'm trying to replicate is the part where he showed that the character from the Western European turned into letter 'a'. Not sure if I'm doing this correctly, and another problem is that I have a database with the US7ASCII setting ( if that matters ). Here's what I've tried.

1st terminal:
[oracle@mortonlx ~]$ echo $NLS_LANG

[oracle@mortonlx ~]$ plus /nolog

SQL*Plus: Release 9.2.0.4.0 - Production on Tue Oct 20 23:40:57 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> select name
  2  from nls_database_parameters
  3  
SQL> select *
  2  from nls_database_parameters
  3  where parameter = 'NLS_CHARACTERSET';

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET               US7ASCII

SQL> drop table t;

Table dropped.

SQL> create table t ( data varchar2(1));

Table created.

SQL> insert into t values(chr(224));

1 row created.

SQL> insert into t values(chr(225));

1 row created.

SQL> insert into t values(chr(226));

1 row created.

SQL> col data format a10
SQL> col dump format a20
SQL> select data, dump(data) dump from t;

DATA       DUMP
---------- --------------------
�          Typ=1 Len=1: 224         ( On my screen the data on the "DATA" column displays as a "?" )
�          Typ=1 Len=1: 225
�          Typ=1 Len=1: 226

SQL> 
Now I opened a second terminal, now setting my NLS_LANG, and hoping I would see a different character. ( That's how I understood it. )

2nd Terminal:
[oracle@mortonlx ~]$ export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
[oracle@mortonlx ~]$ echo $NLS_LANG
AMERICAN_AMERICA.WE8ISO8859P1
[oracle@mortonlx ~]$ plus /nolog

SQL*Plus: Release 9.2.0.4.0 - Production on Tue Oct 20 23:47:08 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> conn morton
Enter password: ******
Connected.
SQL> select *
  2  from nls_database_parameters
  3  where parameter = 'NLS_CHARACTERSET';

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET               US7ASCII

SQL> col data format a10
SQL> col dump format a20
SQL> select data, dump(data) dump from t;

DATA       DUMP
---------- --------------------
�          Typ=1 Len=1: 224         ( On my screen, shows same "?" character )
�          Typ=1 Len=1: 225
�          Typ=1 Len=1: 226

SQL> 
What are the things I did wrong, or what wrong understanding did I make? Is this the correct way of recreating it? Or what are the real concepts I missed?

Thanks.
  • 1. Re: NLS_LANG Question
    657396 Newbie
    Currently Being Moderated
    hi,

    you have changed the server session nls_lang parameter and it will show you different character as per your characterset. Don't know how it doesn't show you any change but i did the same practical and change is there (not big but you can notice it).
    SQL> connect test/test
    Connected.
    SQL> create table t1(data varchar2(1));
    
    Table created.
    
    SQL> insert into t1 values(chr(224));
    
    1 row created.
    
    SQL> insert into t1 values('a');
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> select * from t1;
    
    D
    -
    a
    a
    
    SQL> col data format a10
    SQL> col dump format a20
    SQL> select data,dump(data) dump from t1;
    
    DATA       DUMP
    ---------- --------------------
    a          Typ=1 Len=1: 224
    a          Typ=1 Len=1: 97
    
    SQL> exit
    in above screen as you can see i've created a table before setting nls_lang parameter, two rows is inserted and then retrieved, both character appeared as a however chracter code is different .
    [oracle@localhost ~]$ NLS_LANG=FRENCH_FRANCE.WE8MSWIN 1252
    bash: 1252: command not found
    [oracle@localhost ~]$ NLS_LANG=FRENCH_FRANCE.WE8MSWIN1252
    [oracle@localhost ~]$ export NLS_LANG
    [oracle@localhost ~]$ echo $NLS_LANG
    FRENCH_FRANCE.WE8MSWIN1252
    [oracle@localhost ~]$ sqlplus /nolog
    
    SQL*Plus: Release 10.2.0.1.0 - Production on Mer. Oct. 21 11:01:45 2009
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    SQL> connect test/test
    Connected.
    SQL> select * from t1;
    
    D
    -
    
    a
    
    SQL> select count(*) from t1;
    
      COUNT(*)
    ----------
             2
    set the nls_lang parameter and retrieve the row again but the first row is missing, the same row is exist still but because of nls_lang change on server session (our terminal) conversion take place and that character represent first row value as blank.

    Read the documentation [http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/ch3globenv.htm#sthref195] and you will get detail information with example.

    HTH
  • 2. Re: NLS_LANG Question
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    The US7ASCII characterset is not capable of storing CHR (224) thru CHR (226). When you are inserting into the test table, garbage is being stored, which is what you are seeing in your output.

    HTH
    Srini
  • 3. Re: NLS_LANG Question
    728554 Explorer
    Currently Being Moderated
    Hi,

    What terminal software do you use ? It could be a client (terminal, not oracle client) problem with displaying 8 bit characters. Your terminal software should be able to interpret and display 8 bit characters. I had very similar issue with patent symbol, characterset MSWIN1252, putty and sqlplus. I was unable to see it properly until I set correct translation table in putty via Window->translation->Win1252.

    --romas                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
  • 4. Re: NLS_LANG Question
    493827 Newbie
    Currently Being Moderated
    Ok, so I'm just using the wrong characters to test it? I tried to insert a normal character 'a' on the first terminal, then selected that on the 2nd terminal ( modifed NLS_LANG ) and I saw the same character 'a', it didn't change. Does it mean that its because the NLS_LANG I've use on the 2nd terminal can properly identify character 'a'? Or is it still not working?
  • 5. Re: NLS_LANG Question
    493827 Newbie
    Currently Being Moderated
    Thanks, I'm just using the "GNOME Terminal". I've looked on Menu->Character encoding, it says Current Local UTF-8.

    Edited by: mortonmorton on Oct 21, 2009 8:28 AM
  • 6. Re: NLS_LANG Question
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    I am not sure what you are trying to do here. You are storing "a" (or whatever character) and want to see it change to a different character by simply changing NLS_LANG ?

    You might want to read thru MOS Doc 158577.1 (NLS_LANG Explained (How does Client-Server Character Conversion Work?))

    Srini
  • 7. Re: NLS_LANG Question
    493827 Newbie
    Currently Being Moderated
    Exactly! Because that's what I saw on the book. I'm just trying to re-create the scenario and not getting success on it. Thanks for the link I'll check it!
  • 8. Re: NLS_LANG Question
    728554 Explorer
    Currently Being Moderated
    I missed one important point. If you want to try out NLS stuff with 8 bit characters your database characterset should be 8 bit aware (we8mswin1252, we8iso8859something, any other). With US7ASCII at the database level no matter what 8 bit aware characterset you set at the client level - server/client translation result will show you upside down question marks for 8 bit characters because US7ASCII is 7 bit characterset; hence 7 in the name ;-)

    That metalink note referred in the previous post is a best bet to understand the rules.

    --romas                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
  • 9. Re: NLS_LANG Question
    493827 Newbie
    Currently Being Moderated
    Thank you! So you're saying what I'm doing really won't work since the database I have already used the "lowest" character set, and the one I'm using on the 2nd session is kind of "superior" and can handle any character my database has? So I could really kind see the conversion if the client character set is kind of "inferior" or has less character set than what the database have? I'll try to finish first the doc too. Thanks again.
  • 10. Re: NLS_LANG Question
    728554 Explorer
    Currently Being Moderated
    You got it ! There is no upconverting in Oracle database yet ;-) Migrate your database to 8 bit character set and your test will be valid.

    --romas                                                                                                                                                                                                                                                                                                           
  • 11. Re: NLS_LANG Question
    493827 Newbie
    Currently Being Moderated
    Thank you! I'll try to create a new test db with exactly same scenario.


    Thanks again for all the replies.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points