Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Are the table columns defined as varchar2 or nvarchar2, and if the latter, what's your national character set (NLS_NCHAR_CHARACTERSET in nls_database_parameters)? –  Alex Poole Jun 20 '12 at 16:30
    
Thank you for replying, Alex. NLS_NCHAR_CHARACTERSET in nls_database_parameters is UTF8, but the table columns are defined as varchar2, like: "NAME" VARCHAR2 (20 CHAR). –  Kremchik Jun 21 '12 at 8:43

2 Answers 2

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=

share|improve this answer

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. http://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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.