| Problem with Special Symbol (£) - is it NLS_LANg Setting Problem? [message #278644] |
Mon, 05 November 2007 04:27  |
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 #278662 is a reply to message #278644] |
Mon, 05 November 2007 05:52  |
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 #279053 is a reply to message #278644] |
Tue, 06 November 2007 14:09 |
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 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
