August 5, 2009

LANG versus NLS_LANG

When you're not seeing special characteres like de EURO character (€) and your database characterset is one of the right ones (WE8ISO8859P15, UTF-8, or WE8MSWIN1252 or anyother that contains the chars you want to store), then your might have a problem at the level of machine language.
In UNIX/Linux systems you can find out these settings by typing "locale":
[oracle@myserver~]$ locale

LANG=en_US.iso88591
LC_CTYPE="en_US.iso88591"
LC_NUMERIC="en_US.iso88591"
LC_TIME="en_US.iso88591"
LC_COLLATE="en_US.iso88591"
LC_MONETARY="en_US.iso88591"
LC_MESSAGES="en_US.iso88591"
LC_PAPER="en_US.iso88591"
LC_NAME="en_US.iso88591"
LC_ADDRESS="en_US.iso88591"
LC_TELEPHONE="en_US.iso88591"
LC_MEASUREMENT="en_US.iso88591"
LC_IDENTIFICATION="en_US.iso88591"
LC_ALL=

The only meaningfull environment variable here is LANG. And the million "euro" answer is: LANG and NLS_LANG must match!

To find out what character maps are installed in your unix client box, you can type "locale -a" but you'll have to restrict the query to the ones you're insterested in. For this example shown of LANG you won't be able to see or pass on the EURO char to the database, even if you level it with NLS_LANG. Simply because ISO8859P1 does not contain this character.
One who does have, is ISO8859P15, so we'll try to direct the LANG variable to a value accordingly.

1st: Find the right LANG with "locale" command
2nd: Set it up
3rd: Align LANG with NLS_LANG

Then your client would be able to reproduce and send over the EURO character from and to the database.

Now the tech stuff:

1st: ------------------------------------------------------
[oracle@myserver~]$ locale -a | grep 8859 | grep pt_PT

pt_PT.iso88591
pt_PT.iso885915@euro << bingo! this is the one we want

2nd: ------------------------------------------------------
[oracle@myserver~]$ export LANG=pt_PT.iso885915@euro

3rd: ------------------------------------------------------
[oracle@myserver~]$ export NLS_LANG=PORTUGUESE_PORTUGAL.WE8ISO8859P15

Now you're done and ready to spread the 164 char and not the 191:
SQL> select * from myschema.teste;

N X
---------- -
1 ¿

SQL> select dump(x) from myschema.teste;

DUMP(X)
--------------------------------------------------------------------------------
Typ=1 Len=2: 0,191

SQL> insert into myschema.teste values (2,'€');

1 row created.

SQL> select * from myschema.teste;

N X
---------- -
1 ¿
2 €

SQL> select dump(x) from myschema.teste;

DUMP(X)
--------------------------------------------------------------------------------
Typ=1 Len=2: 0,191
Typ=1 Len=2: 0,164

No comments:

Post a Comment