In oracle, how do I change my session to display UTF8?

Question

I can't figure out Oracle's encryptic syntax for the life of me. This is Oracle 10g

My session's NLS_LANGUAGE is currently defaulting to AMERICAN. I need to be able to display UTF8 characters.

Below are some of my attempts, all incorrect:

ALTER SESSION SET NLS_LANGUAGE='UTF8'
ALTER SESSION SET NLS_LANGUAGE='AMERICAN_AMERICA.UTF8'

What's the secret command?

Answer 1

Okay, per http://www.oracle.com/technology/tech/globalization/htdocs/nls_lang%20faq.htm:

NLS_LANG cannot be changed by ALTER SESSION, NLS_LANGUAGE and NLS_TERRITORY can. However NLS_LANGUAGE and /or NLS_TERRITORY cannot be set as "standalone" parameters in the environment or registry on the client.

Evidently the "right" solution is, before logging into Oracle at all, setting the following environment variable:

export NLS_LANG=AMERICAN_AMERICA.UTF8

Oracle gets a big fat F for usability.

Answer 2

The character set is part of the locale, which is determined by the value of NLS_LANG. As the documentation makes clear this is an operating system variable:

NLS_LANG is set as an environment variable on UNIX platforms. NLS_LANG is set in the registry on Windows platforms.

Now we can use ALTER SESSION to change the values for a couple of locale elements, NLS_LANGUAGE and NLS_TERRITORY. But not, alas, the character set. The reason for this discrepancy is - I think - that the language and territory simply effect how Oracle interprets the stored data, e.g. whether to display a comma or a period when displaying a large number. Wheareas the character set is concerned with how the client application renders the displayed data. This information is picked up by the client application at startup time, and cannot be changed from within.