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

Question

I have the following translation table in 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 outdated problems, I cannot convert the entire database to UTF-8, are there any other options?

The current national character set is AL16UTF16. The usual character set is WE8ISO8859P1.

I am currently using java 1.6

The above example is simplified. This is what the request 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 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 used direct JDBC to write to the database, the JDBC Developer's Guide has an example of writing data to the NVARCHAR2 column . If you use 1.5 JVM, you must use the OraclePreparedStatement.setFormOfUse call for each NVARCHAR2 column. In 1.6 JVM, life becomes easier because JDBC 4.0 added the NCHAR and NVARCHAR2 types. If you use 1.5 JVMs, getting an ORM structure like Spring to use Oracle Extensions for JDBC may not be trivial. I am not familiar with Spring enough to find out what steps are needed for this.

Potentially, you can change the connection string to specify defaultNChar = true. This will force the driver to process all character columns using the national character set. This may be enough to solve your problem without getting Spring to use the OraclePreparedStatement extensions.