Oracle7 Server Reference Manual

Library

Product

Contents

Index

National Language Support

• What Does National Language Support Provide?
• Oracle7 Server NLS Architecture
• Background Information
• Specifying Language-Dependent Behavior
• Specifying Language-Dependent Behavior for a Session
• Specifying Language-Dependent Application Behavior
• Specifying Default Language-Dependent Behavior
• Runtime Loadable NLS Data
• NLS Parameters
• Specifying Character Sets
• Date and Number Formats
• Additional NLS Environment Variables
• Using NLS Parameters in SQL Functions
• Obsolete NLS Data
• Unicode (UTF2) Support
• NLS Data
• Calendar Systems

• What Does National Language Support (NLS) Provide?

• Oracle7 Server NLS Architecture

• Background Information

• Specifying Language-Dependent Behavior

• Specifying Language-Dependent Behavior for a Session

• Specifying Language-Dependent Application Behavior

• Specifying Default Language-Dependent Behavior

• Runtime Loadable NLS Data

• NLS Parameters

• Specifying Character Sets

• Data and Number Formats

• Additional NLS Environment Variables

• Using NLS Parameters in SQL Functions

• Obsolete NLS Data

• UNICODE (UTF2) Support

• NLS Data

• Calendar Systems

What Does National Language Support Provide?

To achieve these goals, NLS provides

• support for processing data in the various character encoding schemes used by computer hardware

• both single-byte and multi-byte character encoding schemes

• client and server can use different character encoding schemes in client/server environments, with transparent conversion of data between them

• language-dependent operation of end-user applications

• Oracle7 Server messages displayed in multiple languages

• dates and numbers formatted using language and territory conventions

• character data sorted according to alphabetic conventions

• language-dependent operation specifiable for each session

Oracle7 Server NLS Architecture

Because the language-dependent data is separate from the code, the operation of NLS functions is governed by data supplied at runtime. New languages can be added and language-specific application characteristics can be altered without requiring any code changes. This architecture also enables language-dependent features to be specified for each session.

Background Information

Character Encoding Schemes

Encoding schemes define the representation of alphabetic characters, numerals, and punctuation characters, together with codes that control terminal display and communication. A character encoding scheme (also known as a character set or code page) specifies numbers corresponding to each character that the terminal can display. Examples are 7-bit ASCII, EBCDIC Code Page 500, and Japanese Extended UNIX Code.

Many encoding schemes are used by hardware manufacturers to support different languages. All support the 26 letters of the Latin alphabet, A to Z. In general, single-byte encoding schemes are used for European languages and multi-byte encoding schemes for Asian languages.

Single-Byte 7-Bit Encoding Schemes

Single-Byte 8-Bit Encoding Schemes

Multi-Byte Encoding Schemes

There are two general groups of encoding schemes, those based on 7-bit ASCII and those based on IBM EBCDIC. Within each group, all schemes normally use the same encoding for the 26 Latin characters (A to Z), but use different encoding for other characters used in languages other than English. ASCII and EBCDIC use different encodings, even for the Latin characters.

National Language Support Enhancements

UTF2 Encoding

Arabic/Hebrew Display Character Set Support

Specifying Language-Dependent Behavior

Most NLS parameters can be used in three ways

• As initialization parameters to specify language-dependent behavior defaults for the server.

For example, in your INIT.ORA file, include

		NLS_TERRITORY = FRANCE

• As environment variables on client machines to specify language-dependant behavior defaults for a session. These defaults override the defaults set for the server.

For example, on a UNIX system

		setenv NLS_TERRITORY FRANCE

• As ALTER SESSION parameters to change the language-dependent behavior of a session. These parameters override the defaults set for the session or for the server.

For example:

		ALTER SESSION SET NLS_TERRITORY = FRANCE

The following NLS parameters can be initialization parameters, environment variables, and ALTER SESSION parameters:

• NLS_CALENDAR

• NLS_CURRENCY

• NLS_DATE_FORMAT

• NLS_DATE_LANGUAGE

• NLS_ISO_CURENCY

• NLS_NUMERIC_CHARACTERS

• NLS_SORT

• NLS_LANGUAGE

• NLS_TERRITORY

The following NLS parameters can only be set as environment variables:

• NLS_CREDIT

• NLS_DEBIT

• NLS_DISPLAY

• NLS_LANG

• NLS_LIST_SEPARATOR

• NLS_MONETARY_CHARACTERS

For more information on these parameters, see "NLS Parameters".

For additional information on NLS_DISPLAY, see "NLS_TERRITORY Parameter" .

For additional information on NLS_LANG, see "Specifying Language-Dependent Behavior for a Session" .

Specifying Language-Dependent Behavior for a Session

NLS_LANG Environment Variable

NLS_LANG = language_territory.charset

Each component controls the operation of a subset of NLS features.

language

Specifies conventions such as the language used for Oracle messages, day names, and month names. Each supported language has a unique name; for example, American, French, or German. The language argument specifies default values for the territory and character set arguments, so either (or both) territory or charset can be omitted. For a complete list of languages, see "Supported Languages" .

territory

Specifies conventions such as the default date format and decimal character used for numbers. Each supported territory has a unique name; for example, America, France, or Canada. For a complete list of territories, see "Supported Territories" .

charset

Specifies the character set used by the client application (normally that of the user's terminal). Each supported character set has a unique acronym, for example, US7ASCII, WEISO8859P1, WE8DEC, WE8EBCDIC500, or JA16EUC. Each language has a default character set associated with it. Default values for the languages available on your system are listed in your installation or user's guide. For a complete list of character sets, see "Storage Character Sets" and "Arabic/Hebrew Display Character Sets" .

Note: All components of the NLS_LANG definition are optional; any item left out will default. If you specify territory or charset, you must include the preceding delimiter [underscore ( _ ) for territory, period ( . ) for charset], otherwise the value will be parsed as a language name.

The three arguments of NLS_LANG can be specified in any combination, as in the following examples:

NLS_LANG = AMERICAN_AMERICA.US7ASCII

or

NLS_LANG = FRENCH_FRANCE.ISO8859P1

or

NLS_LANG = FRENCH_CANADA.WE8DEC

or

NLS_LANG = JAPANESE_JAPAN.JA16EUC

Specifying NLS_LANG

For example, on VMS you could specify the value of NLS_LANG by entering the following line at the VMS prompt:

$ DEFINE NLS_LANG FRENCH_FRANCE.WE8DEC

If you do not specify a value for NLS_LANG, the language_dependent behavior defaults to the language specified by the NLS_LANGUAGE database initialization parameter, the territory specified by the NLS-TERRITORY database initialization parameter, and the character set with which the database was created.

For more information on how to set NLS_LANG on your system, see your operating system-specific Oracle documentation.

Client/Server Architecture

Because NLS_LANG is an environment variable, it is read by the client application at startup time. The client communicates the information defined in NLS_LANG to the server when it connects.

Overriding Language and Territory Specifications

ALTER SESSION SET NLS_LANGUAGE = FRENCH NLS_TERRITORY = FRANCE

This feature implicitly determines the language environment of the database for each session. An ALTER SESSION statement is automatically executed when a session connects to a database to set the values of the database parameters NLS_LANGUAGE and NLS_TERRITORY to those specified by the language and territory arguments of NLS_LANG. If NLS_LANG is not defined, no ALTER SESSION statement is executed.

When NLS_LANG is defined, the implicit ALTER SESSION is executed for all instances to which the session connects, for both direct and indirect connections. If the values of NLS parameters are changed explicitly with ALTER SESSION during a session, the changes are propagated to all instances to which that user session is connected.

Specifying Language-Dependent Application Behavior

Language-Dependent Functions

• language to use for messages and boilerplate text

• number format

• date format

• currency format

• starting day of the week

Messages and Text

• messages from the Server

• messages and boilerplate text generated by SQL*Forms

• messages and boilerplate text defined as part of the application

Number Format

Date Format, Currency Symbols, and First Day of the Week

Sorting Character Data

Note however, that in the ASCII standard all uppercase letters appear before any lowercase letters. In the EBCDIC standard, the opposite is true: all lowercase letters appear before any uppercase letters.

Binary Sorts

Linguistic Sorts

Oracle7 Server provides both sort mechanisms. Linguistic sort sequences are defined as part of language-dependent data. Each linguistic sort sequence has a unique name. NLS parameters define the sort mechanism for ORDER BY queries. A default value can be specified, and this value can be overridden for each session with the NLS_SORT parameter. A complete list of linguistic definitions is provided in the "Linguistic Definitions" table .

Warning: Linguistic sorting is not supported on multi-byte character sets. If the database character set is multi-byte, you get binary sorting, which makes the sort sequence dependent on the character set specification.

Linguistic Special Cases

Another example is the German language sharp s (ß). The linguistic sort sequence German can sort this sequence as the two characters SS, while the linguistic sort sequence Austrian sorts it as SZ.

Special cases like these are also handled when converting uppercase characters to lowercase, and vice versa. For example, in German the uppercase of the sharp s is the two characters SS. Such case-conversion issues are handled by the NLS_UPPER, NLS_LOWER, and NLS_INITCAP functions, according to the conventions established by the linguistic sort sequence. (The standard functions UPPER, LOWER, and INITCAP do not handle these special cases.)

Specifying Default Language-Dependent Behavior

NLS_LANGUAGE Parameter

• language for Server messages

• language for day and month names and their abbreviations (specified in the SQL functions TO_CHAR and TO_DATE)

• symbols for equivalents of AM, PM, AD, and BC

• default sorting sequence for character data when ORDER BY is specified (GROUP BY uses a binary sort, unless ORDER BY is specified)

For more information on which language conventions supported, see your operating system-specific Oracle documentation.

For example, to specify the default session language as French, the parameter should be set as follows:

NLS_LANGUAGE = FRENCH

In this case, the Server message

ORA-00942: table or view does not exist

will appear as

ORA-00942: table ou vue n'existe pas

Messages used by the Server are stored in binary-format files that are placed in the ora_rdbms directory, or the equivalent.

Multiple versions of these files can exist, one for each supported language, using the filename convention

<product_id><language_id>.MSB

For example, the file containing the Server messages in French is called ORAF.MSB, "F" being the language abbreviation for French.

Messages are stored in these files in one specific character set, depending on the particular machine and operating system. If this is different from the database character set, message text is automatically converted to the database character set. If necessary, it will be further converted to the client character set if it is different from the database character set. Hence, messages will be displayed correctly at the user's terminal, subject to the limitations of character set conversion.

The default value of NLS_LANGUAGE may be operating system specific. You can alter the NLS_LANGUAGE parameter by changing the value in the initialization file and then restarting the instance.

For more information on the default value, see your operating system-specific Oracle documentation.

NLS_TERRITORY Parameter

• date format

• decimal character and group separator

• local currency symbol

• ISO currency symbol

• week start day

NLS_TERRITORY = FRANCE

In this case, numbers would be formatted using a comma as the decimal character.

The default value of NLS_TERRITORY may be operating system specific.

You can alter the NLS_TERRITORY parameter by changing the value in the initialization file and then restarting the instance.

For more information on the default value and to see which territory conventions are supported on your system, see your operating system-specific Oracle documentation.

Runtime Loadable NLS Data

Data Loading

On initialization, the boot file is loaded into memory, where it serves as the master list of available NLS objects, prior to loading NLSDATA files. Oracle supports both system and user boot files. A user boot file may only contain a subset of the system boot file. When loading, the user boot file takes precedence over the system one. If the user boot file is not present, the system boot file will be used; this way, all NLS data defined in the system boot file will be available for loading. If neither user nor system boot file is found, then a default linked-in boot file and some default linked-in data objects (language American, territory America, character set US7ASCII) will be loaded. NLS functionality, however, will be limited to what is provided by the linked-in data objects. After a boot file (either user or system) is loaded, the NLSDATA files are read into memory based on the availability of the NLS objects defined in the boot file.

The idea behind a user boot file is to give an application further flexibility to tailor exactly which NLS objects it needs for its language environment, thus controlling the application's memory consumption.

Utilities

NLS Data Installation Utility	Generate binary-format data objects from their text-format versions. Use this when you receive NLS data updates or if you create your own data objects.
NLS Configuration Utility (LXBCNF)	Create and edit user boot files.

For more information, see Oracle7 Server Utilities.

NLS Parameters

Parameter	Description
NLS_CALENDAR	Calendar system
NLS_CURRENCY	Local currency symbol
NLS_DATE_FORMAT	Default date format
NLS_DATE_LANGUAGE	Default language for dates
NLS_ISO_CURRENCY	ISO international currency symbol
NLS_LANGUAGE	Default language
NLS_NUMERIC_CHARACTERS	Decimal character and group separator
NLS_SORT	Character sort sequence
NLS_SPECIAL_CHARS
NLS_TERRITORY	Default territory

For a complete description of ALTER SESSION, see Oracle7 Server SQL Reference.

NLS_CALENDAR

NLS_CALENDAR can have one of the following values:

• Arabic Hijrah

• Gregorian

• Japanese Imperial

• Persian

• ROC Official

• Thai Buddha

SELECT SYSDATE FROM DUAL;

SYSDATE

--------

07-02-17 

NLS_CURRENCY

NLS_CURRENCY = "Dfl "

In this case, the query

SELECT TO_CHAR(TOTAL, 'L099G999D99') "TOTAL"

   FROM ORDERS WHERE CUSTNO = 586

would return

TOTAL

-------------

Dfl 12.673,49

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

For a complete description of ALTER SESSION, see Oracle7 Server SQL Reference.

NLS_DATE_FORMAT

NLS_DATE_FORMAT = "MM/DD/YYYY"

As another example, to set the default date format to display Roman numerals for months, you would include the following line in your initialization file:

NLS_DATE_FORMAT = "DD RM YY"

With such a default date format, the following SELECT statement would return the month using Roman numerals (assuming today's date is February 13, 1991):

SELECT TO_CHAR(SYSDATE) CURRDATE

   FROM DUAL;

CURRDATE

---------

13 II 91

The value of this parameter is stored in the tokenized internal date format. Each format element occupies two bytes, and each string occupies the number of bytes in the string plus a terminator byte. Also, the entire format mask has a two-byte terminator. For example, "MM/DD/YY" occupies 12 bytes internally because there are three format elements, two one-byte strings (the two slashes), and the two-byte terminator for the format mask. The tokenized format for the value of this parameter cannot exceed 24 bytes.

Note: The applications you design may need to allow for a variable-length default date format. Also, the parameter value must be surrounded by double quotes: single quotes are interpreted as part of the format mask.

You can alter the default value of NLS_DATE_FORMAT by changing its value in the initialization file and then restarting the instance, and you can alter the value during a session using an ALTER SESSION SET NLS_DATE_FORMAT command.

For a complete description of ALTER SESSION, see Oracle7 Server SQL Reference.

NLS_DATE_ LANGUAGE

NLS_DATE_LANGUAGE = FRENCH

In this case, the query

SELECT TO_CHAR(SYSDATE, 'Day:Dd Month yyyy')

   FROM DUAL;

would return

Mercredi:13 Février 1991

Month and day name abbreviations are also in the language specified, for example:

Me:13 Fév 1991

The default date format also uses the language-specific month name abbreviations. For example, if the default date format is DD-MON-YYYY, the above date would be inserted using:

INSERT INTO tablename VALUES ('13-Fév-1991');

The abbreviations for AM, PM, AD, and BC are also returned in the language specified by NLS_DATE_LANGUAGE. Note that numbers spelled using the TO_CHAR function always use English spellings; for example:

SELECT TO_CHAR(TO_DATE('27-Fév-91'),'Day: ddspth Month')

   FROM DUAL;

would return:

Mercredi: twenty-seventh Février

You can alter the default value of NLS_DATE_LANGUAGE by changing its value in the initialization file and then restarting the instance, and you can alter the value during a session using an ALTER SESSION SET NLS_DATE_LANGUAGE command.

For a complete description of ALTER SESSION, see Oracle7 Server SQL Reference.

NLS_ISO_CURRENCY

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

SELECT TO_CHAR(TOTAL, 'C099G999D99') "TOTAL"

   FROM ORDERS WHERE CUSTNO = 586

would return

TOTAL

-------------

 FRF12.673,49

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

For a complete description of ALTER SESSION, see Oracle7 Server SQL Reference.

NLS_NUMERIC_ CHARACTERS

NLS_NUMERIC_CHARACTERS = ",."

Both characters are single byte and must be different. Either can be a space.

Note: When the decimal character is not a period (.) or when a group separator is used, numbers appearing in SQL statements must be enclosed in quotes. For example:

        INSERT INTO SIZES (ITEMID, WIDTH, QUANTITY)

          VALUES (618, '45,5', TO_NUMBER('1.234','9G999'));

You can alter the default value of NLS_NUMERIC_CHARACTERS 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_DATE_LANGUAGE command.

For a complete description of ALTER SESSION, see Oracle7 Server SQL Reference.

NLS_SORT

The syntax of NLS_SORT is:

NLS_SORT = { BINARY | name }

BINARY specifies a binary sort and name specifies a particular linguistic sort sequence. For example, to specify the linguistic sort sequence called German, the parameter should be set as follows:

NLS_SORT = German

The name given to a linguistic sort sequence has no direct connection to language names. Usually, however, each supported language will have an appropriate linguistic sort sequence defined that uses the same name.

Note: Setting the NLS_SORT initialization parameter to BINARY causes a sort to use a full table scan, regardless of the path the optimizer chooses.

You can alter the default value of NLS_SORT 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_SORT command.

For a complete description of ALTER SESSION, see Oracle7 Server SQL Reference.

A complete list of linguistic definitions is provided in the "Linguistic Definitions" table .

Specifying Character Sets

Once the database is created, the database character set cannot be changed without re-creating the database. Hence, it is important to consider carefully which character set to use. The database character set should always be a superset or equivalent of the operating system's native character set. The character sets used by client applications that access the database will usually determine which superset is the best choice.

If all client applications use the same character set, then this is the normal choice for the database character set. When client applications use different character sets, the database character set should be a superset (or equivalent) of all the client character sets. This will ensure that every character is represented when converting from a client character set to the database character set.

When a client application operates with a terminal that uses a different character set, then the client application's characters must be converted to the database character set, and vice versa. This conversion is performed automatically, and is transparent to the client application. The character set used by the client application is defined by the NLS_LANG parameter.

Supported Character Sets

Character Set Conversion

Replacement characters may be defined for specific characters as part of a character set definition. Where a specific replacement character is not defined, a default replacement character is used. To avoid the use of replacement characters when converting from client to database character set, the latter should be a superset (or equivalent) of all the client character sets.

The Concatenation Operator

On the user side, a 7-bit replacement character set can be used if the database character set is the same or compatible, that is, if both character sets replace the vertical bar with the same national character.

Storing Data in Multi-Byte Character Sets

If the database character set is single byte, the number of characters and number of bytes will be the same. If the database character set is multi-byte, there will in general be no such correspondence. A character may consist of one or more bytes, depending on the specific multi-byte encoding scheme and whether shift-in/shift-out control codes are present. Hence, column widths must be chosen with care to allow for the maximum possible number of bytes for a given number of characters.

Loadable Character Sets

Date and Number Formats

Note: The TO_NUMBER function also accepts a format mask.

Date Formats

Note that the MON and DY format masks explicitly support month and day abbreviations that may not be three characters in length. For example, the abbreviations "Lu" and "Ma" can be specified for the French "Lundi" and "Mardi", respectively.

Week and Day Number Conventions

To support the ISO standard, a format element IW is provided that returns the ISO week number. In addition, format elements I IY IYY and IYYY, equivalent in behavior to the format elements Y YY YYY YYYY, return the year relating to the ISO week number.

In the ISO standard, the year relating to an ISO week number can be different from the calendar year. For example 1st Jan 1988 is in ISO week number 53 of 1987. A week always starts on a Monday and ends on a Sunday.

• If January 1 falls on a Friday, Saturday, or Sunday, then the week including January 1 is the last week of the previous year, because most of the days in the week belong to the previous year.

• If January 1 falls on a Monday, Tuesday, Wednesday, or Thursday, then the week is the first week of the new year, because most of the days in the week belong to the new year.

Number Formats

• D (Decimal) returns the decimal character

• G (Group) returns the group separator

• L (Local currency) returns the local currency symbol

• C (international Currency) returns the international currency symbol

• RN (Roman Numeral) returns the number as its Roman numeral equivalent

For a more information on using date masks, see Oracle7 Server SQL Reference.

Additional NLS Environment Variables

• NLS_DATE_FORMAT

• NLS_DATE_LANGUAGE

• NLS_SORT

• NLS_NUMERIC_CHARACTERS

• NLS_CURRENCY

• NLS_ISO_CURRENCY

• NLS_CALENDAR

The following is an example for a UNIX environment:

setenv NLS_DATE_FORMAT "dd/mon/yy"

For more information, see the Oracle7 Server Administrator's Guide.

Client-Only Environment Variables

• NLS_CREDIT

• NLS_DEBIT

• NLS_DISPLAY

• NLS_LANG

• NLS_LIST_SEPARATOR

• NLS_MONETARY_CHARACTERS

NLS_CREDIT

Default value:

derived from NLS_TERRITORY

Range of values:

any string, maximum of 9 bytes (not including null)

NLS_CREDIT sets the symbol that displays a credit in financial reports. The default value of this parameter is determined by NLS_TERRITORY.

NLS_DEBIT

Default value:

derived from NLS_TERRITORY

Range of values:

any string, maximum of 9 bytes (not including null)

NLS_DEBIT sets the symbol that displays a debit in financial reports. The default value of this parameter is determined by NLS_TERRITORY.

NLS_DISPLAY

Default value:

none

Range of values:

any valid string

NLS_DISPLAY sets the client-side display environment. It is only applicable to Hebrew and Arabic languages. For a list of valid character sets, see page 4 - 38.

Value is a string of the form locale_direction.characterset, where locale is any string up to 20 bytes (not including null) containing only the characters [A-Z, a-z, 0-9 -], direction is either RTL or LTR (case- insensitive), and characterset specifies a valid display character set.

Specification of locale and direction is optional. If omitted, locale will default to an empty string, and direction will default to LTR. The characterset option must be specified. If you specify direction or characterset, you must include the preceding delimeter [underscore ( _ ) for direction, period ( . ) for characterset], otherwise the value will be parsed as a language name.

NLS_LIST_SEPARATOR

Default value:

derived from NLS_TERRITORY

Range of values:

any valid character

Defines the character to use to separate values in a list of values.

The character specified must be single-byte and cannot be the same as either the numeric or monetary decimal character, any numeric character, or any of the following characters: plus (+), hyphen (-), less than sign (<), greater than sign (>), period (.).

NLS_MONETARY_CHARACTERS

Default value:

derived from NLS_TERRITORY

Defines 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 (>).

Using NLS Parameters in SQL Functions

All SQL functions whose behavior depends on NLS conventions allow NLS parameters to be specified. These functions are

• TO_CHAR

• TO_DATE

• TO_NUMBER

• NLS_UPPER

• NLS_LOWER

• NLS_INITCAP

• NLSSORT

For example, the following query is only evaluated correctly if the language being for dates is American:

SELECT ENAME FROM EMP
WHERE HIREDATE > '1-JAN-91'

Such a query can be made independent of the current date language using:

SELECT ENAME FROM EMP
WHERE HIREDATE > TO_DATE('1-JAN-91','DD-MON-YY',
   'NLS_DATE_LANGUAGE = AMERICAN')

In this way, language-independent SQL statements can be defined where necessary. For example, such statements may be necessary when string literals appear in SQL statements in views, CHECK constraints, or triggers.

Default Specifications

Specifying Parameters

'parameter = value'

The following NLS parameters may be specified:

• NLS_DATE_LANGUAGE

• NLS_NUMERIC_CHARACTERS

• NLS_CURRENCY

• NLS_ISO_CURRENCY

• NLS_SORT

• TO_DATE: NLS_DATE_LANGUAGE

• TO_NUMBER: NLS_NUMERIC_CHARACTERS, NLS_CURRENCY, NLS_ISO_CURRENCY

• TO_CHAR: NLS_DATE_LANGUAGE, NLS_NUMERIC_CHARACTERS, NLS_CURRENCY, NLS_ISO_CURRENCY

• NLS_UPPER: NLS_SORT

• NLS_LOWER: NLS_SORT

• NLS_INITCAP: NLS_SORT

• NLSSORT : NLS_SORT

TO_DATE ('1-JAN-89', 'DD-MON-YY',
   'nls_date_language = American')

TO_CHAR (hiredate, 'DD/MON/YYYY',
   'nls_date_language = French')


TO_NUMBER ('13.000,00', '99G999D99',
   'nls_numeric_characters = ''.,''')

TO_CHAR (sal, '9G999D99L', 'nls_numeric_characters = ''.,''
   nls_currency = ''Dfl ''')

TO_CHAR (sal, '9G999D99C', 'nls_numeric_characters = '',.''
   nls_iso_currency = Japan')
NLS_UPPER (ename, 'nls_sort = Austrian')

NLSSORT (ename, 'nls_sort = German')

Note: For some languages, various lowercase characters correspond to a sequence of uppercase characters, or vice versa. As a result, the output from NLS_UPPER, NLS_LOWER, and NLS_INITCAP can differ from the length of the input.

Unacceptable Parameters

If an NLS parameter is specified in TO_CHAR, TO_NUMBER, or TO_DATE, a format mask must also be specified as the second parameter. For example, the following specification is legal:

TO_CHAR (hiredate, 'DD/MON/YY', 'nls_date_language = French')

These are illegal:

TO_CHAR (hiredate, 'nls_date_language = French')
TO_CHAR (hiredate, 'nls_date_language = French',
   'DD/MON/YY')

This restriction means that a date format must always be specified if an NLS parameter is in a TO_CHAR or TO_DATE function. As a result, NLS_DATE_FORMAT is not a valid NLS parameter for these functions.

CONVERT Function

For more information on CONVERT, see Oracle7 Server SQL Reference.

The CONVERT function converts the binary representation of a character string in one character set to another. It uses exactly the same technique as described previously for the conversion between database and client character sets. Hence, it uses replacement characters and has the same limitations. The syntax for CONVERT is:

where src_char_set is the source and dest_char_set is the destination character set.

NLSSORT Function

String Comparisons in a WHERE Clause

SELECT COL1 FROM TAB1 WHERE COL1 > 'B'

returns both BCD and ÄBC because Ä has a higher numeric value than B. However, in German, an Ä is sorted alphabetically before B. Such conventions are language dependent even when the same character is used. In Swedish, an Ä is sorted after Z. Linguistic comparisons can be made using NLSSORT in the WHERE clause, as follows:

WHERE NLSSORT(col) comparison_operator NLSSORT(comparison_string)

Note that NLSSORT has to be on both sides of the comparison operator. For example:

SELECT COL1 FROM TAB1 WHERE NLSSORT(COL1) > NLSSORT('B')

If a German linguistic sort is being used, this does not return strings beginning with Ä because in the German alphabet Ä comes before B. If a Swedish linguistic sort is being used, such names are returned because in the Swedish alphabet Ä comes after Z.

Controlling an ORDER BY Clause

In other words, the NLSSORT linguistic replacement is only applied once, not twice. The NLSSORT function is generally not needed in an ORDER BY clause when the default sort mechanism is a linguistic sort. However, when the default sort mechanism is BINARY, then a query such as:

SELECT ENAME FROM EMP
ORDER BY ENAME

will use a binary sort. A German linguistic sort can be obtained using:

SELECT ENAME FROM EMP
ORDER BY NLSSORT(ENAME, 'NLS_SORT = GERMAN')

Obsolete NLS Data

Old Name	New Name
AR8MSAWIN	AR8MSWIN1256
JVMS	JA16VMS
JEUC	JA16EUC
SJIS	JA16SJIS
JDBCS	JA16DBCS
KSC5601	KO16KSC5601
KDBCS	KO16DBCS
CGB2312-80	ZHS16CGB231280
CNS 11643-86	ZHT32EUC
ZHT32CNS1164386	ZHT32EUC
TSTSET2	JA16TSTSET2
TSTSET	JA16TSTSET

Table 4 - 1. New Names for Obsolete NLS Data Character Sets

Character set CL8MSWINDOW31 has been de-supported. The newer character set CL8MSWIN1251 is actually a duplicate of CL8MSWINDOW31 and includes some characters omitted from the earlier version. Change any usage of CL8MSWINDOW31 to CL8MSWIN1251 instead.

Unicode (UTF2) Support

The character set name for UTF2 is AL24UTFFSS. Conversion between UTF2 and other existing character sets is provided in this release of Oracle Server. Conversion between UTF2 and single-byte character sets is done through an internal number matching mechanism; conversion between UTF2 and multi-byte character sets is done with conversion functions and tables.

Clients should be aware that AL24UTFFSS is now officially supported as a new character set. Since the encoding scheme of UTF2 is very similar to some existing character sets, no major impact on existing products is expected.

NLS Data

Supported Languages

Supported Territories

Storage Character Sets

Name	Description
US7ASCII	ASCII 7-bit American
WE8DEC	DEC 8-bit West European
WE8HP	HP LaserJet 8-bit West European
US8PC437	IBM-PC Code Page 437 8-bit American
WE8EBCDIC37	EBCDIC Code Page 37 8-bit West European
WE8EBCDIC500	EBCDIC Code Page 500 8-bit West European
WE8PC850	IBM-PC Code Page 850 8-bit West European
D7DEC	DEC VT 100 7-bit German
F7DEC	DEC VT 100 7-bit French
S7DEC	DEC VT100 7-bit Swedish
E7DEC	DEC VT100 7-bit Spanish
SF7ASCII	ASCII 7-bit Finnish
NDK7DEC	DEC VT100 7-bit Norwegian/Danish
I7DEC	DEC VT100 7-bit Italian
NL7DEC	DEC VT100 7-bit Dutch
CH7DEC	DEC VT100 7-bit Swiss (German/French)
YUG7ASCII	ASCII 7-bit Yugoslavian
SF7DEC	DEC VT 100 7-bit Finnish
TR7DEC	DEC VT100 7-bit Turkish
WE8ISO8859P1	ISO 8859-1 West European
EE8ISO8859P2	ISO 8859-2 East European
SE8ISO8859P3	ISO 8859-3 South European
NEE8ISO8859P4	ISO 8859-4 North and North-East European
CL8ISO8859P5	ISO 8859-5 Latin/Cyrillic
AR8ISO8859P6	ISO 8859-6 Latin/Arabic
EL8ISO8859P7	ISO 8859-7 Latin/Greek
IW8ISO8859P8	ISO 8859-8 Latin/Hebrew
WE8ISO8859P9	ISO 8859-9 West European & Turkish
NE8ISO8859P10	ISO 8859-10 North European
TH8TISASCII	Thai Industrial Standard 620-2533 - ASCII 8-bit
TH8TISEBCDIC	Thai Industrial Standard 620-2533 - EBCDIC 8-bit
AR8EBCDICX	EBCDIC XBASIC 8-bit Latin/Arabic
EL8DEC	DEC 8-bit Latin/Greek
TR8DEC	DEC 8-bit Turkish
WE8EBCDIC37C	EBCDIC Code Page 37 8-bit Oracle/c
RU8PC866	IBM-PC Code Page 866 8-bit Latin/Cyrillic
WE8EBCDIC500C	EBCDIC Code Page 500 8-bit Oracle/c
EEC8EUROPA3	EEC EUROPA3 8-bit West European/Greek
EE8PC852	IBM-PC Code Page 852 8-bit East European
RU8BESTA	BESTA 8-bit Latin/Cyrillic
RU8PC855	IBM-PC Code Page 855 8-bit Latin/Cyrillic
TR8PC857	IBM-PC Code Page 857 8-bit Turkish
CL8MACCYRILLIC	Mac Client 8-bit Latin/Cyrillic
CL8MACCYRILLICS	Mac Server 8-bit Latin/Cyrillic
WE8PC860	IBM-PC Code Page 860 8-bit West European
IS8PC861	IBM-PC Code Page 861 8-bit Icelandic
EE8MACCES	Mac Server 8-bit Central European
EE8MACCROATIANS	Mac Server 8-bit Croatian
TR8MACTURKISHS	Mac Server 8-bit Turkish
IS 8MACICELANDICS	Mac Server 8-bit Icelandic
EL8MACGREEKS	Mac Server 8-bit Greek
EE8MSWIN 1250	MS Windows Code Page 1250 8-bit East European
CL8MSWIN1251	MS Windows Code Page 1251 8-bit Latin/Cyrillic
F8EBCDIC297	EBCDIC Code Page 297 8-bit French
BG8MSWIN	MS Windows 8-bit Bulgarian Cyrillic
EL8MSWIN1253	MS Windows Code Page 1253 8-bit Latin/Greek
D8EBCDIC273	EBCDIC Code Page 273/18-bit Austrian German
I8EBCDIC280	EBCDIC Code Page 280/18-bit Italian
DK8EBCDIC277	EBCDIC Code Page 277/18-bit Danish
S8EBCDIC278	EBCDIC Code Page 278/18-bit Swedish
EE8EBCDIC870	EBCDIC Code Page 870 8-bit East European
CL8EBCDIC1025	EBCDIC Code Page 1025 8-bit Cyrillic
N8PC865	IBM-PC Code Page 865 8-bit Norwegian
F7SIEMENS9780X	Siemens 97801/97808 7-bit French
E7SIEMENS9780X	Siemens 97801/97808 7-bit Spanish
S7SIEMENS9780X	Siemens 97801/97808 7-bit Swedish
DK7SIEMENS9780X	Siemens 97801/97808 7-bit Danish
N7SIEMENS9780X	Siemens 97801/97808 7-bit Norwegian
I7SIEMENS9780X	Siemens 97801/97808 7-bit Italian
D7SIEMENS9780X	Siemens 97801/97808 7-bit German
WE8GCOS7	Bull EBCDIC GCOS7 8-bit West European
US8BS2000	Siemens 9750-62 EBCDIC 8-bit American
D8BS2000	Siemens 9750-62 EBCDIC 8-bit German
F8BS2000	Siemens 9750-62 EBCDIC 8-bit French
E8BS2000	Siemens 9750-62 EBCDIC 8-bit Spanish
DK8BS2000 S	Siemens 9750-62 EBCDIC 8-bit Danish
WE8BS2000	Siemens EBCDIC.DF.04 8-bit West European
CL8BS2000	Siemens EBCDIC.EHC.LC 8-bit Cyrillic
WE8BS2000L5	Siemens EBCDIC.DF.O4.L5 8-bit West European/Turkish
WE8DG	DG 8-bit West European
WE8NCR4970	NCR 4970 8-bit West European
WE8ROMAN8	HP Roman8 8-bit West European
EE8MACCE	Mac Client 8-bit Central European
EE8MACCROATIAN	Mac Client 8-bit Croatian
TR8MACTURKISH	Mac Client 8-bit Turkish
IS8MACICELANDIC	Mac Client 8-bit Icelandic
EL8MACGREEK	Mac Client 8-bit Greek
US8ICL	ICL EBCDIC 8-bit American
WE8ICL	ICL EBCDIC 8-bit West European
WE8MACROMAN8	Mac Client 8-bit Extended Roman8 West European
WE8MACROMAN8S	Mac Server 8-bit Extended Roman8 West European
TH8MACTHAI	Mac Client 8-bit Latin/Thai
TH8MACTHAIS	Mac Server 8-bit Latin/Thai
HU8CWI2	Hungarian 8-bit CWI-2
TR8ISO8859P9	Turkish version ISO 8859-9 West European & Turkish
EL8PC437S	IBM-PC Code Page 437 8-bit (Greek modification)
EL8EBCDIC875	EBCDIC Code Page 875 8-bit Greek
EL8PC737	IBM-PC Code Page 737 8-bit Greek/Latin
LT8PC772	IBM-PC Code Page 772 8-bit Lithuanian (Latin/Cyrillic)
LT8PC774	IBM-PCCode Page 774 8-bit Lithuanian (Latin)
CDN8PC863	IBM-PC Code Page 863 8-bit Canadian French
AR8ASMO8X	ASMO Extended 708 8-bit Latin/Arabic
AR8NAFITHA711	Nafitha Enhanced 711 Server 8-bit Latin/Arabic
AR8SAKHR707	SAKHR 707 Server 8-bit Latin/Arabic
AR8MUSSAD768	Mussa'd Alarabi/2 768 Server 8-bit Latin/Arabic
AR8ADOS710	Arabic MS-DOS 710 Server 8-bit Latin/Arabic
AR8ADOS720	Arabic MS-DOS 720 Server 8-bit Latin/Arabic
AR8APTEC715	APTEC 715 Server 8-bit Latin/Arabic
AR8MSWIN1256	MS Windows Code Page 1256 8-Bit Latin/Arabic
AR8NAFITHA721	Nafitha International 721 Server 8-bit Latin/Arabic
AR8SAKHR706	SAKHR 706 Server 8-bit Latin/Arabic
AR8ARABICMAC	Mac Client 8-bit Latin/Arabic
AR8ARABICMACS	Mac Server 8-bit Latin/Arabic
JA16VMS	JVMS 16-bit Japanese
JA16EUC	EUC 16-bit Japanese
JA16SJIS	Shift-JIS 16-bit Japanese
JA16DBCS	IBM DBCS 16-bit Japanese
JA16HP	HP 16-bit Japanese
JA16EBCDIC930	IBM DBCS Code Page 290 16-bit Japanese
JA16TOSHIBAEUC	Toshiba EUC 16-bit Japanese
KO16KSC5601	KSC5601 16-bit Korean
KO16DBCS	IBM DBCS 16-bit Korean
ZHS16CGB231280	CGB2312-80 16-bit Simplified Chinese
ZHT32EUC	EUC 32-bit Traditional Chinese
ZHT32SOPS	SOPS 32-bit Traditional Chinese
ZHT16DBT	Taiwan Taxation 16-bit Traditional Chinese
ZHT32TRIS	TRIS 32-bit Traditional Chinese
ZHT16BIG5	BIG5 16-bit Traditional Chinese
AL24UTFFSS	Unicode UTF-FSS
JA16TSTSET2	ASCII-based 16-bit Test Character Set
JA16TSTSET	Shift-sensitive ASCII-based Test Character Set

Table 4 - 2. (continued) Storage Character Sets

Arabic/Hebrew Display Character Sets

Name	Description
AR8ASMO708PLUS	ASMO 708 Plus 8-bit Latin/Arabic
AR7ASMO449PLUS	ASMO 449 Plus 7-bit Latin/Arabic
AR7AMEER	Ameer 7-bit Latin/Arabic
AR8XBASIC	XBASIC Right-to-Left Arabic Character Set
AR8NAFITHA711T	Nafitha Enhanced 711 Client 8-bit Latin/Arabic
AR8SAKHR707T	SAKHR 707 Client 8-bit Latin/Arabic
AR8MUSSAD768T	Mussa'd Alarabi/2 768 Client 8-bit Latin/Arabic
AR8ADOS710T	Arabic MS-DOS 710 Client 8-bit Latin/Arabic
AR8ADOS720T	Arabic MS-DOS 720 Client 8-bit Latin/Arabic
AR8APTEC715T	APTEC 7 15 Client 8-bit Latin/Arabic
AR8NAFITHA721T	Nafitha International 721 Client 8-bit Latin/Arabic
AR7SEDCOT	SEDCO/ESPRIT/DATA GENERAL 7-bit Latin/Arabic
AR8HPARABIC8T	HP ARABIC8 8-bit Latin/Arabic

Table 4 - 3. Arabic/Hebrew Display Character Sets

Linguistic Definitions

Name	Extended Name
WEST_EUROPEAN	XWEST_EUROPEAN
GERMAN	XGERMAN
DANISH	XDANISH
SPANISH	XSPANISH
GERMAN DIN	XGERMAN_DIN
FINNISH
FRENCH
NORWEGIAN
SWEDISH
ITALIAN
ICELANDIC
DUTCH	XDUTCH
SWISS	XSWISS
ARABIC
HUNGARIAN	XHUNGARIAN
GREEK
CZECH	XCZECH
POLISH
SLOVAK	XSLOVAK
LATIN
THAI_DICTIONARY
THAI_TELEPHONE
TURKISH	XTURKISH
RUSSIAN
HEBREW
LITHUANIAN
CROATIAN	XCROATIAN
ROMANIAN
BULGARIAN
CATALAN	XCATALAN
SLOVENIAN	XSLOVENIAN

Table 4 - 4. (continued) Linguistic Definitions

Calendar Systems

Name	Character Set Texts	Default Format
Japanese Imperial	JA16EUC	EEYY"\307\257"MM"\267\356"DD"\306\374"
ROC Official	ZHT32EUC	EEyy"\310\241 "mm"\305\314"dd"\305\312"
Thai Buddha	TH8TISASCII	"\307\321\27 1\267\325\350" dd month EE yyyy
Persian	AR8ASMO8X	DD Month YYYY
Arabic Hijrah	AR8ISO8859P6	DD Month YYYY

Table 4 - 5. NLS Supported Calendars

Prev Next

Copyright © 1996 Oracle Corporation. All Rights Reserved.

Library

Product

Contents

Index