12 SQL*Plus Globalization Support

Globalization support enables the storing, processing and retrieval of data in native languages. The languages that can be stored in an Oracle database are encoded by Oracle Database-supported character sets. Globalization support ensures that database utilities, error messages, sort order, and date, time, monetary, numeric, and calendar conventions adjust to the native language and locale.

Topics:

Configuring Globalization Support in Command-line SQL*Plus
Configuring Multiple Language Support in iSQL*Plus
NLS_LANG Environment Variable

For more information on globalization support, see the Oracle Technology Network globalization notes at http://otn.oracle.com/tech/globalization/, and see the Oracle Database Globalization Support Guide

Configuring Globalization Support in Command-line SQL*Plus

SQL*Plus supports multiple languages through the NLS_LANG environment variable. To display another language in SQL*Plus, before starting SQL*Plus you must configure:

NLS_LANG in the SQL*Plus client environment.
The Oracle Database during installation.

SQL*Plus Client

The SQL*Plus client environment is configured by setting the NLS_LANG environment variable which is read by SQL*Plus at startup.

Oracle Database

The Oracle Database environment is configured by creating the database with the required character set.

Configuring Multiple Language Support in iSQL*Plus

iSQL*Plus supports multiple languages through the Unicode UTF-8 character encoding in the web browser you use for the iSQL*Plus session, and through the AL32UTF8 encoding (Oracle's implementation of Unicode) in the iSQL*Plus Application Server serving the session.

If your Oracle Database also uses AL32UTF8, then there is a one-for-one correspondence between the character sets in the database and in iSQL*Plus. Otherwise some character mapping may occur.

Web Browser

No changes are necessary to have multilingual support in your iSQL*Plus web browser. However, your browser must support UTF-8 character encoding. Most current web browsers support UTF-8.

The character encoding in the iSQL*Plus browser is set to UTF-8 by the charset attribute in the header of HTML pages returned from the iSQL*Plus Application Server.

Application Server

The language and territory values of the NLS_LANG environment variable in effect when the iSQL*Plus Application Server starts specifies behavior for the iSQL*Plus Application Server. The character set value is ignored.

The language and territory values set for an application server are used for all iSQL*Plus sessions run from that application server. However, you can use an ALTER SESSION command to change the language and territory used for the duration of the current session. For example, to use Chinese language and conventions in your current session, enter

ALTER SESSION SET NLS_LANGUAGE='SIMPLIFIED CHINESE';
ALTER SESSION SET NLS_TERRITORY='CHINA';

New pages served to your iSQL*Plus browser are now displayed with error messages and text fields in Chinese. Information is now displayed according to Chinese convention. This is a temporary change for the current login in the current iSQL*Plus session. To check what settings are currently in effect, enter:

SELECT * FROM NLS_SESSION_PARAMETERS;

The NLS_TERRITORY and NLS_LANGUAGE values correspond to the language and territory components of the NLS_LANG variable.

NLS_LANG Environment Variable

The NLS_LANG environment variable has three components, each controlling a subset of the globalization features.

Your operating system and keyboard must be able to support the character set you have chosen. You may need to install additional support software. For more information about NLS_LANG, and software support, see the Oracle Database Globalization Support Guide.

Setting up locale specific behavior on the SQL*Plus client is achieved with the use of NLS parameters. These parameters may be specified in a number of ways, including as an initialization parameter on the server. For settings that control the behavior of the server, see the Oracle Database Globalization Support Guide.

NLS_LANG has the syntax:

NLS_LANG = language_territory.charset

where language specifies the conventions to use for Oracle Database messages, sorting order, day and month names. For example, to receive messages in Japanese, set language to JAPANESE. If language is not set, it defaults to AMERICAN.

where territory specifies the convention for default dates, and for monetary, and numeric formats. For example to use the Japanese territory format, set territory to JAPAN. If territory is not set, the default value is derived from the language value, and so is set to AMERICA.

where, in SQL*Plus command-line, charset specifies the character set encoding used by SQL*Plus for data processing, and is generally suited to that of the users terminal. Illogical combinations can be set, but will not work. For example, Japanese cannot be supported using a Western European character set such as:

NLS_LANG=JAPANESE_JAPAN.WE8DEC

However, Japanese could be supported with the Unicode character set. For example:

NLS_LANG=JAPANESE_JAPAN.UTF8

charset is not used by the iSQL*Plus Application Server. The iSQL*Plus Application Server always uses AL32UTF8 character encoding, Oracle's implementation of Unicode. It cannot be changed.

Viewing NLS_LANG Settings

You can view the NLS_LANG setting by entering the SELECT command:

SELECT * FROM NLS_SESSION_PARAMETERS;

The NLS_TERRITORY and NLS_LANGUAGE values correspond to the language and territory components of the NLS_LANG variable.

You can also obtain a list of valid values for the NLS_SORT, NLS_LANGUAGE, NLS_TERRITORY and NLS_CHARACTERSET by querying the NLS dynamic performance view table V$NLS_VALID_VALUES.

Setting NLS_LANG

You can set the NLS_LANG environment variable to control globalization features.

Example 12-1 Configuring Japanese Support in SQL*Plus on Windows

Ensure you have exited your current SQL*Plus session.
Open System from Start > Settings > Control Panel.
Click the Advanced tab and select Environment Variables.
Create a new environment variable, NLS_LANG, with a value of Japanese_Japan.UTF8.
You may need to restart Windows for this setting to take effect.

Example 12-2 Configuring Japanese Support in SQL*Plus on UNIX

Ensure you have exited your current SQL*Plus session.
Set the NLS_LANG variable using either set or setenv depending on the UNIX shell you are using. For example, in csh, you would enter:
```
setenv NLS_LANG Japanese_Japan.UTF8
```

Example 12-3 Configuring Japanese Support in Oracle Database

To store data in the Japanese character set using UTF-8 character encoding, ensure that the Oracle database has been created with the UTF8 character set. See your Oracle Database Installation Guide for information about creating your database in a character set other than US7ASCII.