Encoding problems with sqlplus, Linux

Question

I use sqlplus and all non-Latin characters appear as '?' when I run select queries. When I run insert or update queries with non-Latin characters I get question marks on a web page.

select * from nls_database_parameters; gives NLS_CHARACTERSET: AL32UTF8. I've exported the NLS_LANG variable: export NLS_LANG=RUSSIAN_CIS.AL32UTF8, but this didn't make any changes.

What is the problem? Of course I use UTF-8 working in Linux terminal and I can see the script file contents executing cat script.sql.

If I execute alter session set nls_language='russian' I also face with question marks in ora-xxxxx messages.

Answer 1

since you confirmed the character set in the database is AL32UTF8, you need to make sure the fonts is installed on the Linux machine, and install the message libraries. that will make ORA_NLS33 has been set appear when you execute alter session set nls_language=

Answer 2

NLS_LANG is used to tell the database which characterset is used by the client so that the characters can be translated correctly when they are transmitted between client and database. There is an Oracle Metalink article that explains that explains the NLS_LANG parameter an contains a lot of references to important other articles: NLS_LANG Explained (How does Client-Server Character Conversion Work?) (Doc ID 158577.1)

It is neither used to tell the client which character set is used by the database because this is not necessary. The client knows the character set of the database because it is connected to it. Nor is it used to tell the database which character set it is using because the database knows which character set it is using.

So it is wrong simply to set the NLS_LANG to the character set of the database. It is a good way to corrupt your character data in the database

The purpose of the NLS_LANG variable is to tell the Oracle client which character set you unix/windows/... system is using.

Sot what is the value you have to set your NLS_LANG variable? On a Linux system do the following:

$ locale
LANG=
LC_CTYPE=en_US.ISO8859-1
LC_NUMERIC=C
LC_TIME=en_US.ISO8859-1
LC_COLLATE=en_US.ISO8859-1
LC_MONETARY=en_US.ISO8859-1
LC_MESSAGES=C
LC_ALL=

The character set of the unix system is defined by LANG, LC_CTYPE and LC_ALL. LANG is overridden by LC_CTYPE which again is overridden by LC_ALL. So in this case the must be NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1. This setting is independent of the character set of the database.

So first you have to check your locale and set the NLS_LANG appropriately.

There are some of other things that can go wrong e.g the database has already corrupt data or the terminl is not configured correctly.

Two references that explains the unix locale settings. https://unix.stackexchange.com/questions/87745/what-does-lc-all-c-do
http://www.gnu.org/savannah-checkouts/gnu/libc/manual/html_node/Locale-Categories.html