This content has been marked as final. Show 4 replies
What is the issue with the UTL_FILE package ? Maybe there is a simpler solution :-)
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.
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.
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.