4 Replies Latest reply on Aug 11, 2012 12:59 AM by Sergiusz Wolicki-Oracle

    OS locale settings


      Due to some issues with the UTL_FILE package on our Exadata machine running RAC database, we have to change the NLS_LANG variable setting for our database cluster resource. We'll use the command "srvctl setenv database" to change the value from NLS_LANG=AMERICAN_AMERICA.AL32UTF8 to NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1.

      I've been wondering if we shouldn't reflect the change in the oracle user environment by setting the LANG and other similar variables? Shouldn't the variables LANG and NLS_LANG be somehow in sync?

        • 1. Re: OS locale settings
          Srini Chavali-Oracle
          What is the issue with the UTL_FILE package ? Maybe there is a simpler solution :-)

          • 2. Re: OS locale settings
            Sergiusz Wolicki-Oracle
            There are architectural issues in our CORE library, which cause UTL_FILE to miss end-of-file characters if NLS_LANG is not either equal to the database character set or single-byte. Anyway, this is the only time I know when the NLS_LANG matters for the RDBMS itself. NLS_LANG is normally relevant for clients. Therefore, as far as the database is concerned, LANG and LC_ALL do not need to match NLS_LANG as they are not used by the database.

            On the other hand, NLS_LANG should match LANG or LC_ALL for clients, so that content from the database is processed properly by those clients. NLS_LANG + LANG is not always enough. You may also have to match the configuration of your hardware or software terminal. There are situation though were you may want to temporarily change NLS_LANG to match a file to be uploaded and not the terminal settings.

            -- Sergiusz
            • 3. Re: OS locale settings

              Thanks Sergiusz for the answer. As you suggested, our problem is that the end of line character is ignored in some cases. In our environment the database uses a single byte character set and the NLS_LANG variable is set to UTF8. I managed to create a test case where I can trigger the issue quite easily. If you're interested you may find a full description of my investigation here: [https://www.box.com/s/2dd538bbed4e8c4be3ce]

              As the change is only for the database (not clients), based on your answer, we don't need to modify locale settings for the oracle user.
              • 4. Re: OS locale settings
                Sergiusz Wolicki-Oracle
                Correct. Though, if you use "srvctl setenv database" to set the environment, I wonder if it does not influence some RAC/CRS tools and/or TNS listener. If they run in American, though, then the character set does not matter much for them.

                -- Sergiusz