basically it is being converted to WE characterset.
This is RDBMS 126.96.36.199 64bit running on RHE 5. The linux user account executing the proc that generates the output file has NLS_LANG=AMERICAN_AMERICA.AL32UTF8 in it's environment. We don't understand why the characterset is being converted. he has tried using the NCHAR functions of UTL_FILE and still gets the same results.
Any idea what our issue is?
Below is SELECT * FROM NLS_DATABASE_PARAMETERS from the database
I suspect the problem is with how you look at the file. What you show in your post looks like UTF-8-encoded Western European text when viewed with a iso-8859-1 editor (actually, there should be no "Â" character but I have assumed for now this is an error in the posting). The text that UTL_FILE produced with the listed code should be encoded in AL32UTF8, that is, in UTF-8. Copy this file to a Windows machine and open it in Notepad specifying UTF-8 in the Encoding list of the Open File window. If you see the data correctly then, then the problem is with how you look at the generated file, not with the file itself.