Skip to Main Content
  • Questions
  • NLS_LANG configuration for PRO*C app

Breadcrumb

Question and Answer

Sergiusz Wolicki

Thanks for the question, Mustafa.

Asked: October 19, 2020 - 9:35 am UTC

Answered by: Sergiusz Wolicki - Last updated: October 29, 2020 - 2:40 pm UTC

Category: Database Development - Version: 11g

Viewed 100+ times

You Asked

Hi Gurus,

I have a problem with my Pro*C application.

MY OS -> RedHat release 5.5 (Tikanga)

My database;

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0
Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

nls_paramters;

NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_CHARACTERSET WE8ISO8859P9
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

Before I run my C app, I set NLS_LANG. For example ;

export NLS_LANG=AMERICAN_AMERICA.UTF8

and I run appl on unix terminal ->
./app sqlstmt"select * from mus_test_char"


Connected to ORACLE as user: mus/mus123@orcl
Unloading 'select * from must_test_char'
Array size = 10
IDD,TEXT
"1"|"(null)"

if I export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P9

and I run
./app sqlstmt"select * from mus_test_char" 


Connected to ORACLE as user: mus/mus123@orcl
Unloading 'select * from must_test_char'
Array size = 10
IDD,TEXT
"1"|"▒aaaa▒aaaaa▒aaaaa▒aaaaa▒aaaaa▒aaaaa▒aaaa▒aaa"
1 rows extracted

If I unset NLS_LANG -> unset NLS_LANG

and I run ->
./app sqlstmt"select * from mus_test_char"


Connected to ORACLE as user: mus/mus123@orcl
Unloading 'select * from must_test_char'
Array size = 10
IDD,TEXT
"1"|"Saaaa?aaaaaUaaaaaOaaaaaCaaaaa?aaaaa?aaaaoaaa"
1 rows extracted


My original data in database following;

1 ŞaaaaĞaaaaaÜaaaaaÖaaaaaÇaaaaağaaaaaİaaaaöaaa

Can you help me about this problem ?

Edit:

I tried my app another Database. It's NLS Settings , AMERICAN_AMERICA.AL32UTF8
And I set on my client NLS_LANG=AMERICAN_AMERICA.UTF8

Then I run my app on that machine , I get values correctly..

1 ŞaaaaĞaaaaaÜaaaaaÖaaaaaÇaaaaağaaaaaİaaaaöaaa


Thanks.

and we said...


When you run your Pro*C app on a Unix terminal or a PC emulator of such terminal, you must make sure that NLS_LANG's character set corresponds to the character encoding in which the terminal/emulator is configured. There are too many terminals and emulators for me to tell how to check this configuration directly. However, you could try something like:

$ od -t x1
<enter the character  'Ş'><press enter>
<enter cntrl-D or whatever 'stty -a' shows as 'eof' character>


This will display something like:

0000000 c5 9e 0a
0000003


If you see 'c5 9e 0a', then your terminal is configured for utf-8 and NLS_LANG should be .AL32UTF8. If you see 'de 0a', then your terminal is configured for iso8859-9 and NLS_LANG should be .WE8ISO8859P9

In any case, if your database character set NLS_CHARACTERSET is AL32UTF8 and you select your column value as

SELECT DUMP(text,1016) FROM mus_test_char


then you should see 'c5,9e' corresponding to 'Ş' in the column value. If your database character set NLS_CHARACTERSET is WE8ISO8859P9, then you should see 'de'. If you see other codes, then there is still a configuration problem. The so-called pass-through configuration (NLS_LANG=NLS_CHARACTERSET) can sometimes fool people by showing characters correctly while they are actually encoded incorrectly in the database.



and you rated our response

  (1 rating)

Reviews

October 29, 2020 - 10:40 pm UTC

Reviewer: A reader

Hi,

Thanks for your response.

I tried ;

 od -t x1
<enter the character  'Ş'><press enter>
<enter cntrl-D or whatever 'stty -a' shows as 'eof' character>


and I saw same result.

0000000 c5 9e 0a
0000003


sql result corresponding to 'Ş';

SELECT DUMP(text,1016) FROM mus_test_char

Typ=1 Len=1 CharacterSet=WE8ISO8859P9: de


If I set NLS_LANG AL32UTF8 , I get column NULL on terminal.
If I set NLS_LANG WE8ISO8859P9 , I get column value wrong.


Thanks.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database