CODEWITHSUNDEEP
sqloracle-databasesqlplus
user3198603
user3198603

Reputation: 5836

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 from either sqlplus or sql developer

By when run the individual statement from sql plus. Now if I retrieve it from sqlplus, it displays the correct character, but when I retrieve 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]"

Upvotes: 5

Views: 4195

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59543

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. ¿).

Upvotes: 3

Related Questions