Problem with characters between ASCII 128 and 159
843854Dec 19 2003 — edited May 6 2005I'm working on a web service (JSP) that queries data from several Oracle 8.1.7 database in different countries using different code pages.
I thought I had a problem with a particular database but I've narrowed it down to a Java or JDBC problem by building a simple GUI query app with java (no JSP or tomcat involved).
If I do a simple select 'chr(138) from dual' I expect to get '�' (S with upside-down circumflex) returned as I do in TOAD or SQL*Plus but instead I get a block symbol '�' (value 65533 when cast to int in java).
If I do 'insert chr(138) into some_table' using JDBC the correct character gets inserted and I can see it using SQL*Plus (but not in the Java app) so I think the problem occurs during the conversion of the result set values to their Unicode representation. I retrieve the values using resultset.getString(), if I use getBytes() and look at the first byte the value is -17 instead of 138 which is what I would expect.
Trying to track the problem down I've done many tests using different client systems and accessing various databases and using various NLS_LANG values (OCI and thin client) but I always get the same results.
To summarize: performing a simple 'select chr(nnn) from dual' using a value nnn between 128 and 159 always returns the same unrecognizable character in Java/JDBC.
The same query in TOAD or SQL*plus returns a correct character depending on the value, database and NLS_LANG setting.
I've also noticed that doing 'select ascii('�') from dual' returns 169 in the java app but 138 in TOAD or SQL*Plus.
Is this a Java/JDBC bug or is there something I haven't thought of?
Can anyone verify this behaviour?
Thanks Con