This can be a bit 'tricky' to work with - i created a small blog entry about what we had to do to get utf8 formatted files from windows to linux and then run in to the database with sqlplus. I don;t know exactly your platform etc from your problem description - but i thik the concept at least might help you?
Did you set the NLS_LANG (client side) environment variable correctly?
Are you using Linux? What is the output for the locale command?
Its a bit help full but not fixed
Infect I create scripts from my window pc then copy to my linux server using 'SCP' i tried what is your link but it isn't worked in my case
You should not set NLS_LANG to .UTF8 but rather to the identified character set of your file. How did you exactly create the file?
Yes I am using Linux Server (Linux 2.6.18-164.el5PAE i686)
locale output as under:
I create my scrip from Windows (and if i execute scripts from window client , it work fine but i cant do all script execution over slow network).
So when i transfer the script files to Linux server and execute locally on server it wont show correct characters.
Moreover if i execute the manual statement locally on server sqlplus (sql>insert into tab_name values ('hâtaa'); ) it also not show correct charters.
I create file on Windows client, (it work fine if i execute it on same linux server from window client)
but for heavy scripts execution when i move the same file to Linux, it wont give correct character.
Moreover i have tried to convert my scripts_file to UTF8 on windows before FTP, FTP in binary mode, alter transfer to linux server also convert it to unix format with 'dos2unix' command but all this didn't worked for me.
If you create a file in Notepad, select either the ANSI or the UTF-8 encoding in the Save As dialog box. Copy the file to Linux in binary mode. Set the NLS_LANG variable to ".WE8MSWIN1252", if the file was saved with encoding ANSI or to ".AL32UTF8", if the file was saved with encoding UTF-8. Do not forget to export the variable. Then, start SQL*Plus and run the script. I assume you are on a US or Western European version of Windows.
Solution provided by you worked in my case, thanks for help again
Solution provided by Sergius solved the problem ;-)