I have a Java application that stores data in an old Oracle database. The database’s charset was originally set to Win-1252 and I don’t have the option of changing it.
It turns out Oracle has an alternative set of column types (NCHAR, NVARCHAR2, NCLOB) which can be used to store unicode in a database with a different encoding.
To convert a column we first add a new temporarily named column with the new type (e.g. NVARCHAR2, NCLOB). Delete the old column. Rename the temp column to the correct name.
ALTER TABLE items ADD (nametmp NVARCHAR2(256)); UPDATE items SET nametmp = TO_NCHAR(name); ALTER TABLE items DROP COLUMN name; ALTER TABLE items RENAME COLUMN nametmp TO name;
To enable the thin JDBC driver to send NCHAR strings by default set connection
defaultNChar=true or system property
Even with this set I found Unicode characters could only be passed via bound
parameters. String literals in queries, even with the N string flag like
SET name = N'example' silently had their characters replaced.