11 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.


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

The iSQL*Plus user interface can be displayed in a number of languages:

  • Chinese (China) [zh-cn]

  • Chinese (Taiwan) [zh-tw]

  • French (France) [fr]

  • German (Germany) [de]

  • Italian (Italy) [it]

  • Japanese [ja]

  • Korean [ko]

  • Portuguese (Brazil) [pt-br]

  • Spanish (International/Traditional Sort) [es]

The iSQL*Plus user interface is automatically displayed in the language specified as your preferred language in your web browser.

To display the iSQL*Plus user interface in a language other than your existing web browser's preferred language, add the language to your web browser, then select the language to use. For example, to display the iSQL*Plus user interface in Japanese in Internet Explorer 6.0:

  1. Start Internet Explorer.

  2. Select Tools > Internet Options > Languages... > Add.

  3. Select Japanese [ja] and click OK.

  4. Select Japanese [ja] and click Move Up until it is at the top of the list.

  5. Click OK.

  6. Click OK.

  7. Enter the iSQL*Plus URL. The iSQL*Plus user interface is now displayed in Japanese.

Your web browser must support UTF-8 character encoding. Most current web browsers support UTF-8.

Application Server

The language used for the output from iSQL*Plus queries is dependent on the language and territory values of the NLS_LANG environment variable in effect when the iSQL*Plus Application Server starts. The charset parameter is ignored and is always UTF8. The language and territory values 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


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:


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:


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


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:


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 11-1 Configuring Japanese Support in SQL*Plus on Windows

  1. Ensure you have exited your current SQL*Plus session.

  2. Open System from Start > Settings > Control Panel.

  3. Click the Advanced tab and select Environment Variables.

  4. Create a new environment variable, NLS_LANG, with a value of Japanese_Japan.JA16SJIS.

  5. You may need to restart Windows for this setting to take effect.

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

  1. Ensure you have exited your current SQL*Plus session.

  2. 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


    setenv NLS_LANG Japanese_Japan.JA16SJIS


    setenv NLS_LANG Japanese_Japan.JA16EUC

    The locale setting of your UNIX terminal determines the exact value of the NLS_LANG parameter. For more information on the NLS_LANG setting, see the Oracle Database Globalization Support Guide.

Example 11-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 AL32UTF8 character set. See your Oracle Database Installation Guide for information about creating your database in a character set other than US7ASCII.