Update/insert/retrieve accented character in DB?

Question

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]"

score 3 · Accepted Answer · edited May 23 '17 at 12:13

3

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

edited May 23 '17 at 12:13

Community

1
1

answered Nov 22 '16 at 13:00

Wernfried Domscheit

54,457
9
76
110

It does not work. What i did is 1) Open CMD 2) run `chcp 1252` which outputs `Active code page: 1252` 3)Then i run `@F:\update.sql` 4) I retrieve the updated data, it again gives me junk code – user3198603 Nov 22 '16 at 13:38
I tried `set NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252` But it does not make any difference ? – user3198603 Nov 22 '16 at 13:45
Yes i am 100 % sure it is saved as ANSI.Will you be able to try it at your end ? – user3198603 Nov 22 '16 at 13:57
What do you get by `select dump(name, 1016) from employee where id= 1;` – Wernfried Domscheit Nov 22 '16 at 14:05
it gives `inconsistent datatypes: expected - got CLO`. Actually name column is clob – user3198603 Nov 22 '16 at 14:10
Then do `select dump(to_char(name), 1016) from employee where id= 1;` – Wernfried Domscheit Nov 22 '16 at 14:13
it returns `Typ=1 Len=1 CharacterSet=WE8MSWIN1252: e9` – user3198603 Nov 22 '16 at 14:16
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/128727/discussion-between-wernfried-domscheit-and-user3198603). – Wernfried Domscheit Nov 22 '16 at 14:22

Update/insert/retrieve accented character in DB?

1 Answers1