When I insert record with special characters like 'hâta' with INSERT statement from client tool it works fine, But I execute the same INSERT statement from run statement i.e. "SQL>@/Dir_Name/Script_File_Name.sql" the special characters didn't inserted properly and it become some thing like 'h¿ta'.
How can I enter the special characters correctly from run (@) statement?
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?
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.