8 Replies Latest reply: Apr 16, 2012 6:08 AM by sybrand_b RSS

    NLS_LANG database server and client

    Neo-b
      Hello All,

      I installed Oracle 11gR2 on AIX7.1 my database character set is AR8MSWIN1256.

      On the oracle AIX user I set NLS_LANG to: AMERICAN_AMERICA.AR8MSWIN1256.

      from a windows client and using SQL DEVELOPER i was able to insert Arabic characters and I was able to select it.

      My AIX sqlplus is not able to read these Arabic characters, Why ? even I set NLS_LANG in the profile of the oracle user.

      Note:
      i exported the records and import it from the oracle user on AIX. It was exported and imported sucessfully with Arabic characters.

      Regards,
        • 1. Re: NLS_LANG database server and client
          900442
          Hi NB,

          Whenever you set the client NLS_LANG parameter same as the NLS_LANG parameter with the database server, what happens is that all data coming from the client, whether it would be Western, Japanese or Korean, would be regarded as ARABIC when being sent to the database server. Now, having NLS_LANG set to ARABIC to the database server, he would say that "any other language sent to me that is NOT arabic will be translated to arabic". If you set the client NLS_LANG as AR8MSWIN1256 (with AMERICAN_AMERICA.AR8MSWIN1256 as the original input), then it would tell the database server that "I'm sending data as AR8MSWIN1256 even if it is written in AMERICAN_AMERICA.AR8MSWIN1256). In effect, since the database server says that he should receive AR8MSWIN1256, then he would not bother translating your data (because the client said that it is in AR8MSWIN1256 even if it is input in AMERICAN_AMERICA.AR8MSWIN1256).

          The solution to this i believe is to set your client NLS_LANG to AMERICAN_AMERICA.AR8MSWIN1256. This way, data sent from your client would be AMERICAN_AMERICA.AR8MSWIN1256 and when the database receives it, he would say that "oh, the data is not AR8MSWIN1256. let me translate that...".

          Now, regarding your note:
          i exported the records and import it from the oracle user on AIX. It was exported and imported sucessfully with Arabic characters.

          -when you do an export, it will export the file with the original character set it uses. now when you do an import to the database server, the database would know that it is AMERICAN_AMERICA.AR8MSWIN1256. In turn, he would convert it.

          Kindly test it once...
          HTH...
          • 2. Re: NLS_LANG database server and client
            sybrand_b
            can you use arabic characters in a shell prompt on AIX? The likely cause is an incorrect locale setting on the AIX server.
            Also I seem to recall Unix uses ISO code pages and they are not identical with Windows code pages.
            As far as I know Sql developer uses utf8, the problem may be resolved when you set your AIX locale to utf8.

            -------
            Sybrand Bakker
            Senior Oracle DBA
            • 3. Re: NLS_LANG database server and client
              sybrand_b
              when you do an export, it will export the file with the original character set it uses.
              Please substantiate this with a link to the documentation!! The OP didn't even specify whether he used exp or expdp, which act differently with respect to characterset issues!!!


              ------------
              Sybrand Bakker
              Senior Oracle DBA
              • 4. Re: NLS_LANG database server and client
                Neo-b
                No i cannot use Arabic character on AIX shell script.
                You are right UNIX use ISO codes.

                How should I set my NLS_LANG so i can read the stored arabic characters in my database from AIX sqlplus?

                Regards,
                • 5. Re: NLS_LANG database server and client
                  900442
                  Hi,
                  Based the reply on the following extract from the doc...

                  "The Export utility always exports user data, including Unicode data, in the character sets of the Export server. (Character sets are specified at database creation.) If the character sets of the source database are different than the character sets of the import database, then a single conversion is performed to automatically convert the data to the character sets of the Import server."

                  Reference: http://docs.oracle.com/cd/E11882_01/server.112/e22490/original_export.htm#BABGGIBB

                  Sorry for the quick response, i did not take into account which utility is used by the OP...

                  Thanks for the clarification....
                  • 6. Re: NLS_LANG database server and client
                    sybrand_b
                    First you should make sure your terminal session can display arabic characters correctly. You will need to change your locale for that.
                    Secondly the NLS_LANG on AIX needs to match the locale.
                    With a bit of Googling I found the Unix characterset for arabic is ISO8859-6. Likely the Oracle characterset description will be AR8ISO8859P6, but I leave it to you to verify this in the *Globalization Manual'. Also there is v$nls_valid_values, which will show you what your software will 'swallow.

                    --------
                    Sybrand Bakker
                    Senior Oracle DBA
                    '
                    • 7. Re: NLS_LANG database server and client
                      sybrand_b
                      I am quite sure that using traditional exp, not using direct=y, and using a NLS_LANG different from the database characterset, the export will be converted.
                      As far as I know 'export server' means the session on the server performing the export.
                      So I don't agree with your assertion.

                      Expdp, however, ignores NLS_LANG.

                      ---------
                      Sybrand Bakker
                      Senior Oracle DBA
                      • 8. Re: NLS_LANG database server and client
                        900442
                        Yeah, thanks for the clarification.

                        Regards...