Release 1 (9.0.1)
Part Number A90236-02
 Home |
 Book List |
 Contents |
 Index |  Master Index |  Feedback |
| Oracle9i Globalization Support Guide Release 1 (9.0.1) Part Number A90236-02 |
|
This chapter tells how to set up a globalization support environment. It includes the following topics:
NLS parameters determine the locale-specific behavior on both the client and the server. NLS parameters can be specified in the following ways:
NLS_TERRITORY = "CZECH REPUBLIC"
In addition, NLS_LENGTH_SEMANTICS and NLS_CONV_EXCP can be set using the ALTER SYSTEM statement.
% setenv NLS_SORT FRENCH
ALTER SESSION statement can be used to override the default values that are set for the session in the initialization parameter file or set by the client with environment variables.
ALTER SESSION SET NLS_SORT = FRENCH;
ALTER SESSION statement. For example:
TO_CHAR(hiredate, 'DD/MON/YYYY', 'nls_date_language = FRENCH')
The database character set and the national character set are specified in the CREATE DATABASE statement.
Table 3-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. Explicitly setting an NLS parameter within a SQL function overrides all other settings -- default, initialization parameter, environment variable, and ALTER SESSION parameters.
| Highest Priority | |
|
1 |
Explicitly set in SQL functions |
|
2 |
Set by an |
|
3 |
Set as an environment variable |
|
4 |
Specified in the initialization parameter file |
|
5 |
Default |
|
|
Lowest Priority |
Table 3-2 lists the NLS parameters available with the Oracle server.
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, which is the character set of data entered or displayed by a client program.
The NLS_LANG parameter has three components (language, territory, and character set) in the form:
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, sorting, day names, and month names. Each supported language has a unique name; for example, |
|
territory |
Specifies conventions such as the default date, monetary, and numeric formats. Each supported territory has a unique name; for example, |
|
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, |
The three arguments of NLS_LANG can be specified in many combinations, as in the following examples:
NLS_LANG = AMERICAN_AMERICA.US7ASCII
or
NLS_LANG = FRENCH_CANADA.WE8DEC
or
NLS_LANG = JAPANESE_JAPAN.JA16EUC
Note that illogical combinations can be set but will not work properly. For example, the following specification tries to support Japanese by using a Western European character set:
NLS_LANG = JAPANESE_JAPAN.WE8DEC
Because WE8DEC does not support any Japanese characters, you would be unable to store Japanese data.
You can set NLS_LANG as an environment variable at the command line. For example, on UNIX, you can specify the value of NLS_LANG by entering a statement similar to the following:
% setenv NLS_LANG FRENCH_FRANCE.WE8DEC
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 the NLS_LANG parameter.
% setenv NLS_LANG American_America.WE8ISO8859P1 SQL> SELECT ename, hiredate, ROUND(sal/12,2) sal FROM emp; ENAME HIREDATE SAL ------- --------- ------- Clark 09-DEC-88 4195.83 Miller 23-MAR-92 4366.67 Strauß 01-APR-95 3795.87
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, then the same query returns the following information:
% setenv NLS_LANG French_France.WE8ISO8859P1 SQL> SELECT ename, hiredate, ROUND(sal/12,2) sal FROM emp; ENAME HIREDATE SAL --------- --------- ------- Clark 09/12/88 4195,83 Miller 23/03/92 4366,67 Strauß 01/04/95 3795,87
The NLS_LANG parameter 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 the NLS_LANG parameter set the default values for the other NLS parameters, such as date format, numeric characters, and linguistic sorting. Each of these detailed parameters can be set in the client environment to customize the language and territory values.
Note that NLS parameters in the client environment are ignored if NLS_LANG is not set.
If the NLS_LANG parameter 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 retrieves new settings; Oracle Developer is an example of an application that does not retrieve new settings.
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.
You can find the values for NLS parameters in some views or by using an OCI function call.
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 parameter file 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.
User applications can query client NLS settings with the OCINlsGetInfo function.
NLS_LANGUAGE and NLS_TERRITORY parameters are general NLS parameters that describe NLS behavior of locale-dependent operations.
| Parameter type: |
String |
| Parameter scope: |
Initialization Parameter and |
| Default value: |
Derived from |
| Range of values: |
Any valid language name |
NLS_LANGUAGE specifies the default conventions for the following session characteristics:
TO_CHAR and TO_DATE)
NLS_LANGUAGE is set to AMERICAN.)
ORDER BY is specified. (GROUP BY uses a binary sort, unless ORDER BY is specified.)
The value specified for NLS_LANGUAGE in the initialization parameter 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:
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 inexistante
Messages used by the server are stored in binary-format files that are placed in the $ORACLE_HOME/product_name/mesg directory, or the equivalent. Multiple versions of these files can exist, one for each supported language, using the filename convention:
<product_id><language_abbrev>.MSB
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 parameter 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.
ALTER SESSION SET NLS_LANGUAGE=Italian; SELECT ename, hiredate, ROUND(sal/12,2) sal FROM emp; ENAME HIREDATE SAL ----- -------- --- Clark 09-Dic-88 4195.83 Miller 23-Mar-87 4366.67 Strauß 01-Apr-95 3795.87 SQL> ALTER SESSION SET NLS_LANGUAGE=German; SQL> SELECT ename, hiredate, ROUND(sal/12,2) sal FROM emp; ENAME HIREDATE SAL ----- -------- --- Clark 09-DEZ-88 4195.83 Miller 23-MÄR-87 4366.67 Strauß 01-APR-95 3795.87
| Parameter type: |
String |
|
Parameter scope: |
Dynamic, Initialization Parameter, |
| Default value: |
|
| Range of values: |
|
NLS_LENGTH_SEMANTICS enables you to create CHAR, VARCHAR2, and LONG columns using either byte or character length semantics. NCHAR, NVARCHAR2, CLOB, and NCLOB columns are always character-based. Existing columns are not affected.
You may be required to use byte semantics in order to maintain compatibility with existing applications.
NLS_LENGTH_SEMANTICS does not apply to tables in SYS and SYSTEM. The data dictionary will always use byte semantics.
| Parameter type: |
String |
| Parameter scope: |
Initialization Parameter and |
| Default value: |
Derived from |
| Range of values: |
Any valid territory name |
NLS_TERRITORY specifies the conventions for the following default date and numeric formatting characteristics:
The value specified for NLS_TERRITORY in the initialization parameter file is the default for the instance. For example, to specify the default as France, the parameter should be set as follows:
NLS_TERRITORY = FRANCE
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 parameter 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 value in the initialization parameter file is overridden at 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, issue the following statement:
ALTER SESSION SET NLS_TERRITORY=France;
The following examples show behavior before and after setting NLS_TERRITORY:
SQL> DESCRIBE SalaryTable; Name Null? TYPE --------- ------- ------ SALARY NUMBER SQL> column SALARY format L999,999.99; SQL> SELECT * from SalaryTable; SALARY --------------------- $100,000.00 $150,000.00 ALTER SESSION SET NLS_TERRITORY = Germany; Session altered. SQL> SELECT * from SalaryTable; SALARY --------------------- DM100,000.00 DM150,000.00 ALTER SESSION SET NLS_LANGUAGE = German; Sitzung wurde geändert. SQL> SELECT * from SalaryTable; SALARY --------------------- DM100,000.00 DM150,000.00 ALTER SESSION SET NLS_TERRITORY = France; Sitzung wurde geändert. SQL> SELECT * from SalaryTable; SALARY --------------------- F100,000.00 F150,000.00
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.
The default values for language and territory can be overridden during a session by using the ALTER SESSION statement. For example:
% setenv NLS_LANG Italian_Italy.WE8DEC SQL> SELECT ename, hiredate, ROUND(sal/12,2) sal FROM emp; ENAME HIREDATE SAL ----- -------- --- Clark 09-Dic-88 4195,83 Miller 23-Mar-87 4366,67 Strauß 01-Apr-95 3795,87 ALTER SESSION SET NLS_LANGUAGE = German NLS_DATE_FORMAT = 'DD.MON.YY' NLS_NUMERIC_CHARACTERS = '.,'; SQL> SELECT ename, hiredate, ROUND(sal/12,2) sal FROM emp; ENAME HIREDATE SAL ----- -------- --- Clark 09.DEZ.88 4195.83 Miller 23.MÄR.87 4366.67 Strauß 01.APR.95 3795.87
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.
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:
The application is responsible for meeting the last requirement. NLS takes care of the other two.
Oracle enables you to control many aspects of date and time display.
Many different date formats are used throughout the world. Some typical ones are shown in Table 3-3.
| Country | Description | Example |
|---|---|---|
|
Estonia |
dd.mm.yyyy |
28.02.1998 |
|
Germany |
dd-mm-rr |
28-02-98 |
|
Japan |
rr-mm-dd |
98-02-28 |
|
UK |
dd-mon-rr |
28-Feb-98 |
|
US |
dd-mon-rr |
28-Feb-98 |
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:
NLS_DATE_FORMAT = "MM/DD/YYYY"
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:
NLS_DATE_FORMAT = '\"Today\'s date\" 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 the initialization file:
NLS_DATE_FORMAT = "DD RM YYYY"
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):
SELECT TO_CHAR(SYSDATE) CURRDATE FROM DUAL; CURRDATE --------- 12 II 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.
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.
Partition bound expressions for a date column must specify a date using a format that requires the month, day, and 4-digit year to be 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:
TO_DATE('11-jan-1997', 'dd-mon-yyyy')
If the default date format, specified by NLS_DATE_FORMAT, of your session does not support specification of a date independent of the current century (if your default date format is MM-DD-YY for example), then you must take one of the following actions:
TO_DATE to express the date in a format that requires you to fully specify the day, month, and 4-digit year.
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.
| Parameter type: |
String |
|
Parameter scope: |
Initialization Parameter, Environment Variable, and |
| Default value: |
Derived from |
| Range of values: |
Any valid language name |
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:
NLS_DATE_LANGUAGE = FRENCH
In this case, the query
SELECT TO_CHAR(SYSDATE, 'Day:Dd Month yyyy') FROM DUAL;
returns
Mercredi:12 Février 1997
Month and day name abbreviations are also in the specified language. For example:
SELECT TO_CHAR(SYSDATE, 'Dy:dd Mon yyyy') FROM DUAL; Me:12 Fév 1997
The default date format also uses the language-specific month name abbreviations. For example, if the default date format is DD-MON-YYYY, then the above date can be inserted as follows:
INSERT INTO tablename VALUES ('12-Fév-1997');
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('12-Fév'),'Day: ddspth Month') FROM DUAL;
returns:
Mercredi: twelfth Février
You can alter the default value of NLS_DATE_LANGUAGE by changing its value in the initialization parameter file and then restarting the instance. You can alter the value during a session using an ALTER SESSION SET NLS_DATE_LANGUAGE statement.
Many different time formats are used throughout the world. Some typical time formats are shown in Table 3-4.
| Country | Description | Example |
|---|---|---|
|
Estonia |
hh24:mi:ss |
13:50:23 |
|
Germany |
hh24:mi:ss |
13:50:23 |
|
Japan |
hh24:mi:ss |
13:50:23 |
|
UK |
hh24:mi:ss |
13:50:23 |
|
US |
hh:mi:ssxff am |
1:50:23.555 PM |
NLS_TIMESTAMP_FORMAT defines the default timestamp format to use with TO_CHAR and TO_TIMESTAMP functions. The value must be surrounded by quotation marks as follows:
NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF'
An example is:
TO_TIMESTAMP('11-nov-2000 01:00:00.336', 'dd-mon-yyyy hh:mi:ss.ff')
You can specify the value of NLS_TIMESTAMP_FORMAT by setting it in the initialization parameter file. You can specify its value for a client as a client environment variable.
You can also alter the value of NLS_TIMESTAMP_FORMAT by changing its value in the initialization parameter file and then restarting the instance. To alter the value during a session, use the ALTER SESSION SET statement.
NLS_TIMESTAMP_TZ_FORMAT defines the default timestamp with time zone format to use with TO_CHAR and TO_TIMESTAMP_TZ functions. The value must be surrounded by quotation marks as follows:
NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF TZH:TZM'
An example is:
TO_TIMESTAMP_TZ('2000-08-20, 05:00:00.55 America/Los_Angeles', 'yyyy-mm-dd hh:mi:ss.ff TZR')
You can specify the value of NLS_TIMESTAMP_TZ_FORMAT by setting it in the initialization parameter file. You can specify its value for a client as a client environment variable.
You can also alter the value of NLS_TIMESTAMP_TZ_FORMAT by changing its value in the initialization parameter file and then restarting the instance. To alter the value during a session, use the ALTER SESSION SET statement.
|
See Also:
Oracle9i SQL Reference for more information about date format elements and time zone formats |
You can create a database with a specific time zone by specifying:
CREATE DATABASE ... SET TIME_ZONE = '-08:00 ';
CREATE DATABASE ... SET TIME_ZONE = 'PST ';
To see a listing of valid region names, query the V$TIMEZONE_NAMES view.
The database time zone is relevant only for TIMESTAMP WITH LOCAL TIME ZONE columns. Oracle normalizes all TIMESTAMP WITH LOCAL TIME ZONE data to the time zone of the database when the data is stored on disk. If you do not specify the SET TIME_ZONE clause, then Oracle uses the operating system's time zone of the server. If the operating system's time zone is not a valid Oracle time zone, the database time zone defaults to UTC.
After the database has been created, you can change the time zone by issuing the ALTER DATABASE SET TIME_ZONE statement and then shutting down and starting up the database. The following example sets the time zone of the database to London time:
ALTER DATABASE SET TIME_ZONE = 'Europe/London ';
To find out the time zone of a database, use the DBTIMEZONE function as shown in the following example:
SELECT dbtimezone FROM dual; DBTIME ------- -08:00
You can change the time zone parameter of a user session by issuing an ALTER SESSION statement:
ALTER SESSION SET TIME_ZONE = local;
ALTER SESSION SET TIME_ZONE = DBTIMEZONE;
ALTER SESSION SET TIME_ZONE = '-05:00';
ALTER SESSION SET TIME_ZONE = 'America/New_York';
You can use the environment variable ORA_SDTZ to set the default client session time zone. This variable takes input like DB_TZ, OS_TZ, time zone region or numerical time zone offset. If ORA_SDTZ is set to DB_TZ, the session time zone will be the same as the database time zone. If it is set to OS_TZ, the session time zone will be same as the operating system's time zone. If ORA_SDTZ is set to an invalid Oracle time zone, Oracle uses the operating system's time zone as default session time zone. If the operating system's time zone is not a valid Oracle time zone, the session time zone defaults to UTC. To find out the time zone of a user session, use the SESSIONTIMEZONE function as shown in the following example:
SELECT sessiontimezone FROM dual; SESSIONTIMEZONE --------------- -08:00
Oracle allows you to control calendar-related items through the use of parameters.
The type of calendar information stored for each territory is as follows:
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.
| März | 1998 | |||||
|---|---|---|---|---|---|---|
|
Mo |
Di |
Mi |
Do |
Fr |
Sa |
So |
|
|
|
|
|
|
|
1 |
|
2 |
3 |
4 |
5 |
6 |
7 |
8 |
|
9 |
10 |
11 |
12 |
13 |
14 |
15 |
|
16 |
17 |
18 |
19 |
20 |
21 |
22 |
|
23 |
24 |
25 |
26 |
27 |
28 |
29 |
|
30 |
31 |
|
|
|
|
|
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.
To support the ISO standard, the IW format element is provided that returns the ISO week number.
A typical example with four or more days in the first week is:
A typical example with three or fewer days in the first week is:
Oracle supports six calendar systems in addition to the default Gregorian:
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.
| Parameter type: |
String |
|
Parameter scope: |
Initialization Parameter, Environment Variable, and |
| Default value: |
Gregorian |
| Range of values: |
Any valid calendar format name |
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:
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:
SELECT SYSDATE FROM DUAL; SYSDATE -------- H 09-05-15
Oracle allows you to control how numbers appear.
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 3-8.
| Country | Example Numeric Formats |
|---|---|
|
Estonia |
1 234 567,89 |
|
Germany |
1.234.567,89 |
|
Japan |
1,234,567.89 |
|
UK |
1,234,567.89 |
|
US |
1,234,567.89 |
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 (<), greater than sign (>).
The characters are specified in the following format:
NLS_NUMERIC_CHARACTERS = "<decimal_character><group_separator>"
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:
NLS_NUMERIC_CHARACTERS = ",."
Both characters are single byte and must be different. Either can be a space.
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:
INSERT INTO SIZES (ITEMID, WIDTH, HEIGHT, QUANTITY) VALUES (618, '45,5', 27.86, TO_NUMBER('1.234','9G999'));
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:
NLS_NUMERIC_CHARACTERS in the initialization parameter file and then restart the instance.
ALTER SESSION SET NLS_NUMERIC_CHARACTERS statement to change the parameter's value during a session.
Oracle allows you to control how currency and financial symbols appear.
Many different currency formats are used throughout the world. Some typical ones are shown in Table 3-9.
| Country | Example |
|---|---|
|
Estonia |
1 234,56 kr |
|
Germany |
1.234,56 DM |
|
Japan |
¥1,234.56 |
|
UK |
£1,234.56 |
|
US |
$1,234.56 |
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:
NLS_CURRENCY = "Dfl "
In this case, the query
SELECT TO_CHAR(TOTAL, 'L099G999D99') "TOTAL" FROM ORDERS WHERE CUSTNO = 586;
returns
TOTAL ------------- Dfl 12.673,49
You can alter the default value of NLS_CURRENCY by changing its value in the initialization parameter file and then restarting the instance. You can alter its value during a session using an ALTER SESSION SET NLS_CURRENCY statement.
| Parameter type: |
String |
|
Parameter scope: |
Initialization Parameter, Environment Variable, and |
| Default value: |
Derived from |
| Range of values: |
Any valid territory name |
This parameter specifies the character string returned by the number format mask C, the ISO currency symbol, overriding the value 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, it is 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;
returns
TOTAL ------------- FRF12.673,49
You can alter the default value of NLS_ISO_CURRENCY by changing its value in the initialization parameter file and then restarting the instance. 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 3-10.
| Country | Example |
|---|---|
|
Estonia |
1 234 567,89 EEK |
|
Germany |
1.234.567,89 DEM |
|
Japan |
1,234,567.89 JPY |
|
UK |
1,234,567.89 GBP |
|
US |
1,234,567.89 USD |
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 3-11 lists the character sets that support the Euro symbol:
| Parameter type: |
String |
| Parameter scope: |
Environment Variable |
| Default value: |
Derived from |
| Range of values: |
Any valid name |
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 (<), greater than sign (>).
| Parameter type: |
String |
| Parameter scope: |
Environment Variable |
| Default value: |
Derived from |
| 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.
This parameter can be specified only in the client environment. It can be retrieved through the OCIGetNlsInfo function.
| Parameter type: |
String |
| Parameter scope: |
Environment Variable |
| Default value: |
Derived from |
| 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.
This parameter can be specified only in the client environment. It can be retrieved through the OCIGetNlsInfo function.
Oracle allows you to choose how data is sorted through the use of linguistic parameters.
Oracle provides many different types of sorts, 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.
This parameter specifies the type of sort for character data, overriding that defined implicitly by NLS_LANGUAGE.
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 has an appropriate linguistic sort sequence defined that uses the same name.
You can alter the default value of NLS_SORT by changing its value in the initialization parameter file and then restarting the instance. You can alter its value during a session using an ALTER SESSION SET NLS_SORT statement.
A complete list of linguistic definitions is provided in Table A-10, "Monolingual Linguistic Sorts".
| Parameter type: |
String |
|
Parameter scope: |
Initialization Parameter, Environment Variable and |
| Default value: |
Binary |
| Range of values: |
|
You can use this parameter to avoid the cumbersome process of using NLS_SORT in SQL statements. Normally, comparison in the WHERE clause and in PL/SQL blocks 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:
ALTER SESSION SET NLS_COMP = ANSI;
To specify that comparison in the WHERE clause is always binary, issue the following statement:
ALTER SESSION SET NLS_COMP = BINARY;
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:
CREATE INDEX i ON t(NLSSORT(col, 'NLS_SORT=FRENCH'));
| Parameter type: |
String |
| Parameter scope: |
Environment Variable |
| Default value: |
Derived from |
| Range of values: |
Any valid character |
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 (<), greater than sign (>), period (.).
You can specify the character set used for the client.
| Parameter type: |
String |
| Parameter scope: |
Environment Variable, |
| Default value: |
|
| Range of values: |
|
NLS_NCHAR_CONV_EXCP determines whether data loss during an implicit or explicit character type conversion will report an error.
|
 Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|