NLS_CHARACTERSET shows a database character set (AL32UTF8 in your DB) which is used for CHAR and VARCHAR2 columns, PL/SQL variables and such identifiers, while NLS_NCHAR_CHARACTERSET shows a national language character set (AL16UTF16) (used for NCHAR and NVARCHAR2 columns) in your DB. Those represent Unicode 3.1 UTF-8, variable length (that is, 16 - fixed length) Universal multibyte character sets.
Can't tell for sure, but - don't your character sets already support spanish characters? Did you try to store those characters into the table columns and/or variables?
Check this page to view character set names etc.; perhaps there you'll find what you need.
I think it doesn't come from data not being correctly written in the DB, but from data not correctly read by the client. What is your NLS_LANG environment variable (OS) like on the client machine ? If the character set on the client is not the one on the server, Oracle will translate data from the db charset to the client charset, and I think that here you must have something like US7ASCII as client charset (the default if you have no NLS_LANG environment parameter). If you use PUTTY, change the charset to UTF-8 in "Window-> Translation", then have your NLS_LANG be something like this :
You're not obliged to use UTF-8 on the client, but be sure that the charset you use is compatible with the letters you want to display, and that your client terminal's charset and NLS_LANG's charset are the same.
No, with UTF-8 you can encode ANY character (including all european languages, japanese, korean... everything). So if you plan to have a multilanguage database, AL32UTF8 is excellent.
... I think I see : UTF-8 is multi-bytes and chr(n) in UTF-8 will certainly NOT correspond to the character with ASCII code n. For all characters whose ASCII code is under 127, their UTF-8 representation will be one-byte with the same code, but for all other characters, they will be coded on 2 to 4 bytes with codes completely different from their ASCII codes. In fact, I think your test is flawed.
Here is part of the CHR() function doc :
For single-byte character sets, if n > 256, then Oracle returns the binary equivalent of n mod 256.
For multibyte character sets, n must resolve to one entire codepoint. Invalid codepoints are not validated, and the result of specifying invalid codepoints is indeterminate.
Conclusion : don't test with chr(), and be sure UTF-8 is very good for supporting multi-language databases. Keep it, and if you want to be sure, just insert spanish text from the web, configure NLS_LANG correctly, and read these data from your client.
Furthermore, your charset conversion would have been complicated because UTF-8 is a super-set of all other character sets.
Thanks for the reply. I too think UTF8 characterset is the best to suit the requirement (to include spanish language). So I tried to fix it with the same characterset as AL32UTF8. But I couln't give any suggestions for how to insert and fetch the spanish characters from the table, to the developers. They r facing the same problem.
Also, I have set the env variable NLS_LANG to AMERICAN_AMERICA.UTF8
Sorry, I may have misguided you for the NLS_LANG, because UTF8 and AL32UTF8 are different in Oracle. Try :
Remember this variable must be set on the client (on the machine on which you run your client, it may be the machine where the Oracle server runs), and your client terminal must be compliant with UTF-8, or it won't display data correctly. If your client terminal can't display UTF-8, then use ISO-8859-1, and set NLS_LANG to AMERICAN_AMERICA.WE8ISO8859P1. You just have to be consistent on the client (always use the charset that is in NLS_LANG) and on the server (but here you always use the same charset ), Oracle will do the right charset translations for you.
These charset translations are a bit hard to understand at first, and having a UTF-8 db, I myself spent some time understanding how it worked exactly, sometimes wondering which magical spells were hiding behind these translations . But when you get the thing, it always works the same and becomes easy enough.