11 Replies Latest reply: Oct 21, 2009 11:46 AM by 493827 RSS

    NLS_LANG Question

    493827
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              Thank you! I'll try to create a new test db with exactly same scenario.


                              Thanks again for all the replies.