Solved

Oracle Database Character Set Views

Posted on 2001-09-17
5
3,835 Views
Last Modified: 2008-03-10
Thanks for your help and input below.
last problem.....

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) in sqlplus  it prints to the screen as # and inserts #  in the db instead of #(pound). I noticed that
the nls  env variable is ORA_NLS=$ORACLE_HOME/ocommon/nls/admin/data

rather than ORA_NLS3=$ORACLE_HOME/ocommon/nls/admin/data

, is that why we are having  problems entering the #(pound) sign? Or do I just need to change the nls_currency
value from $ to #(pound).

0
Comment
Question by:Obi
5 Comments
 
LVL 3

Expert Comment

by:Wadhwa
ID: 6487617
connect with sys and issue following query , it will tell u all the nls settings on the server.
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
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



DAtabase character set means the chacterset which you defined while creation of database.  whenever u insert something in to the database , oracle implicitily does the conversion based upon this character set and store it in the database.

National character and national lang support set is the database character set which is supported by almost all the languages. like UTF8 or UTF16 .


ORA_NLS (ORA_NLS32, ORA_NLS33) Environment Variables.

ORA_NLS is the path to the language object files and is set on the client side.

For RDBMS 7.2.x version it is called ORA_NLS.
For version 7.3.x it is called ORA_NLS32.
For Oracle 8 it is called ORA_NLS33 because of NLSRTL (NLS Run Time Libraries)
version.

Note:
In this document, ORA_NLSxx term should be generic for all versions.


ORA_NLSxx needs to be set in the following cases.

1. If you need to create database with any character set other than US7ASCII
   on Oracle version 7.x. You must set the environment variable ORA_NLSxx to
 
 $ORACLE_HOME/ocommon/nls/admin/data

When you try to create a database without setting ORA_NLSxx parameter you will
have the error:

  ORA-12701: "CREATE DATABASE character set is not known"
        See: [NOTE:21555.1]

If you are creating database upon installation of Oracle software, you need to
do in the startup file of the oracle software owner.

Before setting ORA_NLS please check that language files were installed
properly on your system. To do that go to directory:

  $ORACLE_HOME/ocommon/nls/admin/data

and issue the following command:

  strings * | grep -i we8iso8859p1

where we8iso8859p1 (or any other than us7ascii) is the character set choose for
the database.

2. When database is already created and SQL Net (or Net8) connections need
   to be established, if connecting through SQL*Net and using a SQL*Plus
   version from Oracle7.3, set the environment variable:

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

If using a version of SQL*PLUS from Oracle8 (i.e V8.0.3 - 8.0.5), set the
environment variable:

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

Note:
When establishing connection through SQL*Net, connecting to an Oracle8
database with a Unicode UTF8 character set, make sure you are using
SQL*Net version 2.3.4 and higher. Otherwise you will get an error, as
SQL*Net V2.3.4 and higher is required to connect to a UTF8 database.
(i.e. ORA-3106)
3. For NCHAR and NLS Compatibility:

     set the ORA_NLS32 environment variable for the release 7.3.x environment

     set the ORA_NLS33 environment variable for the version 8 environment

Verify that the client has the correct NLS character set environment variables.
An error is generated when release 7.3 NLS code tries to load a version 8
character set.

4. When Developer is installed.

Note1:
ORA_NLSxx does not need to be set for Developer 6.0.

For Oracle version 8.x, if we connect from the client and Developer
version 1.6.1 ORA_NLS33 needs to be set:
 
  $ORACLE_HOME/ocommon/nls/admin/datad2k

For other version it should point to:

 $ORACLE_HOME/ocommon/nls/admin

Note2:
$ORACLE_HOME/ocommon/nls/admin/data2k subdirectory is created by
Developer version 1.6.1 only. This is caused by the fact that this
version of Developer uses two versions of NLSRTL library at once: version 3.2
for Oracle common RDBMS libraries (known as RSF on Windows) and version 3.3
for Forms processing. As these two NLSRTL versions needs different *.nlb files,
two data directories are created: 'data' for 3.2 and 'datad2k' for 3.3.

NLSRTL 3.2 files are pointed to by the ORA_NLS32 variable, which defaults from
ORACLE_HOME. NLSRTL 3.3 files are pointed by the ORA_NLS33 variable,
which must be set explicitly to $ORACLE_HOME/ocommon/nls/admin/datad2k.

Note3:
When exporting from the Developer 1.6.1 environment, you might be getting a
ORA-600 [23] [] error. If this is the case, try setting ORA_NLS33 to

  $ORACLE_HOME/ocommon/nls/admin/data

instead of

  $ORACLE_HOME/ocommon/nls/admin/datad2k

This should resolve the internal ORA-600 [23][].

If ORA_NLS is not set or can't be read and the database is started with
other languages and character sets, the database will default and character
set will not be recognized.

This can result in:

1. Character set translation not occurring for clients using a character set
   different from the database's. This may result in corrupt data in certain
   applications as 8-bit or 16-bit or 32-bit may be lost and misinterpreted.

2. ORA-12705 errors.

3. "SQL*Loader-266: Unable to locate character set" error.

4. Messages being displayed in a language different from that expected.
 
5. ORA-3106 fatal two-task communication protocol error. ORA-600[12333] which
   will have the same meaning as ORA-3106 in this case. At the client you may
   see a TNS-12571 or ORA-12571.


You can check which NLS setting are valid for this platform by connecting to
the database as a DBA and issuing the following command:

  SELECT * FROM V$NLS_VALID_VALUES;

Hope this helps
Sam
0
 
LVL 47

Accepted Solution

by:
schwertner earned 100 total points
ID: 6487683
I did my best to collect you answers from different sources. If you need the document "National Lang. Support" (it is the main document) I can email you the .pdf


CHARACTER SET
specifies the character set the database uses to store data. The supported character sets and default value of this parameter depend on your operating system.
Restriction: You cannot specify any fixed-width multibyte character sets as the database character set.

 
 
NATIONAL CHARACTER SET
specifies the national character set used to store data in columns specifically defined as NCHAR, NCLOB, or NVARCHAR2. If not specified, the national character set defaults to the database character set. See Oracle8i National Language Support Guide for valid character set names.
 





Oracle8i is implemented using a client/server rchitecture. The language-dependent operations are controlled by a number of parameters and environment variables on both the client and the server.
On the server, each session started on behalf of a client may run in the same or different locale, and have the
same or different language requirements specified.
A database itself also has a set of session-independent NLS parameters specified at its creation time. Two of them are the DATABASE and the NATIONAL CHARACTER set. They
specify the character set used to store text data in the database. Other parameters, like language and territory, are used in the evaluation of CHECK constraints.
In the event that the client and server specify different character sets, Oracle8i will handle character set conversion of strings automatically.
As far as NLS architecture is concerned, all applications, even these running on the same physical machine as the Oracle instance, are considered clients. For example,
SQL*Plus started by the Unix user which owns Oracle software, from the Oracle Home in which RDBMS software is installed, and connecting to the database through an adapter by specifying the ORACLE_SID, is still considered a client and its behavior is ruled by client-side NLS parameters.
When a client application is started, it initializes its client NLS environment from environment settings. All NLS operations performed locally are executed using these settings. Examples of local NLS operations are display formatting (using item format masks) in Oracle Developer applications or user OCI code executing NLS
OCI functions with OCI environment handles.  When the application connects to a database, a session is created on the server. The new session initializes its NLS environment from NLS instance parameters specified
in the initialization parameter file. These settings can be subsequently changed by an ALTER SESSION statement. The statement changes the session NLS environment only. It does not change the local client NLS environment. The sessionNLS settings are used in the processing of SQL and PL/SQL statements executed on the server.

Character set conversion is often necessary in a client/server computing environment where a client
application may reside on a different computer platform from that of the server, and both platforms
may not use the same character encoding schemes. Character data passed between client and server must
be converted between the two encoding schemes. Character conversion occurs automatically and transparently
via Net8.
In order to change unpropriate database character set if it is not 7ASCII you have to recreate database
as follow( or simular)
CREATE DATABASE ques
   LOGFILE 'D:\ora8v2\oradata\ques\redo01.log' SIZE 1024K,
           'D:\ora8v2\oradata\ques\redo02.log' SIZE 1024K,
           'D:\ora8v2\oradata\ques\redo03.log' SIZE 1024K
       MAXLOGFILES 32
       MAXLOGMEMBERS 2
       MAXLOGHISTORY 1
     DATAFILE 'D:\ora8v2\oradata\ques\system01.dbf' SIZE 98M  REUSE AUTOEXTEND ON NEXT 640K
     MAXDATAFILES 254
    MAXINSTANCES 1
    CHARACTER SET UTF8
    NATIONAL CHARACTER SET CL8MSWIN1251;
If initial character set is 7ASCII
you can use
Alter database set character set UTF8 ...
because UTF8 is superset of 7ASCII.



Oracle's National Language Support (NLS) architecture allows you to store, process,
and retrieve data in native languages. It ensures that database utilities and error
messages, sort order, date, time, monetary, numeric, and calendar conventions
automatically adapt to the native language and locale.
Parameter settings determine the behavior of individual conventions.
Oracle's National Language Support architecture is implemented with the use of the
Oracle NLS Runtime Library. The NLS Runtime library provides a comprehensive
suite of language-independent functions, which allows for proper text and
character processing and language convention manipulations. Behavior of these
functions for a specific language and territory is governed by a set of locale-specific
data identified and loaded at runtime.
The locale-specific NLS data is stored in a directory specified by the ORA_NLS*
environment variable. For each new release, there is a different corresponding
ORA_NLS data directory. For Oracle8i, the ORA_NLS33 directory is used. For
example, on most UNIX platforms, the environment variable ORA_NLS33 should
be set to $ORACLE_HOME/ocommon/nls/admin/data. On Win32 platforms, the
default setting done by the installer should work fine as long as one ORACLE_
HOME has just one release of Oracle.
If your system is running in a multi-version Oracle environment, you must ensure
that the appropriate ORA_NLS* variable (for example, ORA_NLS33) is set and that
the corresponding NLS data files for that release are available.
Table 1?1 Location of NLS Data
Release Environment Variable
7.2 ORA_NLS
7.3 ORA_NLS32
8.0, 8.1 ORA_NLS33
A boot file is used to determine the availability of the NLS objects that can be
loaded. Oracle supports both system and user boot files. The user boot file gives
you the flexibility to tailor what NLS locale objects will be available for the
database, thus helping you control memory consumption. Also, new locale-data can
be added and some locale-data components can be customized.
Oracle8i is implemented using a client/server architecture. The
language-dependent operations are controlled by a number of parameters and
environment variables on both the client and the server. On the server, each session
started on behalf of a client may run in the same or different locale, and have the
same or different language requirements specified.
A database itself also has a set of session-independent NLS parameters specified at
its creation time. Two of them are the database and the national character set. They
specify the character set used to store text data in the database. Other parameters,
like language and territory, are used in the evaluation of CHECK constraints.
In the event that the client and server specify different character sets, Oracle8i will
handle character set conversion of strings automatically.
As far as NLS architecture is concerned, all applications, even these running on the
same physical machine as the Oracle instance, are considered clients. For example,
SQL*Plus started by the Unix user which owns Oracle software, from the Oracle
Home in which RDBMS software is installed, and connecting to the database
through an adapter by specifying the ORACLE_SID, is still considered a client and
its behavior is ruled by client-side NLS parameters.
When a client application is started, it initializes its client NLS environment from
environment settings. All NLS operations performed locally are executed using
these settings. Examples of local NLS operations are display formatting (using item
format masks) in Oracle Developer applications or user OCI code executing NLS
OCI functions with OCI environment handles. See Chapter 5, "OCI Programming",
for further details.
When the application connects to a database, a session is created on the server. The
new session initializes its NLS environment from NLS instance parameters specified
in the initialization parameter file. These settings can be subsequently changed by
an ALTER SESSION statement. The statement changes the session NLS
environment only. It does not change the local client NLS environment. The session
NLS settings are used in the processing of SQL and PL/SQL statements executed on
the server.

Setting NLS Parameters
NLS parameters determine the locale-specific behavior on both the client and the
server. There are four ways to specify NLS parameters:
1. As initialization parameters on the server. You can include parameters in the
initialization parameter file to specify a default session NLS environment. These
settings have no effect on the client side; they control only the server's behavior.
For example:
NLS_TERRITORY = "CZECH REPUBLIC"
2. As environment variables on the client. You can use NLS parameters to specify
locale-dependent behavior for the client, and also override the defaults set for
the session in the initialization file. For example, on a UNIX system:
% setenv NLS_SORT FRENCH
3. As ALTER SESSION parameters. NLS parameters set in an ALTER SESSION
statement can be used to override the defaults set for the session in the
initialization file, or set by the client with environment variables.
SQL> ALTER SESSION SET NLS_SORT = FRENCH;
For a complete description of ALTER SESSION, see Oracle8i SQL Reference.
4. As a SQL function parameter. NLS parameters can be used explicitly to
hardcode NLS behavior within a SQL function. Doing so will override the
defaults set for the session in the initialization file, the client with environment
variables, or set for the session by ALTER SESSION. For example:
TO_CHAR(hiredate, 'DD/MON/YYYY', 'nls_date_language = FRENCH')
The database character set and the national character set are specified in the
CREATE DATABASE statement. For a complete description of CREATE
DATABASE, see Oracle8i SQL Reference.
Table 2?1 shows the precedence order when using NLS parameters. Higher priority
settings will override lower priority settings. For example, a default value will have
the lowest possible priority, and can be overridden by any other method. And
explicitly setting an NLS parameter within a SQL function can override all other
settings ? default, initialization parameter, environment variable, and ALTER
SESSION parameters.
0
 

Author Comment

by:Obi
ID: 6487860
Thanks for your help and input above.
last problem.....

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 #  it prints to the screen as # and inserts #  in the db instead of #. I noticed that the nls  env variable is ORA_NLS=$ORACLE_HOME/ocommon/nls/admin/data

 rather than ORA_NLS3=$ORACLE_HOME/ocommon/nls/admin/data

, is that why we are having  problems entering the # sign? Or do I just need to change the nls_currency value from $ to #.

The nls_language and nls_territory is American is there a value for britain at all?

0
 
LVL 1

Expert Comment

by:Moondancer
ID: 7034784
Please update and finalize this old, open question.  Please:

1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.

EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.

Thanks,
Moondancer - EE Moderator

P.S.  Please click your Member Profile, expand View Question History to navigate and update all your open and locked questions.
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 7057607
Zero response from Asker or Experts in terms of closing recommendations, sorry to say.
Finalized today by Moondancer - EE Moderator
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now