We help IT Professionals succeed at work.

Oracle Nls Lang problems

Obi
Obi asked
on
Our officess are in London England.

The database character set and nchar_ characterset was set to we8is08859p1.

The database is oracle 8i.6.

When we enter # (pound sign) in sqlplus  it prints to the screen as # and inserts #  in the db instead of
#(pound). The env variable ORA_NLS33 and nls_lang are set to

ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data

nls_lang=English.united kingdom.we8is08859p1

when I do select * from nls_database_parameters I get

SQL> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               WE8ISO8859P1
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_NCHAR_CHARACTERSET         WE8ISO8859P1
NLS_RDBMS_VERSION              8.1.6.0.0

18 rows selected.

What do I need to do to make sure the pound sign is printed in sqlpus and inserted in the db.
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2008

Commented:
NLS_CURRENCY


This parameter specifies the character string returned by the number format mask
L, the local currency symbol, overriding that defined implicitly by NLS_
TERRITORY. For example, to set the local currency symbol to "Dfl " (including a
space), the parameter should be set as follows:
NLS_CURRENCY = "Dfl "
In this case, the query
SQL> SELECT TO_CHAR(TOTAL, 'L099G999D99') "TOTAL"
> FROM ORDERS WHERE CUSTNO = 586;
would return
TOTAL
-------------
Dfl 12.673,49
Table 2?9 Currency Format Examples
Country Example
Estonia 1 234,56 kr
Germany 1.234,56 DM
Japan ?1,234.56
UK ?1,234.56
US $1,234.56
Parameter type: String
Parameter scope: Initialization Parameter, Environment Variable, and
ALTER SESSION
Default value: Default local currency symbol for a particular territory
Range of values: Any valid currency symbol string
You can alter the default value of NLS_CURRENCY by changing its value in the
initialization file and then restarting the instance, and you can alter its value during
a session using an ALTER SESSION SET NLS_CURRENCY statement.



NLS_ISO_CURRENCY

This parameter specifies the character string returned by the number format mask
C, the ISO currency symbol, overriding that defined implicitly by NLS_
TERRITORY.
Local currency symbols can be ambiguous; for example, a dollar sign ($) can refer to
US dollars or Australian dollars. ISO Specification 4217 1987-07-15 defines unique
"international" currency symbols for the currencies of specific territories (or
countries).
For example, the ISO currency symbol for the US Dollar is USD, for the Australian
Dollar AUD. To specify the ISO currency symbol, the corresponding territory name
is used.
NLS_ISO_CURRENCY has the same syntax as the NLS_TERRITORY parameter,
and all supported territories are valid values. For example, to specify the ISO
currency symbol for France, the parameter should be set as follows:
NLS_ISO_CURRENCY = FRANCE
In this case, the query
SQL> SELECT TO_CHAR(TOTAL, 'C099G999D99') "TOTAL"
> FROM ORDERS WHERE CUSTNO = 586;
returns
TOTAL
-------------
FRF12.673,49
Parameter type: String
Parameter scope: Initialization Parameter, Environment Variable, and
ALTER SESSION
Default value: Derived from NLS_TERRITORY
Range of values: Any valid territory name
You can alter the default value of NLS_ISO_CURRENCY by changing its value in
the initialization file and then restarting the instance, and you can alter its value
during a session using an ALTER SESSION SET NLS_ISO_CURRENCY statement.
Typical ISO currency symbols are shown in Table 2?10.
NLS_DUAL_CURRENCY
You can use this parameter to override the default dual currency symbol defined in
the territory. When starting a new session without setting NLS_DUAL_
CURRENCY, you will use the default dual currency symbol defined in the territory
of your current language environment. When you set NLS_DUAL_CURRENCY,
you will start up a session with its value as the dual currency symbol.
NLS_DUAL_CURRENCY was introduced to help support the Euro. The following
Table 2?11 lists the character sets that support the Euro symbol:
Table 2?10 ISO Currency Examples
Country Example
Estonia 1 234 567,89 EEK
Germany 1.234.567,89 DEM
Japan 1,234,567.89 JPY
UK 1,234,567.89 GBP
US 1,234,567.89 USD
Parameter type: String
Parameter scope: Initialization Parameter, Environment Variable, and
ALTER SESSION
Default value: Default dual currency symbol for a particular territory
Range of values: Any valid name
Table 2?11 Character Sets that Support the Euro Symbol
Name Description Euro Code Value
D8EBCDIC1141 EBCDIC Code Page 1141 8-bit Austrian German 0x9F
DK8EBCDIC1142 EBCDIC Code Page 1142 8-bit Danish 0x5A
S8EBCDIC1143 EBCDIC Code Page 1143 8-bit Swedish 0x5A
I8EBCDIC1144 EBCDIC Code Page 1144 8-bit Italian 0x9F


NLS_MONETARY_CHARACTERS


specifies the characters that indicate monetary
units, such as the dollar sign ($) for U.S. Dollars, and the cent symbol (?) for cents.
The two characters specified must be single-byte and cannot be the same as each
other. They also cannot be any numeric character or any of the following characters:
plus (+), hyphen (-), less than sign (<), greater than sign (>).



NLS_CREDIT

NLS_CREDIT sets the symbol that displays a credit in financial reports. The default
value of this parameter is determined by NLS_TERRITORY.
This parameter can be specified only in the client environment. It can be retrieved
through the OCIGetNlsInfo function.



NLS_DEBIT

NLS_DEBIT sets the symbol that displays a debit in financial reports. The default
value of this parameter is determined by NLS_TERRITORY.
This parameter can be specified only in the client environment. It can be retrieved
through the OCIGetNlsInfo function.
Parameter type: String
Parameter scope: Environment Variable
Default value: Derived from NLS_TERRITORY
Range of values: Any string, maximum of 9 bytes (not including null)
Parameter type: String
Parameter scope: Environment Variable
Default value: Derived from NLS_TERRITORY
Range of values: Any string, maximum of 9 bytes (not including null)



Well, NLS_CURRENCY has nothing to do with it. It controls currency symbol when converting number to string or other types of financial procedures, however it does not control how keybord keys are displayed. This is controlled by input locale. And actually, you would not be able to set NLS_CURRENCY to #, since, as Obi mentioned, pressing keybord pound key results in #, rather than in #. Yes, you can set it indirectly via, for example, NLS_TERRITORY:
SQL> select to_char(99,'L999') from dual;

TO_CHAR(99,'L9
--------------
           $99

SQL> alter session set nls_territory='UNITED KINGDOM';

Session altered.

SQL> select to_char(99,'L999') from dual;

TO_CHAR(99,'L9
--------------
           #99

However:

SQL> select '#' from dual;

'
-
#

still displays #. Obi, you do tell what is client's OS. If you are on NT (or other Windows OS), go to Settings, Control Panel, Regional Settings. Select Input Locales tab. Click Add button and add English(United Kingdom) locale. EN icon will appear on task bar in the lower right corner of your screen. Start SQL*Plus session. Click EN icon and select English(United Kingdom). Now pressing pound key will display #. And if you insert pound sign into a table while in English(United Kingdom) locale, select will always display it as # regardless of the locale set on your box. Same way inserting pound sign into a table while in English(United States) locale, select will always display it as # regardless of the locale set on your box.
SQL> create table xtbl(x char(1));

Table created.

SQL> insert into xtbl values('#');

1 row created.

SQL> insert into xtbl values('#');

1 row created.

SQL> select * from xtbl;

X
-
#
#

Solomon Yakobson.
Well, it looks like Experts-Exchange itself has NLS related issues. Pound was displayed properly on my screen right before pressing submit button.

Solomon Yakobson.
CERTIFIED EXPERT
Author of the Year 2009

Commented:
Hi Obi,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days.  I will suggest to:

    Accept syakobson's comment(s) as an answer.

Obi, if you think your question was not answered at all or if you need help, you can simply post a new comment here.  Community Support moderators will followup.

Please do not accept this comment as an answer!

EXPERTS: Your input for closing recommendations are REQUESTED.
==========
DanRollins -- EE database cleanup volunteer
Force accepted

** Mindphaser - Community Support Moderator **
This is exactly the issue I'm trying to resolve at the moment, unfortunately due to the NLS issue experienced by Experts Exchange themselves, the solution given above doesn't help as pound symbols are displayed as hashes!!!!
Come on Experts Exchange, sort your NLS issues out!!!
Bye the way, why is it that the dollar symbol is a standard ascii character whereas the pound symbol is an extended character and it is necessary to jump through loops to use it?
Maybe we Brits need to have a bscii - British Standard Code for Information Interchange so that we aren't dictated to by the yanks ;-)