CODEWITHSUNDEEP
databaseoracle-databaseunicodenon-unicode
John Assymptoth
John Assymptoth

Reputation: 8507

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

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).

Upvotes: 1

Views: 2050

Answers (1)

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

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.

Upvotes: 2

Related Questions