Spool Unicode data in sqlplus to ISO-client without character set conversion

Question

I'm currently facing the following problem:

We have an Oracle 11g database with character set AL32UTF8, and an sqlplus-client with NLS_LANG=GERMAN_GERMANY.WE8ISO8859P1. Stored in the database are various non-ISO8859-1 characters.

Now, when the client connects to the database and tries to spool these special characters, a character set conversion takes place according to the client's NLS_LANG setting and the special characters are lost.

Unfortunately, changing the client-NLS_LANG isn't easily possible (legacy application - it's complicated...).

Is there an easy, or standard way to spool this data without losing these special characters?

I've thought about encoding the data in base64, spooling it, and then decoding it client-side.

But is there a better way?

Answer 1

Without setting NLS_LANG it's going to be tough.

How good are you at simple coding or parsing/manipulating strings?

You can do:

select DUMP(field) from table;

Then write some sort of conversion code to parse the comma-delimited numbers from the dump into Unicode.

Answer 2

You can set NLS_LANG value simply on command line:

SET NLS_LANG=GERMAN_GERMANY.AL32UTF8

The NLS_LANG value persist for this window as long as the window stays open.

Note, when you set NLS_LANG then it does not have any effect on character set which is used by sqlplus!

sqlplus inherits character set from current command line, i.e. you have to change this also:

C:\>chcp 65001
Active code page: 65001
C:\>SET NLS_LANG=GERMAN_GERMANY.AL32UTF8
C:\>sqlplus ...

Here a list of MS Code Page Identifiers