Setting Environment Variables


Recall that on the Unix platform, NLS_LANG is an environment variable, and on Windows it is set globally in the Registry, although you can override it by setting the NLS_LANG local environment at your command prompt. NLS_LANG controls the languages used for Oracle messages, day and month names, the default date format, the decimal character and group separator, the local currency symbol, and the week start day.

Working with NLS_LANG

The format for setting the NLS_LANG environment variable is as follows:

 NLS_LANG=language_territory.characterset 

Or with the values filled in for each of the parameters:

 NLS_LANG=FRENCH_CANADA.WE8ISO8859P1 

The "language" portion of the language_territory overrides the default values of NLS_LANGUAGE and controls the same features that NLS_LANGUAGE controls (Oracle messages, sorting, day and month names, for example). If the language portion is not specified, it defaults to AMERICAN.

The territory portion of language_territory overrides the default value of NLS_TERRITORY and controls the same features that NLS_TERRITORY controls (default date, monetary, and numeric formats). If this value is not specified, it is derived from the language value.

The characterset portion of the example specifies the character-encoding scheme used by a client application. This application is typically running at the user's terminal. For every supported character set, a unique acronym is associated to it, and each language has a default character set associated to it.

You create the database with the CHARACTER SET <character set> parameter and the NATIONAL CHARACTER SET <national character set> parameters. Then on the client you can set the environment variables that allow for that client's session to take on the different characteristics based on the language of the user using that client.

NLS_LANG defines the client terminal's character encoding scheme. Different clients can use different encoding schemes. Data passed between the client and the database server is converted automatically between the two schemes. This conversion is completely transparent to the user and the client application.

Whenever the database character set and the client computer's character set are the same, Oracle simply assumes that whatever data is being sent from the client or received by the client will be the same character set; therefore, no validation or conversion is done with that data. This scenario has the distinct advantage of providing better performance. It also has the disadvantage of allowing data inconsistency in the database, which occurs when data from one character set is actually stored that is different from what the database understands is being sent from the client.

For example, if your database's character set is US7ASCII and you are using Russian or Chinese as the character set on your client computer by setting the NLS_LANG accordingly, but Oracle isn't aware of this difference, Oracle will treat every character sent as a single character and store it in a single byte in the database. All SQL string manipulation functions (for example, INSTR, SUBSTR, LENGTH, or TOCHAR) will be based on bytes (because Oracle assumes that one byte is one character) rather than on the characters. What's worse, all non-ASCII characters could either be lost or misrepresented. This is particularly true following an export/import operation where the data is taken as a stream of bits rather than any implicit interpretation taking place.

The ORA_NLS33 is an environment variable that points to a directory containing all the locale-specific data necessary for a multilingual database and is only strictly necessary if you are using a language other than English. If ORA_NLS33 is not defined, the default value of $ORACLE_HOME/ocommon/nls/admin/data or %ORACLE_HOME%\ocommon\nls\admin\data for Windows is used.

If the environment variable on the database's host computer ORA_NLS33 is set to an invalid directory, it is possible to inadvertently create a database that has only the default database character set, US7ASCII.

ORA_NLS33 should be set to $ORACLE_HOME/ocommon/nls/admin/data on Unix, or on Windows it should be set to %ORACLE_HOME%\ocommon\nls\admin\data. These are also the default settings if no explicit settings are made to the ORA_NLS33 environment variable.

Now we will look at how to control these variables with the ALTER SESSION command.

Using ALTER SESSION to Set Environment Variables

Because most of the language-dependent behavior can be altered at the session level, it is possible to alter these behaviors dynamically for any and every session that connects to the database. This is true for the environment variables and the way that they are interpreted and also for the initialization parameters.

This is accomplished through the use of the ALTER SESSION command. An example of resetting the date format parameter, NLS_DATE_FORAMT, to the value DD.MM.YYYY follows. The two commands are interchangeable:

 ALTER SESSION SET NLS_DATE_FORAMT = 'DD.MM.YYYY'; DBMS_SESSION.SET_NLS('NLS_DATE_FORMAT','''DD.MM.YYYY'''); 

SQL*Plus reads the values to which each of these environment variables is set when it starts and invisibly issues the corresponding ALTER SESSION command so that the user doesn't need to do anything special to simply use these customized settings.

Now we will look at some of the other features that Oracle provides for the support of National Language features.

Sorting with user queries is a big deal. When you have multiple languages in the query, these sorts become more complex.



    Oracle 9i Fundamentals I Exam Cram 2
    Oracle 9i Fundamentals I Exam Cram 2
    ISBN: 0789732653
    EAN: 2147483647
    Year: 2004
    Pages: 244
    Authors: April Wells

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net