user3198603 user3198603 - 1 year ago 93
SQL Question

Update/insert/retrieve accented character in DB?

I am using oracle 12G

when i run

from sql plus it displays accented character
as junk character when i retrieve either from sqlplus or sql developer

By when run the individual statement from sql plus . Now if retrieve it from sqlplus, it displays correct character but when i retieve it from sqldeveloper it again
displays the junk character.

update.sql content is this

update employee set name ='é' where id= 1;

What i want is when i run @F:\update.sql , it should insert/update/retrieve it in correct format whether it is from sqlplus or any other tool ?

For information :- when i run


i get below information

------------------------------ ----------------------------------------

when i run
from command prompt i see

SP2-0310: unable to open file ".[AMERICAN_AMERICA.WE8MSWIN1252]"

Answer Source

I am not familiar with SQL Developer but I can give solution for SQL*Plus.

Presume you like to work in Windows CP1252

First of all ensure that the file F:\update.sql is saved in CP1252 encoding. Many editors call this encoding ANSI which is the same (let's skip the details about difference between term ANSI and Windows-1252)

Then before you run the script enter

chcp 1252

in order to switch encoding of your cmd.exe to CP1252. By default encoding of cmd.exe is most likely CP850 or CP437 which are different.

Then set NLS_LANG environment variable to character set WE8MSWIN1252, e.g.


After that your script should work fine with SQL*Plus. SQL*Plus inherits the encoding (or "character set", if you prefer this term) from parent cmd.exe. NLS_LANG tells the Oracle driver which character set you are using.

Example Summary:

chcp 1252
sqlplus username/password@db @F:\update.sql

Some notes: In order to set encoding of cmd.exe permanently, see this answer: Unicode characters in Windows command line - how?

NLS_LANG can be set either as Environment Variable or in your Registry at HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_%ORACLE_HOME_NAME%\NLS_LANG (for 32-bit Oracle Client), resp. HKLM\SOFTWARE\ORACLE\KEY_%ORACLE_HOME_NAME%\NLS_LANG (for 64-bit Oracle Client).

For SQL Developer check you options, somewhere it should be possible to define encoding of SQL files.

You are not forced to use Windows-1252. The same works also for other encoding, for example WE8ISO8859P1 (i.e. ISO-8859-1, chcp 28591) or UTF-8. However, in case of UTF-8 your SQL-script may contain characters which are not supported by database character set WE8MSWIN1252. Such characters would be replaced by placeholder (e.g. ¿).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download