Home » RDBMS Server » Server Administration » Problem with Special Symbol (£) - is it NLS_LANg Setting Problem? (Oracle 9.2.0.6.0 on RHEL 2.4.9)
Problem with Special Symbol (£) - is it NLS_LANg Setting Problem? [message #278644] Mon, 05 November 2007 04:27 Go to next message
orasaket
Messages: 70
Registered: November 2006
Member
Hi

I am using oracle database (server) 9.2.0.6.0

I am accessing it through 9.0.1.4.0 Oracle client

I am facing problem while inserting the '' symbol.

I am not sure if it is NLS_LANG setting which causes this problem.


Following ar the results
1) insert the '' from sqlplus 
read from sqlplus                      - junk
read from sqlplusw                     - junk
read from TOAD                         - junk
read directly from server (Telnet using putty)- junk

2) insert the '' from sqlplusw 
read from sqlplus                        - junk                      
read from sqlplusw                       -          (proper)
read from TOAD                           -          (proper)
read directly from server (Telnet using putty)- junk

3) insert the '' from sqlplus 
read from sqlplus                        - junk                      
read from sqlplusw                       -          (proper)
read from TOAD                           -          (proper)
read directly from server (Telnet using putty)- junk

4)
read from sqlplus                      - junk
read from sqlplusw                     - junk
read from TOAD                         - junk
read directly from server (Telnet using putty)- junk


what could be the reason

I checked nlslang values in my windows Registry
HKEY_LOCAL_MACHINE - SOFTWARE - ORACLE - HOME

and found the following value
ENGLISH_UNITED KINGDOM.WE8MSWIN1252

Also the 'Regional and Language Setting' in Control Pannel has following value
(English) United Kingdom


Also following are other NLS details on the server
(i am suspicios about nls_dual_currency which has different value for different query)


SELECT * FROM NLS_SESSION_PARAMETERS; (as seen from TOAD)

NLS_LANGUAGE	         AMERICAN
NLS_TERRITORY	         UNITED KINGDOM
NLS_CURRENCY	         
NLS_ISO_CURRENCY	 UNITED KINGDOM
NLS_NUMERIC_CHARACTERS	 .,
NLS_CALENDAR	         GREGORIAN
NLS_DATE_FORMAT	         DD-MON-RR
NLS_DATE_LANGUAGE	 AMERICAN
NLS_SORT	         BINARY
NLS_TIME_FORMAT	         HH24.MI.SSXFF
NLS_TIMESTAMP_FORMAT	 DD-MON-RR HH24.MI.SSXFF
NLS_TIME_TZ_FORMAT	 HH24.MI.SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT	 DD-MON-RR HH24.MI.SSXFF TZR
NLS_DUAL_CURRENCY	 
NLS_COMP	         BINARY
NLS_LENGTH_SEMANTICS	 BYTE
NLS_NCHAR_CONV_EXCP	 FALSE


SELECT * from nls_database_parameters; (as seen from TOAD)

NLS_NCHAR_CHARACTERSET	  AL16UTF16
NLS_LANGUAGE	          AMERICAN
NLS_TERRITORY	          AMERICA
NLS_CURRENCY	          $
NLS_ISO_CURRENCY	  AMERICA
NLS_NUMERIC_CHARACTERS	  .,
NLS_CHARACTERSET	  WE8ISO8859P15
NLS_CALENDAR	          GREGORIAN
NLS_DATE_FORMAT	          DD-MON-RR
NLS_DATE_LANGUAGE	  AMERICAN
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_COMP	          BINARY
NLS_LENGTH_SEMANTICS	  BYTE
NLS_NCHAR_CONV_EXCP	  FALSE
NLS_RDBMS_VERSION	  9.2.0.6.0


If i fire following statement..nothing is returned (fired from Telnet using putty)

SQL> !echo $NLS_LANG

Do i need to change nls setting to match with that of database server's?

Please Suggest

Thanks and Regards,
OraSaket
Re: Problem with Special Symbol (£) - is it NLS_LANg Setting Problem? [message #278649 is a reply to message #278644] Mon, 05 November 2007 05:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
DOS box does not use the same character set than Windows.
It uses OEM character set (most likely code page 850).
If you execute "chcp 1252" before using sqlplus (DOS mode) then you should have the same behaviour in DOS and Windows.

Regards
Michel
Re: Problem with Special Symbol (£) - is it NLS_LANg Setting Problem? [message #278662 is a reply to message #278644] Mon, 05 November 2007 05:52 Go to previous messageGo to next message
orasaket
Messages: 70
Registered: November 2006
Member
Michel,

As usual Salute to you.

I done what you said and things happen correctly.

Apologies for mistake in section 3 and 4 send earlier

here are the earlier tests and results


1) insert the '' from sqlplus 
read from sqlplus                      - junk
read from sqlplusw                     - junk
read from TOAD                         - junk
read directly from server (Telnet using putty)- junk

2) insert the '' from sqlplusw 
read from sqlplus                        - junk                      
read from sqlplusw                       -          (proper)
read from TOAD                           -          (proper)
read directly from server (Telnet using putty)- junk

3) [COLOR=red]insert the '' from TOAD[/COLOR]
read from sqlplus                        - junk                      
read from sqlplusw                       -          (proper)
read from TOAD                           -          (proper)
read directly from server (Telnet using putty)- junk

4) [COLOR=red]insert the '' from Telnet[/COLOR]
read from sqlplus                      - junk
read from sqlplusw                     - junk
read from TOAD                         - junk
read directly from server (Telnet using putty)- junk

and now 1) has changed as 

read from sqlplus                        - junk                      
read from sqlplusw                       -          (proper)
read from TOAD                           -          (proper)
read directly from server (Telnet using putty)- junk


One thing I could not understand is 'till the time i was executing chcp 1252 i was able to see '' character on the console as soon as i was typing it.
However once i have executed chcp 1252 and as i type '', i can see junk character on console but things are ok in database.

Please suggest

Also please help me understanding following?

Does NLS_LANG comes in to picture while these special characters? why not in this case?
Do we need to set NLS_LANG on client same as database server?In which case?
What is OEM character set?

Thanks and Regards,
OraSaket
Re: Problem with Special Symbol (£) - is it NLS_LANg Setting Problem? [message #278669 is a reply to message #278662] Mon, 05 November 2007 06:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You always have to set NLS_LANG with the actuel character set you use.
For TOAD, I don't use it but I know it modified NLS parameters on the fly. You have to refer to TOAD manual.
sqlplusw use NLS_LANG in the registry or by default Windows character set.
OEM character set is the one used in DOS box (comes from history before Windows existed), you have to set either set DOS code point to Windows NLS_LANG (this is the purpose of chcp 1252) or explicitly set you NLS_LANG to OEM character set (if Oracle knows it). chcp alone gives the current code page (that is character set in current language).
I don't know which set use your telnet. Maybe configure in its configuration file or something like that. Check documentation.

Regards
Michel
Re: Problem with Special Symbol (£) - is it NLS_LANg Setting Problem? [message #279053 is a reply to message #278644] Tue, 06 November 2007 14:09 Go to previous messageGo to next message
orasaket
Messages: 70
Registered: November 2006
Member
Hi Michel,

thanks for your your help and advice

However still i have following queries


Shall i always (on oracle client) set NLS_LANG in my windows registry and change regional setting (locale) to match database server settings?

Since nls_session_parameters and nls_database_parameters were having different values
how i was getting symbol correctly entered from sqlplusw?

does these nls_lang settings affect while reading from database as well?

in case we haven't set anything on oracle client which values are set while interacting with database?

Please help me understand this

Thanks and Regards,
OraSaket



Re: Problem with Special Symbol (£) - is it NLS_LANg Setting Problem? [message #279132 is a reply to message #279053] Wed, 07 November 2007 01:35 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

Shall i always (on oracle client) set NLS_LANG in my windows registry and change regional setting (locale) to match database server settings?

You have to set it accordingly with the character set used by the environment.
Values are taken from 1) environment variable 2) registry.

Quote:

Since nls_session_parameters and nls_database_parameters were having different values
how i was getting symbol correctly entered from sqlplusw?

The difference between the character set does not matter (in short). It just tells Oracle which character set are used both sides. That is how Oracle has to convert code point from one to the other one.
Now when you type , sqlplusw does not receive a pound but 163 if your Window code page is 1252 and sent this 163 to the server along with your NLS_CHARACTER_SET or NLS_LANG setting (WE8MSWIN1252 if correctly set).
Then the server received 163 in WE8MSWIN1252, it checks its conversion table and see (assuming your database is in WE8EBCDIC500), then for me it is 177 and store 177.

Quote:

does these nls_lang settings affect while reading from database as well?

When you select, it does the opposite.

Quote:

in case we haven't set anything on oracle client which values are set while interacting with database?

I think US7ASCII is the default value.
Try it: your set NLS_LANG without the character set part and then query v$nls_parameters to know which one is taken.

Regards
Michel



Previous Topic: Database shutdown in the midnight
Next Topic: I think this is a magic . am i right ?
Goto Forum:
  


Current Time: Sat Dec 03 13:50:42 CST 2016

Total time taken to generate the page: 0.04274 seconds