ORACLE и параметр NLS_LANG

Установил Oracle Database Express Edition, но не могу найти где поменять настройку параметра NLS_LANG?

Re: ORACLE и параметр NLS_LANG

Ну а просто сказать — export NLS_LANG=.. . не пробовал?

Re: ORACLE и параметр NLS_LANG

Пытаюсь сделать connect к удаленной БД в SQL*Plus, а она выдает

SQL> connect system/oracle@192.168.15.147/fis8_1p
ERROR:
ORA-00604: ЮиШСЪР ЭР аХЪгабШТЭЮЬ SQL-гаЮТЭХ 1
ORA-12705: ГЪРЧРЭЮ ЭХТХаЭЮХ ШЫШ ЭХШЧТХбвЭЮХ
ЧЭРзХЭШХ ЯРаРЬХваР NLS

Re: ORACLE и параметр NLS_LANG

Re: ORACLE и параметр NLS_LANG

]$ env
SSH_AGENT_PID=8655
HOSTNAME=localhost.localdomain
TERM=xterm
SHELL=/bin/bash
HISTSIZE=1000
KDE_NO_IPV6=1
NLS_LANG=AMERICAN_AMERICA.CL8MSWIN1251
GTK_RC_FILES=/etc/gtk/gtkrc:/home/sash/.gtkrc-1.2-gnome2
WINDOWID=52431483
USER=SOZ
JRE_HOME=/usr/java/jdk1.5.0_11/jre
LS_COLORS=no=00:fi=00:di=00;34:ln=00;36:pi=40;33:so=00;35:bd=40;33;01:cd=40;33;0 1:or=01;05;37;41:mi=01;05;37;41:ex=00;32:*.cmd=00;32:*.exe=00;32:*.com=00;32:*.b tm=00;32:*.bat=00;32:*.sh=00;32:*.csh=00;32:*.tar=00;31:*.tgz=00;31:*.arj=00;31: *.taz=00;31:*.lzh=00;31:*.zip=00;31:*.z=00;31:*.Z=00;31:*.gz=00;31:*.bz2=00;31:* .bz=00;31:*.tz=00;31:*.rpm=00;31:*.cpio=00;31:*.jpg=00;35:*.gif=00;35:*.bmp=00;3 5:*.xbm=00;35:*.xpm=00;35:*.png=00;35:*.tif=00;35:
ORACLE_SID=XE
SSH_AUTH_SOCK=/tmp/ssh-EOXPom8598/agent.8598
GNOME_KEYRING_SOCKET=/tmp/keyring-cQwjS8/socket
KDEDIR=/usr
USERNAME=SOZ
SESSION_MANAGER=local/localhost.localdomain:/tmp/.ICE-unix/8598
PATH=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin:/usr/kerberos/bin:/ usr/local/bin:/usr/bin:/bin:/usr/X11R6/bin:/opt/pavcl/usr/bin:/home/sash/bin:/us r/bin
DESKTOP_SESSION=default
MAIL=/var/spool/mail/SOZ
JAVA_BINDIR=/usr/java/jdk1.5.0_11/bin
GDM_XSERVER_LOCATION=local
PWD=/home/sash
INPUTRC=/etc/inputrc
XMODIFIERS=@im=none
JAVA_HOME=/usr/java/jdk1.5.0_11
LANG=ru_RU.UTF-8
KDE_IS_PRELINKED=1
SDK_HOME=/usr/java/jdk1.5.0_11
GDMSESSION=default
SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass
SHLVL=3
HOME=/home/sash
JDK_HOME=/usr/java/jdk1.5.0_11
GNOME_DESKTOP_SESSION_ID=Default
LOGNAME=SOZ
DBUS_SESSION_BUS_ADDRESS=unix:abstract=/tmp/dbus-E6LXsJD1xA,guid=a89dfaab9234605 012ecbd004639aed1
LESSOPEN=|/usr/bin/lesspipe.sh %s
DISPLAY=:0.0
ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
G_BROKEN_FILENAMES=1
COLORTERM=gnome-terminal
XAUTHORITY=/tmp/.gdmEFMGRT
JAVA_ROOT=/usr/java/jdk1.5.0_11
_=/usr/bin/env
[SOZ@localhost

Re: ORACLE и параметр NLS_LANG

Re: ORACLE и параметр NLS_LANG

параметр LANG=ru_RU.UTF-8 не игоает роли, я его устанавливал как en_US.UTF-8. Это ничего не дало.
Если я стартую SQL*Plus через терминал (командную строку) то соединение проходит нормально.
Если стармую из меню выбором пункта меню «Run SQL Command Line» то получаю

SQL*Plus: Release 10.2.0.1.0 — Production on Пт Май 4 10:09:58 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn system/oracle@192.168.15.147/fis8_1p
ERROR:
ORA-00604: ЮиШСЪР ЭР аХЪгабШТЭЮЬ SQL-гаЮТЭХ 1
ORA-12705: ГЪРЧРЭЮ ЭХТХаЭЮХ ШЫШ ЭХШЧТХбвЭЮХ
ЧЭРзХЭШХ ЯРаРЬХваР NLS

Re: ORACLE и параметр NLS_LANG

Вы ответ на свой вопрос потрудитесь понять.
Выше уже отметили, что проблема в рассогласование внешних переменных
LANG и NLS_LANG
Переменная ORACLE NLS_LANG определяет какая кодировка установлена в среде функционирования КЛИЕНТА (в данном случае кодировка окружения в котором запускается sqlplus).
Если имеете в системе LANG=ru_RU.UTF-8 или LANG=en_US.UTF-8, то необходимро выставлять NLS_LANG=AMERICAN_CIS.UTF8 либо NLS_LANG=RUSSIAN_CIS.UTF8
В некоторых модулях доступа (например до php-oci8-4.3.9-3.22.4) есть ошибка приводящая к обрезанию значений текстовых полей при получении данных в случае когда сама база в однобайтовой кодировке а клиент в UTF8.

Re: ORACLE и параметр NLS_LANG

]$ echo $LANG
ru_RU.UTF-8
[SOZ@localhost

]$ echo $NLS_LANG
RUSSIAN_CIS.UTF8
[SOZ@localhost

SQL*Plus: Release 10.2.0.1.0 — Production on Пт Май 4 15:45:30 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn system/oracle@fis8_1p
ERROR:
ORA-00604: ЮиШСЪР ЭР аХЪгабШТЭЮЬ SQL-гаЮТЭХ 1
ORA-12705: ГЪРЧРЭЮ ЭХТХаЭЮХ ШЫШ ЭХШЧТХбвЭЮХ
ЧЭРзХЭШХ ЯРаРЬХваР NLS

Re: ORACLE и параметр NLS_LANG

Аосмотрел какой файл запускается из меню и запустил его из терминала

]$ sh /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/config/scripts/runsqlplus.sh
[SOZ@localhost

Результат тот-же т.е.

SQL*Plus: Release 10.2.0.1.0 — Production on Пт Май 4 15:28:07 2007

Copyright © 1982, 2005, Oracle. All rights reserved.

SQL> conn system/oracle@fis8_1p
ERROR:
ORA-00604: ЮиШСЪР ЭР аХЪгабШТЭЮЬ SQL-гаЮТЭХ 1
ORA-12705: ГЪРЧРЭЮ ЭХТХаЭЮХ ШЫШ ЭХШЧТХбвЭЮХ
ЧЭРзХЭШХ ЯРаРЬХваР NLS

А вот запускаю из терминала SQL*Plus

SQL*Plus: Release 10.2.0.1.0 — Production on Fri May 4 15:31:02 2007

Copyright © 1982, 2005, Oracle. All rights reserved.

Enter user-name: system
Enter password:

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 — Production

SQL> conn system/oracle@fis8_1p
Connected.
SQL>

Источник

Oracle set up default NLS_LANG

I am running Oracle database 11g on RHEL6. If is no client NLS_LANG set the length of some utf8 character is 2. After NLS_LANG=AMERICAN_AMERICA.UTF8 is set the length is only 1. How can bet the default NLG_LANG changed for whole database? I don’t want to change this value only for session, or like enviromental variable in linux. Is there any possibility to set it general for database?

Many thanks for any idea

2 Answers 2

This is what probably happens:

• Your client character set must be something like CP1252 or ISO-8859-15 whereas in fact your client really uses UTF8.
• In this character set (UTF8), the symbol á takes two bytes, so your client send these two bytes, while telling Oracle to treat these as CP1252. In CP1252 the two bytes code for two characters which result in the DB interpreting the input as two characters, therefore length(‘à’) equals 2 (and if you insert this string the result of the insert is not equals to à )
• When you correctly set the character set, the input is correctly treated by oracle as a single character, and its length is 1 (still two bytes).

Conclusion: set your client character set properly or you will get translation errors (you won’t get illegal characters this way but you may get strange symbols ( ¿ ).

The database character set is set at the time of creation and is generally changed via export/create blank database/import.

Since the database character set is AL32UTF8, when you set the client NLS_LANG to UTF8, you are telling Oracle to bypass the character set conversion that normally takes place when data is transmitted between the client and the server. That is extremely dangerous because it means that if the client sends data in any other encoding, there is a substantial risk that invalid data will be stored in the database. In this case, the LENGTH call that returns 1 is sending incorrectly encoded data to the database. Most likely, the client operating system represents data using something like the ISO-8859-1 character set where á is a single-byte character (binary value 0xE1). When the client sends the data to the database, it is sending the 0xE1 and telling the database «trust me, this is valid UTF-8 data». The database doesn’t bother to check the binary data to notice that 0xE1 is not a valid binary representation of any character in the UTF-8 character set. But now if some other client comes along that is correctly configured and asks for character set translation to take place and the database has a 0xE1 stored in some column, the character set conversion code will run, identify that 0xE1 is not a valid UTF-8 character, and return a replacement character (generally ‘?’) rather than á to the correctly configured client.

Since the database character set is UTF-8, you would expect that an ‘á’ character would have a length of 2. UTF-8 is a variable-width character set. Characters that are part of the US7ASCII character set are represented with 1 byte, most Western European characters such as the ‘á’ are represented using 2 bytes, and most Asian characters are represented using 3 bytes. There are a few rare characters that require 4 bytes.

Your NLS_LANG needs to be set to match the character set that your client system supports. It is rare that you’d have a client system that has native UTF-8 support. Since your client setting will override anything set at the database level, that means that each client needs to be configured to set an appropriate environment variable. There is a section in the NLS_LANG FAQ on how to determine the correct NLS_LANG setting for a Unix client.

Источник

2
Setting Up an NLS Environment

This chapter tells how to set up an NLS environment, and includes the following topics:

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:

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:

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:

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.

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

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:

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.

Table 2-1 Parameters and Their Priorities

Explicitly set in SQL functions

Set by an ALTER SESSION statement

Set as an environment variable

Specified in the initialization parameter file

Table 2-2 lists the NLS parameters available with the Oracle server.

Table 2-2 Parameters and their Scope

Calendar system

Gregorian

SQL Operator comparison

Credit accounting symbol

NLS_TERRITORY

Local currency symbol

NLS_TERRITORY

Date format

NLS_TERRITORY

Language for day and month names

NLS_LANGUAGE

Debit accounting symbol

NLS_TERRITORY

ISO international currency symbol

NLS_TERRITORY

NLS_LANG

Language, territory, character set

American_America.US7ASCII

Language

NLS_LANG

Character separating items in a list

NLS_TERRITORY

Monetary symbol for dollar and cents (or their equivalents)

NLS_TERRITORY

National character set

NLS_LANG

Decimal character and group separator

NLS_TERRITORY

Character Sort Sequence

NLS_LANGUAGE

Territory

NLS_LANG

Dual currency symbol

NLS_TERRITORY

Choosing a Locale with NLS_LANG

A locale is a linguistic and cultural environment in which a system or program is running. Setting the NLS_LANG parameter is the simplest way to specify locale behavior. It sets the language and territory used by the client application. It also sets the character set of the client, i.e., the character set of data entered or displayed by a client program.

The NLS_LANG parameter has three components ( language , territory , and charset ) in the form:

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

language

Specifies conventions such as the language used for Oracle messages, collation, 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. If language is not specified, the value defaults to American. For a complete list of languages, see Appendix A, «Locale Data».

territory

Specifies conventions such as the default date, monetary, and numeric formats. Each supported territory has a unique name; for example, America, France, or Canada. If territory is not specified, the value defaults from the language. For a complete list of territories, see Appendix A, «Locale Data».

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.

Parameter	Description	Default	Scope (I= INIT.ORA, E= Environment Variable, A= Alter Session)

The three arguments of NLS_LANG can be specified in many combinations, as in the following examples:

Note that illogical combinations could be set, but would not work properly. For example, the following tries to support Japanese by using a Western European character set:

Because WE8DEC does not support any Japanese characters, the result is that you would be unable to store Japanese data.

Specifying NLS_LANG

You can set NLS_LANG as an environment variable at the command line. For example, on UNIX, you could specify the value of NLS_LANG by entering the following line at the prompt:

NLS_LANG Examples

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

The following examples show how date and number formats are affected by NLS_LANG.

If NLS_LANG is set with the language as French, the territory as France, and the character set as Western European 8-bit ISO 8859-1, the same query returns:

Overriding Language and Territory Specifications

NLS_LANG sets the language and territory environment used by both the server session (for example, SQL command execution) and the client application (for example, display formatting in Oracle tools). Using this parameter ensures that the language environments of both database and client application are automatically the same.

The language and territory components of NLS_LANG set the default values for the other NLS parameters, such as date format, numeric characters, and collation. Each of these detailed parameters can be set in the client environment to fine-tune the language and territory defaults.

Note that NLS parameters in the client environment are ignored if NLS_LANG is not set.

If NLS_LANG is not set, the server session environment remains initialized with values of NLS_LANGUAGE, NLS_TERRRITORY, and other NLS instance parameters from the initialization parameter file. You can modify these parameters and restart the instance to change the defaults.

You might want to modify your NLS environment dynamically during the session. To do so, you can use NLS_LANGUAGE, NLS_TERRITORY and other NLS parameters in the ALTER SESSION statement.

The ALTER SESSION statement modifies only the session environment. The local client NLS environment is not modified, unless the client explicitly retrieves the new settings and modifies its local environment. SQL*Plus is an example of an application that does it; Oracle Developer is an example of an application that does not do this.

NLS Database Parameters

When a new database is created during the execution of CREATE DATABASE statement, the NLS database environment is established. The current NLS instance parameters, as defined by the initialization parameter file, are stored in the Data Dictionary along with the database and national character sets.

Checking NLS Parameters

You can find the values for NLS settings with some views and an OCI function call.

NLS Views

Applications can check the current session, instance and database NLS parameters by querying the following Data Dictionary views:

NLS_SESSION_PARAMETERS shows the current NLS parameters of the session querying the view.

NLS_INSTANCE_PARAMETERS shows the current NLS parameters of the instance, that is, NLS parameters read from the initialization file (INIT.ORA) at instance startup. The view shows only parameters that were explicitly set.

NLS_DATABASE_PARAMETERS shows the current NLS parameters of the database, including the database character set.

V$NLS_VALID_VALUES can be used to see which language, territory, linguistic and character set definitions are supported by the server.

See Oracle8i Reference for further details.

OCI Functions

To allow user applications to query client NLS settings Oracle8i OCI contains the OCINlsGetInfo function. See Chapter 5, «OCI Programming», for the description of this function.

Language and Territory Parameters

NLS_LANGUAGE and NLS_TERRITORY parameters are general NLS parameters describing NLS behavior of locale-dependent operations.

NLS_LANGUAGE

String Parameter scope:

Initialization Parameter and ALTER SESSION Default value:

Derived from NLS_LANG Range of values:

NLS_LANGUAGE specifies the default conventions for the following session characteristics:

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)

affirmative/negative response strings

The value specified for NLS_LANGUAGE in the initialization file is the default for all sessions in that instance.

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

In this case, the server message

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

For example, the file containing the server messages in French is called ORAF.MSB, with «F» being the language abbreviation for French.

Messages are stored in these files in one specific character set, depending on the language 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.

The following examples show behavior before and after setting NLS_LANGUAGE.

NLS_TERRITORY

String Parameter scope:

Initialization Parameter and ALTER SESSION Default value:

Derived from NLS_LANG Range of values:

NLS_TERRITORY specifies the conventions for the following default date and numeric formatting characteristics:

decimal character and group separator

local currency symbol

ISO currency symbol

dual currency symbol

credit and debit symbol

The value specified for NLS_TERRITORY in the initialization file is the default for the instance. For example, to specify the default as France, the parameter should be set as follows:

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

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

If NLS_LANG is specified in the client environment, the initialization file value is overridden already at the connection time.

The territory can be modified dynamically during the session by specifying the new NLS_TERRITORY value in an ALTER SESSION statement. Modification of NLS_ TERRITORY resets all derived NLS session parameters to default values for the new territory.

To change the territory dynamically to France, the following statement should be issued:

The following examples show behavior before and after setting NLS_TERRITORY.

Note that the symbol for currency units changed, but no monetary conversion calculations were performed. The numeric characters did not change because they were hardcoded by the SQL*Plus statement.

ALTER SESSION

The default values for language and territory can be overridden during a session by using the ALTER SESSION statement. For example:

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

Messages and Text

All messages and text should be in the same language. For example, when running an Oracle Developer application, messages and boilerplate text seen by the user originate from three sources:

messages from the server

messages and boilerplate text generated by Oracle Forms

messages and boilerplate text defined as part of the application

The application is responsible for meeting the last requirement. NLS takes care of the other two.

Time Parameters

Many different time formats are used throughout the world. Some typical ones are shown in Table 2-3.

Table 2-3 Time Parameters

hh24:mi:ss

hh24:mi:ss

hh24:mi:ss

hh24:mi:ss

hh:mi:ss am

Date Parameters

Oracle allows you to control how dates appear through the use of date parameters.

Date Formats

Many different date formats are used throughout the world. Some typical ones are shown in Table 2-4.

Table 2-4 Date Formats

dd.mm.yyyy

28.02.1998

NLS_DATE_FORMAT

String

Initialization Parameter, Environment Variable, and
ALTER SESSION

Default format for a particular territory

This parameter defines the default date format to use with the TO_CHAR and TO_DATE functions. The default value of this parameter is determined by NLS_TERRITORY. The value of this parameter can be any valid date format mask, and the value must be surrounded by quotation marks. For example:

To add string literals to the date format, enclose the string literal with double quotes. Note that every special character (such as the double quote) must be preceded with an escape character. The entire expression must be surrounded with single quotes. For example:

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

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

The value of this parameter is stored in the 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 format for the value of this parameter cannot exceed 24 bytes.

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.

Parameter type:

Parameter scope:

Default value:

Range of values:

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

Year 2000 Issues

Currently, the default date format for most territories specifies the year format as «RR» to indicate the last 2 digits. If your applications are Year 2000 compliant, you can safely specify the NLS_DATE_FORMAT using «YYYY» or «RRRR». If your applications are not yet Year 2000 compliant, you may wish to specify the NLS_DATE_FORMAT as «RR». The «RR» format will have the following effect: Given a year with 2 digits, RR will return a year in the next century if the year is less than 50 and the last 2 digits of the current year are greater than or equal to 50; return a year in the preceding century if the year is greater than or equal to 50 and the last 2 digits of the current year are less than 50.

See the Date Format Models section in the Oracle8i SQL Reference for full details on Date Format Elements.

Date Formats and Partition Bound Expressions

Partition bound expressions for a date column must specify a date using a format which requires that the month, day, and 4-digit year are fully specified. For example, the date format MM-DD-YYYY requires that the month, day, and 4-digit year are fully specified. In contrast, the date format DD-MON-YY (11-jan-97, for example) is invalid because it relies on the current date for the century.

Use TO_DATE() to specify a date format which requires the full specification of month, day, and 4-digit year. For example:

If the default date format, specified by NLS_DATE_FORMAT, of your session does not support specification of a date independent of current century (that is, if your default date format is MM-DD-YY for example), you must take one of the following actions:

Use TO_DATE() to express the date in a format that requires you to fully specify the day, month, and 4-digit year.

Change the value of NLS_DATE_FORMAT for the session to support the specification of dates in a format which requires you to fully specify the day, month, and 4-digit year.

For more information on using TO_DATE(), see Oracle8i SQL Reference .

NLS_DATE_LANGUAGE

String

Initialization Parameter, Environment Variable, and
ALTER SESSION

Derived from NLS_LANGUAGE

This parameter specifies the language for the spelling of day and month names by the functions TO_CHAR and TO_DATE, overriding that specified implicitly by NLS_LANGUAGE. NLS_DATE_LANGUAGE has the same syntax as the NLS_LANGUAGE parameter, and all supported languages are valid values. For example, to specify the date language as French, the parameter should be set as follows:

In this case, the query

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

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:

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:

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

Calendar Parameter

Oracle allows you to control calendar-related items through the use of parameters.

Calendar Formats

The type of calendar information stored for each territory is as follows:

First Day of the Week

Some cultures consider Sunday to be the first day of the week. Others consider Monday to be the first day of the week. A German calendar starts with Monday.

Table 2-5 First Day of the Week

First Calendar Week of the Year

Many countries, Germany, for example, use weeks for scheduling, planning, and bookkeeping. Oracle supports this convention.

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.

To support the ISO standard, a format element IW is provided that returns the ISO week number.

A typical example with four or more days in the first week is:

Table 2-6 Day of the Week Example 1

Parameter type:

Parameter scope:

Default value:

Range of values:

January		1998
Mo	Tu	We	Th	Fr	Sa	Su
1	2	3	4	Table 2-7 Day of the Week Example 2

January		1999
Mo	Tu	We	Th	Fr	Sa	Su
1	2	3	Number of Days and Months in a Year

Oracle supports six calendar systems, as well as the default Gregorian.

Japanese Imperial—uses the same number of months and days as Gregorian, but the year starts with the beginning of each Imperial Era.

ROC Official—uses the same number of months and days as Gregorian, but the year starts with the founding of the Republic of China.

Persian—has 12 months of equal length.

Thai Buddha—uses a Buddhist calendar.

Arabic Hijrah—has 12 months with 354 or 355 days.

English Hijrah—has 12 months with 354 or 355 days.

First Year of Era

The Islamic calendar starts from the year of the Hegira. The Japanese Imperial calendar starts from the beginning of an Emperor’s reign. For example, 1998 is the tenth year of the Heisei era. It should be noted, however, that the Gregorian system is also widely understood in Japan, so both 98 and Heisei 10 can be used to represent 1998.

NLS_CALENDAR

String

Initialization Parameter, Environment Variable, and ALTER SESSION

Gregorian

Many different calendar systems are in use throughout the world. NLS_CALENDAR specifies which calendar system Oracle uses.

NLS_CALENDAR can have one of the following values:

ROC Official (Republic of China)

For example, if NLS_CALENDAR is set to «Japanese Imperial», the date format is «E YY-MM-DD», and the date is May 15, 1997, then the SYSDATE is displayed as follows:

Numeric Parameters

Oracle allows you to control how numbers appear.

Numeric Formats

The database must know the number-formatting convention used in each session to interpret numeric strings correctly. For example, the database needs to know whether numbers are entered with a period or a comma as the decimal character (234.00 or 234,00). Similarly, the application needs to be able to display numeric information in the format expected at the client site.

Some typical ones are shown in Table 2-8.

Table 2-8 Numeric Formats

Parameter type:

Parameter scope:

Default value:

Range of values:

1 234 567,89

1.234.567,89

1,234,567.89

1,234,567.89

NLS_NUMERIC_CHARACTERS

Country	Example Numeric Formats

String

Initialization Parameter, Environment Variable, and
ALTER SESSION

Default decimal character and group separator for a particular territory

This parameter specifies the decimal character and grouping separator, overriding those defined implicitly by NLS_TERRITORY. The group separator is the character that separates integer groups (that is, the thousands, millions, billions, and so on). The decimal character separates the integer and decimal parts of a number.

Any character can be the decimal or group separator. The two characters specified must be single-byte, and both characters must be different from each other. The characters cannot be any numeric character or any of the following characters: plus (+), hyphen (-), less than sign ( ).

The characters are specified in the following format:

The grouping separator is the character returned by the number format mask G. For example, to set the decimal character to a comma and the grouping separator to a period, the parameter should be set as follows:

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

Note: SQL statements can include numbers represented as numeric or text literals. Numeric literals are not enclosed in quotes. They are part of the SQL language syntax and always use a dot as the decimal separator and never contain a group separator. Text literals are enclosed in single-quotes. They are implicitly or explicitly converted to numbers, if required, according to the current NLS settings. For example, in the following statement:

618 and 27.86 are numeric literals. The text literal ‘45,5’ is implicitly converted to the number 45.5 (assuming that WIDTH is a NUMBER column). The text literal ‘1.234’ is explicitly converted to a number 1234. This statement is valid only if NLS_NUMERIC_CHARACTERS is set to «,.».

You can alter the default value of NLS_NUMERIC_CHARACTERS in either of these ways:

Change the value of NLS_NUMERIC_CHARACTERS in the initialization file and then restart the instance.

Use the ALTER SESSION SET NLS_NUMERIC_CHARACTERS command to change the parameter’s value during a session.

Monetary Parameters

Oracle allows you to control how currency and financial symbols appear.

Currency Formats

Many different currency formats are used throughout the world. Some typical ones are shown in Table 2-9.

Table 2-9 Currency Format Examples

1 234,56 kr

1.234,56 DM

NLS_CURRENCY

Parameter type:

Parameter scope:

Default value:

Range of values:

String

Initialization Parameter, Environment Variable, and
ALTER SESSION

Default local currency symbol for a particular territory

This parameter specifies the character string returned by the number format mask L, the local currency symbol, overriding that defined implicitly by NLS_TERRITORY. For example, to set the local currency symbol to «Dfl » (including a space), the parameter should be set as follows:

In this case, the query

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

NLS_ISO_CURRENCY

Parameter type:

Parameter scope:

Default value:

Range of values:

String

Initialization Parameter, Environment Variable, and
ALTER SESSION

Derived from NLS_TERRITORY

This parameter specifies the character string returned by the number format mask C, the ISO currency symbol, overriding that defined implicitly by NLS_TERRITORY.

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:

In this case, the query

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

Typical ISO currency symbols are shown in Table 2-10.

Table 2-10 ISO Currency Examples

1 234 567,89 EEK

1.234.567,89 DEM

1,234,567.89 JPY

1,234,567.89 GBP

NLS_DUAL_CURRENCY

Parameter type:

Parameter scope:

Default value:

Range of values:

String

Initialization Parameter, Environment Variable, and
ALTER SESSION

Default dual currency symbol for a particular territory

You can use this parameter to override the default dual currency symbol defined in the territory. When starting a new session without setting NLS_DUAL_CURRENCY, you will use the default dual currency symbol defined in the territory of your current language environment. When you set NLS_DUAL_CURRENCY, you will start up a session with its value as the dual currency symbol.

NLS_DUAL_CURRENCY was introduced to help support the Euro. The following Table 2-11 lists the character sets that support the Euro symbol:

Table 2-11 Character Sets that Support the Euro Symbol

Parameter type:

Parameter scope:

Default value:

Range of values:

D8EBCDIC1141

EBCDIC Code Page 1141 8-bit Austrian German

DK8EBCDIC1142

EBCDIC Code Page 1142 8-bit Danish

S8EBCDIC1143

EBCDIC Code Page 1143 8-bit Swedish

I8EBCDIC1144

EBCDIC Code Page 1144 8-bit Italian

F8EBCDIC1147

EBCDIC Code Page 1147 8-bit French

WE8PC858

IBM-PC Code Page 858 8-bit West European

WE8ISO8859P15

ISO 8859-15 West European

EE8MSWIN1250

MS Windows Code Page 1250 8-bit East European

CL8MSWIN1251

MS Windows Code Page 1251 8-bit Latin/Cyrillic

WE8MSWIN1252

MS Windows Code Page 1252 8-bit West European

EL8MSWIN1253

MS Windows Code Page 1253 8-bit Latin/Greek

WE8EBCDIC1140

EBCDIC Code Page 1140 8-bit West European

WE8EBCDIC1140C

EBCDIC Code Page 1140 Client 8-bit West European

WE8EBCDIC1145

EBCDIC Code Page 1145 8-bit West European

WE8EBCDIC1146

EBCDIC Code Page 1146 8-bit West European

WE8EBCDIC1148

EBCDIC Code Page 1148 8-bit West European

WE8EBCDIC1148C

EBCDIC Code Page 1148 Client 8-bit West European

EL8ISO8859P7

ISO 8859-7 Latin/Greek

IW8MSWIN1255

MS Windows Code Page 1255 8-bit Latin/Hebrew

AR8MSWIN1256

MS Windows Code Page 1256 8-Bit Latin/Arabic

TR8MSWIN1254

MS Windows Code Page 1254 8-bit Turkish

BLT8MSWIN1257

MS Windows Code Page 1257 Baltic

VN8MSWIN1258

MS Windows Code Page 1258 8-bit Vietnamese

TH8TISASCII

Thai Industrial 520-2533 — ASCII 8-bit

AL24UTFFSS

Unicode 1.1 UTF-8 Universal character set

Unicode 2.1 UTF-8 Universal character set

UTF-EBCDIC encoding of Unicode 2.1

NLS_MONETARY_CHARACTERS

String

Environment Variable

Derived from NLS_TERRITORY

NLS_MONETARY_CHARACTERS specifies 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 ( ).

NLS_CREDIT

Name	Description	Euro Code Value
Parameter scope:
Default value:
Range of values:

String

Environment Variable

Derived from NLS_TERRITORY

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

This parameter can be specified only in the client environment. It can be retrieved through the OCIGetNlsInfo function.

NLS_DEBIT

Parameter type:

Parameter scope:

Default value:

Range of values:

String

Environment Variable

Derived from NLS_TERRITORY

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

This parameter can be specified only in the client environment. It can be retrieved through the OCIGetNlsInfo function.

Collation Parameters

Oracle allows you to choose how data is sorted through the use of collation parameters.

Sorting Order

Different languages have different sort orders. What’s more, different cultures or countries using the same alphabets may sort words differently. For example, the German language sharp s (ß) is sorted differently in Germany and Austria. The linguistic sort sequence German sorts this sequence as the two characters (SS), while the linguistic sort sequence Austrian sorts it as (SZ). Another example is the treatment of ö , o , and oe . They are sorted differently throughout the various Germanic languages.

Oracle provides many different types of sort, but achieving a linguistically correct sort frequently harms performance. This is a trade-off the database administrator needs to make on a case-by-case basis. A typical case would be when sorting Spanish. In traditional Spanish, ch and ll are distinct characters, which means that the correct order would be: cerveza , colorado , cheremoya , lago , luna , llama . But a true linguistic sort will cause some performance degradation.

Sorting East Asian languages is difficult and complex. At present, Oracle typically relies on the binary order of the particular encoded character set for sorting East Asian Languages.

Sorting Character Data

Conventionally, when character data is sorted, the sort sequence is based on the numeric values of the characters defined by the character encoding scheme. Such a sort is called a binary sort. Such a sort produces reasonable results for the English alphabet because the ASCII and EBCDIC standards define the letters A to Z in ascending numeric value.

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

When characters used in other languages are present, a binary sort generally does not produce reasonable results. For example, an ascending ORDER BY query would return the character strings ABC, ABZ, BCD, ÄBC, in that sequence, when the Ä has a higher numeric value than B in the character encoding scheme.

Linguistic Sorts

To produce a sort sequence that matches the alphabetic sequence of characters for a particular language, another sort technique must be used that sorts characters independently of their numeric values in the character encoding scheme. This technique is called a linguistic sort. A linguistic sort operates by replacing characters with other binary values that reflect the character’s proper linguistic order so that a sort returns the desired result.

The Oracle 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 «Linguistic Definitions» .

Warning: Linguistic sorting is not supported on Asian multi-byte character sets. If the database character set is multi-byte, you will get binary sorting, which makes the sort sequence dependent on the character set specification. There are two exceptions to this rule: Japanese Hiragana/Katakana and the UTF8 character set. This means that the Japanese Yomi sort is only possible by creating an extra column using the Hiragana or Katakana reading for the kanji and sorting on that column.

Linguistic Indexes

You can create a function-based index that uses languages other than English. The index does not change the linguistic sort order determined by NLS_SORT. The index just improves the performance. A simple example is:

returns the result much faster than without an index.

For more information, see the description of function-based indexes in Oracle8i Concepts .

Multiple Linguistic Indexes

If you store character data of multiple languages into one database, you may want to create multiple linguistic indexes for one column. This approach improves the performance of the linguistic sort for a specific column for multiple languages and is a powerful feature for multilingual databases.

An example of creating multiple linguistic indexes is:

When session variable NLS_SORT is set to FRENCH, french_index can be used and when it is set to GERMAN, german_index can be used.

Requirements for Linguistic Indexes

If you want to use a single linguistic index or multiple linguistic indexes, there are some requirements to be met in order for the linguistic index to be used. The first requirement is that QUERY_REWRITE_ENABLED needs to be true. This is not a specific requirement for linguistic indexes, but for all function-based indexes. Here is an example of setting QUERY_REWRITE_ENABLED.

The second requirement, which is specific to linguistic indexes, is that

NLS_COMP needs to be ANSI. There are various ways to set NLS_COMP.

Here is an example.

The third requirement is that NLS_SORT needs to indicate the linguistic definition you want to use for the linguistic sort. If you want a FRENCH linguistic sort order, NLS_SORT needs to be FRENCH. If you want a GERMAN linguistic sort order, NLS_SORT needs to be GERMAN. There are various ways to set NLS_SORT. The below is an example. Although the example below uses the ALTER SESSION statement, it’s probably better for you to set NLS_SORT as a client environment variable so that you can use the same SQL statements for all languages and different linguistic indexes can be picked up based on NLS_SORT being set in the client environment.

The fourth requirement is that you need to use the cost-based optimizer. Function-based indexes do not get picked up by the rule-based optimizer. Function-based indexes are used only by the cost-based optimizer.

The last thing is that you need to specify «WHERE NLSSORT(column_name) IS NOT NULL» when you want to use «ORDER BY column_name» and the «column_name» is the column with the linguistic index. This is necessary only when you use «ORDER BY». See the example below.

The below is an example of using a FRENCH linguistic index. For NLS_SORT, you may want to set it in the client environment variable instead of the ALTER SESSION statement (as described above).

For more information, see the description of function-based indexes in Oracle8i Concepts .

Case-Insensitive Search

You can create a function-based index which improves the performance of case-insensitive searches. For example:

For more information, see the description of function-based indexes in Oracle8i Application Developer’s Guide — Fundamentals .

Linguistic Special Cases

Linguistic special cases are character sequences that need to be treated as a single character when sorting. Such special cases are handled automatically when using a linguistic sort. For example, one of the linguistic sort sequences for Spanish specifies that the double characters ch and ll are sorted as single characters appearing between c and d and between l and m respectively.

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. S uch 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.)

NLS_SORT

String

Initialization Parameter, Environment Variable, and
ALTER SESSION

Default character sort sequence for a particular language

This parameter specifies the type of sort for character data, overriding that defined implicitly by NLS_LANGUAGE.

The syntax of NLS_SORT is:

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:

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

Note: When the NLS_SORT parameter is set to BINARY, the optimizer can, in some cases, satisfy the ORDER BY clause without doing a sort (by choosing an index scan). But when NLS_SORT is set to a linguistic sort, a sort is always needed to satisfy the ORDER BY clause if the linguistic index does not exist for the linguistic sort order specified by NLS_SORT. If the linguistic index exists for the linguistic sort order specified by NLS_SORT, the optimizer can, in some cases, satisfy the ORDER BY clause without doing a sort (by choosing an index scan).

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.

A complete list of linguistic definitions is provided in Table A-8, «Linguistic Definitions».

NLS_COMP

String

Initialization Parameter, Environment Variable and ALTER SESSION

Binary

You can use this parameter to avoid the cumbersome process of using NLS_SORT in SQL statements. Normally, comparison in the WHERE clause is binary. To use linguistic comparison, the NLSSORT function must be used. Sometimes this can be tedious, especially when the linguistic sort needed has already been specified in the NLS_SORT session parameter. You can use NLS_COMP in such cases to indicate that the comparisons must be linguistic according to the NLS_SORT session parameter. This is done by altering the session:

To specify that comparison in the WHERE clause is always binary, issue

As a final note, when NLS_COMP is set to ANSI, a linguistic index improves the performance of the linguistic comparison.

To enable a linguistic index, use the syntax:

NLS_LIST_SEPARATOR

Parameter type:

Parameter scope:

Default value:

Range of values:

Parameter scope:

Default value:

Range of values:

Parameter scope:

Default value:

Range of values:

String

Environment Variable

Derived from NLS_TERRITORY

NLS_LIST_SEPARATOR specifies 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 ( ), period (.).

Character Set Parameters

You can specify the character set used for the client.

NLS_NCHAR

Parameter type:

Parameter scope:

Default value:

Range of values:

String

Environment Variable

Derived from NLS_LANG

NLS_NCHAR specifies the character set used by the client application for national character set data (NCHAR, NVARCHAR2, NCLOB). If it is not specified, the client application uses the same character set that it uses for the database character set data.

Источник

Parameter type:

Parameter scope:

Default value:

Range of values: