How can I save unicode columns in non-unicode Oracle database with VARCHAR2?

Question

How can I save unicode characters in a Oracle database, configured in a non-unicode charset (this configurations is impossible to change)?

The text will be saved in VARCHAR2 columns (it can't be NVARCHAR2).

Answer 1

There are a number of ways you can store arbitrary data (in this case UTF-encoded) in a VARCHAR2 field:

• You could convert the Unicode characters to their U+XXXX form or their &#YYYY form. Only some of the characters would need a conversion.
• You could store the binary data rawtohex-encoded.

• You could also use standard functions such as UTL_ENCODE.mimeheader_encode:

  /* needs to be checked on a non-unicode db */ 
  SQL> declare
    2     l nvarchar2(200);
    3     p nvarchar2(200);
    4  begin
    5     l := UTL_ENCODE.MIMEHEADER_ENCODE (
    6        buf            => nchr(352),--'Š',
    7        encode_charset => 'UTF8',
    8        encoding       => UTL_ENCODE.QUOTED_PRINTABLE
    9     );
   10     dbms_output.put_line('encoded string: ' || l);
   11     p := utl_encode.mimeheader_decode (
   12        buf => l
   13     );
   14     dbms_output.put_line('decoded string: ' || p);
   15  end;
   16  /
  
  encoded string: =?UTF8?Q?=C5=A0?=
  decoded string: Š
  
  Statement processed.