Wendy Gibbons wrote:i think it is the AL..16.. that is the problem, I think it needs to also be AL..32.. for varchar to accept unicode characters.
This is based on an old forum disscussion
https://forums.oracle.com/forums/thread.jspa?threadID=356363
but if you can it would be worth trying out.
I got exactly the opposite impression from the discussion you referenced (it states that both encodings can accomodate Unicode). In any case,
VARCHAR2 datatype uses
NLS_CHARACTERSET encoding, while
NVARCHAR2 uses
NLS_NCHAR_CHARACTERSET. Since the column is declared as
VARCHAR2, the
AL16UTF16 encoding is not involved at all.
Regarding the original question: which tool are you using to display the data in the database? Make sure that tool can display the characters. For example the Windows console cannot display full Unicode at any time, but even other, GUI based tools might have problems. Check which encoding your client uses (this is one possible source of error) and whether the font used to display the data contains all Unicode characters (other possible source of error). If still in doubt, you can use the
DUMP function to display how the data is actually stored in the database, though you might have tough time interpreting it.
If the data can be shown to be stored incorrectly,
you should check how they are being written to it. I assume this is a
Java process. Check whether the XML file is processed in the correct encoding; you could also display the value before writing it into the database to verify it contains the correct national character(s).
Edit: regarding the encodings, see also
http://docs.oracle.com/cd/B19306_01/server.102/b14225/ch6unicode.htm and
http://docs.oracle.com/cd/B28359_01/server.111/b28298/ch2charset.htm#autoId9