CODEWITHSUNDEEP
oracle-databaseencoding
DevDav
DevDav

Reputation: 346

Can't insert ñ character to an Oracle database

I've got an issue inserting ñ chatacter to an Oracle database.

The INSERT operation completes successfully. However, when I SELECT, I get n instead of ñ.

Also, I noticed executing:

select 'ñ' from dual;

gives me 'n'.

select * from nls_database_parameters where parameter='NLS_CHARACTERSET';

gives me 'EE8MSWIN1250'.

How do I insert ñ? I'd like to avoid modifying db settings.

The only way I got this working was:

  1. read the input .csv file with its windows-1250 encoding
  2. change encoding to unicode
  3. change strings to unicode codes representation (in which ñ is \00d1)
  4. execute INSERT/UPDATE passing the value from #3 to a UNISTR function

For sure there is an easier way to achieve this.

Upvotes: 1

Views: 11056

Answers (3)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

Before you start your SQL*Plus you have to set the codepage (using chcp command) and NLS_LANG environment parameter accordingly:

chcp 1250
set NLS_LANG=.EE8MSWIN1250
sqlplus ...

However as already given in comments, Windows 1250 does not support character ñ. So, these values will not work.

It is not compulsory to set these values equal to your database character set, however codepage and NLS_LANG must match, i.e. following ones should work as well (as they all support ñ)

chcp 1252
set NLS_LANG=.WE8MSWIN1252
sqlplus ...

or to support any character as UTF-8

chcp 65001
set NLS_LANG=.AL32UTF8
sqlplus ...

Again, EE8MSWIN1250 does not support character ñ, unless data type of your column is NVARCHAR2 (resp. NCLOB) it is not possible to store ñ in the database.

Upvotes: 2

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

Simplest way would be, find out the ASCII of the character using ASCII() and insert use CHR() to convert back to string.

SQL:

select ascii('ñ'),chr(50097) from dual;

Output:

ASCII('Ñ') CHR(50097)
---------- ----------
     50097 ñ

The client you are using to connect to database matters. Some clients, donot support these characters.

Upvotes: 2

Rene
Rene

Reputation: 10541

Is that character supported by the database character set? I could not find that character here: https://en.wikipedia.org/wiki/Windows-1250

I had no problems inserting that character in my database which has character set WE8MSWIN1252. https://en.wikipedia.org/wiki/Windows-1252

Upvotes: 1

Related Questions