Oracle, utf-8, NVARCHAR2, and a lot of confusion

Question

I have the following "translation" table in an oracle 10g database:

ID  VARCHAR2(100 BYTE)
LANGUAGE    CHAR(2 BYTE)
COUNTRY CHAR(2 BYTE)
TRANSLATION NVARCHAR2(2000 CHAR)
TRACK_TIMESTAMP DATE
TRACK_USER  VARCHAR2(2000 BYTE)

When I try to do this:

update translation set translation = 'œ' where id = 'MY_ID' And language = 'fr';

Then I run this:

select * from translation where id = 'MY_ID' and language = 'fr';

and the translation column shows: S instead of œ and I have no idea why.

Due to legacy issues I cannot convert the whole database to use UTF-8, are there any other options?

Currently the national character set is AL16UTF16. The ordinary character set is WE8ISO8859P1.

I am currently using java 1.6

The above is a simplified example. Here is what the query looks like in my actual application:

UPDATE TRANSLATION SET TRANSLATION=? WHERE TRANSLATION.COUNTRY=? and TRANSLATION.ID=? and TRANSLATION.LANGUAGE=? 1=1,800 - 2,500 œufs par heure 2=CA 3=3_XT_FE_ECS18 4=fr

The problem here is instead of adding œufs it adds ¿ufs

Answer 1

Since you are using bind variables rather than hard-coded literals, you should be able to pass Unicode strings to your UPDATE statement.

If you were using straight JDBC to write to the database, there is an example in the JDBC Developer's Guide on writing data to a NVARCHAR2 column. If you are using a 1.5 JVM, it is necessary to use the OraclePreparedStatement.setFormOfUse call for each NVARCHAR2 column. In a 1.6 JVM, life gets easier because JDBC 4.0 added NCHAR and NVARCHAR2 types. If you are using a 1.5 JVM, getting an ORM framework like Spring to use the Oracle extensions to JDBC may be a non-trivial undertaking. I'm not familiar enough with Spring to know what steps would be necessary for that to happen.

Potentially, you may be able to modify the connection string to specify defaultNChar=true. That will force the driver to treat all character columns using the national character set. That may be enough to resolve your problem without getting Spring to use the OraclePreparedStatement extensions.