€ Euro sign/symbol not showing up on Oracle 10gr2 and aix 6.1

Question

this is Oracle 10gr2 on aix 6100-02

The issue is that I do not manage to see the € sign from Oracle.

This is an easy way to see the issue:

SQL> insert into prueba values ('€');
SQL> commit;
SQL> select * from prueba;
EURO
------------------------------------------------------------
¿
SQL>

This are the tests I have already done.

I see it from the shell:

oracle@krats:/u01#perl -Mcharnames=:full -CS -wle 'print "\N{EURO SIGN}"'
€
oracle@krats:/u01#

But I do not see it from the sql :

SQL> select chr(128) from dual;

C
-


SQL>

This database has the following NLS_SESSION_PARAMETERS;

SQL> SELECT * FROM NLS_SESSION_PARAMETERS;
PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   SPANISH
NLS_TERRITORY                  SPAIN
NLS_CURRENCY                   ¿
NLS_ISO_CURRENCY               SPAIN
NLS_NUMERIC_CHARACTERS         ,.
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD/MM/RR
NLS_DATE_LANGUAGE              SPANISH
NLS_SORT                       SPANISH
NLS_TIME_FORMAT                HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT           DD/MM/RR HH24:MI:SSXFF

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_TIME_TZ_FORMAT             HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT        DD/MM/RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY              ¿
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE

17 filas seleccionadas.

SQL>

I have create a table just to show this up.

oracle@krats:/u01#NLS_LANG=SPANISH_SPAIN.UTF8
oracle@krats:/u01#export NLS_LANG
oracle@krats:/u01#sqlplus "/ as sysdba"
SQL> create table prueba (euro nvarchar2(20));
table created.
SQL> insert into prueba values ('€');
1 fila creada.
SQL> commit;
Confirmación terminada.
SQL> select * from prueba;
EURO
------------------------------------------------------------
¿
SQL>

Finally, I'm not sure if important but this are the oracle user env vars:

SQL> host env;
_=/usr/bin/env
LANG=en_US
LOGIN=oracle
PAGER=/usr/bin/pg
PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:/u01/bin:/usr/bin/X11:/sbin:.:/u01/oracle/product/10.2/db_1/bin/
NLS_LANG=SPANISH_SPAIN.WE8ISO8859P1
ORACLE_BASE=/u01/oracle/product
LC__FASTMSG=true
EDITOR=/usr/bin/vim
KM_CRYPTO_LIB_PATH=/usr/lib/libkmcryptolib.so
LOGNAME=oracle
MAIL=/usr/spool/mail/oracle
LOCPATH=/usr/lib/nls/loc
ORACLE_SID=DIESEL
PS1=oracle@krats:${PWD}#
USER=oracle
AUTHSTATE=files
ORA_NLS10=/u01/oracle/product/10.2/db_1/nls/data
SHELL=/usr/bin/ksh
ODMDIR=/etc/objrepos
R_SHLIB_LD_LIBRARY_PATH=/usr/lib
HOME=/u01
FCEDIT=/usr/bin/vi
TERM=vt100
MAILMSG=[YOU HAVE NEW MAIL]
ORACLE_HOME=/u01/oracle/product/10.2/db_1
PWD=/u01
TZ=Europe/Madrid
KM_SUPPORT_LIB_PATH=/usr/lib/libkmsvcshlib.so
ORACLE_BIN=/u01/oracle/product/10.2/db_1/bin/
A__z=! LOGNAME
NLSPATH=/usr/lib/nls/msg/%L/%N:/usr/lib/nls/msg/%L/%N.cat

SQL>

Has anyone faced a similar situation?

Many thanks.

Answer 1

Your database character set is WE8ISO8859P1, it doesn't have the € sign. Either use NVARCHAR2 or change your database character set (export, reinstall with a character set that supports the euro sign like WE8ISO8859P15 or AL32UTF8, and import).

Here's an example of NVARCHAR2:

SQL> SELECT * FROM nls_database_parameters WHERE parameter LIKE '%SET';

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_CHARACTERSET               WE8ISO8859P1

As you can see I am in the same situation as you.

SQL> CREATE TABLE prueba (id NUMBER, euro NVARCHAR2(20));

Table created.

SQL> INSERT INTO prueba VALUES (1, N'€');

1 row created.

SQL> INSERT INTO prueba VALUES (2, UNISTR('\20AC'));

1 row created.    

SQL> INSERT INTO prueba VALUES (3, NCHR(8364));

1 row created.

SQL> INSERT INTO prueba VALUES (4, CHR(8364 USING NCHAR_CS));

1 row created.

SQL> INSERT INTO prueba VALUES (5, '€');

1 row created.

SQL> SELECT id, euro, ascii(euro) FROM prueba;

        ID EURO                 ASCII(EURO)
---------- -------------------- -----------
         1 €                           8364
         2 €                           8364
         3 €                           8364
         4 €                           8364
         5 ¿                            191