user3198603 user3198603 - 15 days ago 9
SQL Question

Update/insert/retrieve accented character in DB?

I am using oracle 12G

when i run

@F:\update.sql
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

SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER LIKE '%CHARACTERSET%'


i get below information

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET WE8MSWIN1252
NLS_NCHAR_CHARACTERSET AL16UTF16


when i run
@.[%NLS_LANG%]
from command prompt i see

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

Answer

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.

set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252

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
set NLS_LANG=.WE8MSWIN1252
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. ¿).