Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
What is your database character set? What does SELECT * FROM nls_database_parameters WHERE parameter LIKE '%CHARACTERSET' return? –  Justin Cave Jun 28 '12 at 15:56

1 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
share|improve this answer
    
I've tryied to use nvarchar2 without success . As follows => create table prueba (euro nvarchar2(20)); –  marc.riera Jun 28 '12 at 15:16
    
@marc.riera: (1) Make sure your client is able to read the euro sign. (2) see my update, using NCHR or UNISTR may be necessary if your client can't read the euro sign (I haven't been able to read the € sign with sqlplus on windows XP for example) –  Vincent Malgrat Jun 28 '12 at 15:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.