3

I'm loading a csv file using sqlldr. the file contains the symbol "€" which is inserted into a VARCHAR2 column. After the load, the database displays '¿' instead of the euro symbol. I have specified the characterset in the control file during the load:

LOAD DATA 
CHARACTERSET WE8MSWIN1252

I'm runing all of this on a Solaris machine, which by the way can't display the '€' symbol, I gives me a '.' instead when I hit the key to get the €.

We are using the data for BI purposes, so we have to keep the varchar2 column, even though changing its type to nvarchar2 inserts the € symbol correctly.

Can you suggest any other solution for the issue?

When I run locale command on the machin, I get : 

LANG=
LC_CTYPE="C"
LC_NUMERIC="C"
LC_TIME="C"
LC_COLLATE="C"
LC_MONETARY="C"
LC_MESSAGES="C"
LC_ALL=

and my NLS DATABASE PARAMETERS Are:

NLS_NCHAR_CHARACTERSET  AL16UTF16
NLS_LANGUAGE    AMERICAN
NLS_TERRITORY   AMERICA
NLS_CURRENCY    $
NLS_ISO_CURRENCY    AMERICA
NLS_NUMERIC_CHARACTERS  .,
NLS_CHARACTERSET    WE8DEC
NLS_CALENDAR    GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE   AMERICAN
NLS_SORT    BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT    DD-MON-RR HH.MI.SSXFF AM

I have tried setting the NLS_LANG variable, but nothing seems to work. Regards.

Slimane Agouram
  • 365
  • 1
  • 5
  • 17

1 Answers1

4

Your database is not configured to support the Euro character in a VARCHAR2 column. Your database's NLS_CHARACTERSET of WE8DEC means that it uses the old DEC MCS character set. That character set long predates the Euro character (it's even older than the ISO 8859-1 character set that also predates the Euro). So you can't validly store a Euro character in a VARCHAR2 column in your database.

You could change the database character set to something that supports the Euro character. There are many such character sets but I would guess that ISO-8859-15, Windows-1252, or UTF-8 would be the easiest migrations. Personally, I'd always prefer Unicode (UTF-8 which is the AL32UTF8 character set in Oracle) but you may have a reason to prefer a single-byte character set. Alternatively, you could declare this column (and any others that need to use the Euro character) to be NVARCHAR2 columns since your national character set supports Unicode. Supporting NVARCHAR2 columns may require changes to your front-end applications. If you can change the database character set, that would be my strong preference over adding NVARCHAR2 columns.

Community
  • 1
  • 1
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • That's what I thought. We settled for the nvarchar2 solution, since the customer wouldn't let us get any close to his database settings. Let's just hope that this has no repercussions on our front-end. Thank you very much for your precise answer. – Slimane Agouram May 29 '15 at 08:57