We've recently hit a snag where a trademark symbol is being copied from one Oracle database to another, but have had it come across as a '?'.
We've tracked the issue to the destination database being configured with a character set of 'US7ASCII'. Unfortunately, rebuilding the database to address this is not something we can do at the present time.
Is there a way we can define a specific column to use a character set other than the character set configured on the server?
You could use the UTF-7 encoding to store arbitrary Unicode strings in a 7bit encoding. The UTF-7 encoding of Unicode text happens to be perfectly legal ASCII text.
Of course this would require special attention wherever you want to use the values, because they are not readable as-is (and UTF-7 is used rarely enough that you probably don't have support for it everywhere).
Alternatively, you could go with a more common encoding such as BASE64, but that has the drawback that even almost-ASCII text is completely unreadable in that encoding. That means that you don't get at least something human-readable, when some part does not handle the encoding correctly.
Hi Adam,
you could use a NVARCHAR2 column instead of a VARCHAR2 column. Look in the view NLS_DATABASE_PARAMETERS to determine the NVARCHAR2 character set (it will always support unicode).