In oracle, how do I change my session to display U

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

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.

Answer 2

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 3

Therefore, before starting '$ sqlplus' on OS, run the followings:

• On Windows

  set NLS_LANG=AMERICAN_AMERICA.UTF8

• On Unix (Solaris and Linux, centos etc)

  export NLS_LANG=AMERICAN_AMERICA.UTF8

It would also be advisable to set env variable in your '.bash_profile' [on start up script]

This is the place where other ORACLE env variables (ORACLE_SID, ORACLE_HOME) are usually set.

just fyi - SQL Developer is good at displaying/handling non-English UTF8 characters.