Release 2 (9.2)
Part Number A96529-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| Oracle9i Database Globalization Support Guide Release 2 (9.2) Part Number A96529-01 |
|
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:
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:
NLS_TERRITORY = "CZECH REPUBLIC"
You can use NLS parameters to specify locale-dependent behavior for the client and also to override the default values set for the session in the initialization parameter file. For example, on a UNIX system:
% setenv NLS_SORT FRENCH
ALTER SESSION statement
NLS parameters that are set in an 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;
| See Also:
Oracle9i SQL Reference for more information about the |
NLS parameters can be used explicitly to hardcode NLS behavior within a SQL function. Doing so will override the default values that are set for the session in the initialization parameter file, set for the client with environment variables, or set for the session by the ALTER SESSION statement. For example:
TO_CHAR(hiredate, 'DD/MON/YYYY', 'nls_date_language = FRENCH')
| See Also:
Oracle9i SQL Reference for more information about SQL functions, including the |
Table 3-1 shows the precedence order of the different methods of setting NLS parameters. Higher priority settings override lower priority settings. For example, a default value has the lowest priority and can be overridden by any other method. Another example is that setting an NLS parameter within a SQL function overrides all other methods of setting NLS parameters.
| Priority | Method |
|---|---|
|
1 (highest) |
Explicitly set in SQL functions |
|
2 |
Set by an |
|
3 |
Set as an environment variable |
|
4 |
Specified in the initialization parameter file |
|
5 |
Default |
Table 3-2 lists the NLS parameters available with the Oracle server.
| Parameter | Description | Default | Scope: I = Initialization Parameter File E = Environment Variable A = ALTER SESSION |
|---|---|---|---|
|
Calendar system |
Gregorian |
I, E, A |
|
|
SQL, PL/SQL operator comparison |
|
I, E, A |
|
|
Credit accounting symbol |
Derived from |
E |
|
|
Local currency symbol |
Derived from |
I, E, A |
|
|
Date format |
Derived from |
I, E, A |
|
|
Language for day and month names |
Derived from |
I, E, A |
|
|
Debit accounting symbol |
Derived from |
E |
|
|
ISO international currency symbol |
Derived from |
I, E, A |
|
|
See Also: "Choosing a Locale with the NLS_LANG Environment Variable" |
Language, territory, character set |
|
E |
|
Language |
Derived from |
I, A |
|
|
How strings are treated |
|
I, A |
|
|
Character that separates items in a list |
Derived from |
E |
|
|
Monetary symbol for dollar and cents (or their equivalents) |
Derived from |
E |
|
|
Reports data loss during a character type conversion |
|
I, A |
|
|
Decimal character and group separator |
Derived from |
I, E, A |
|
|
Character sort sequence |
Derived from |
I, E, A |
|
|
Territory |
Derived from |
I, A |
|
|
Timestamp |
Derived from |
I, E, A |
|
|
Timestamp with time zone |
Derived from |
I, E, A |
|
|
Dual currency symbol |
Derived from |
I, E, A |
A locale is a linguistic and cultural environment in which a system or program is running. Setting the NLS_LANG environment parameter is the simplest way to specify locale behavior. It sets the language and territory used by the client application. It also sets the client's character set, which is the character set for data entered or displayed by a client program.
The NLS_LANG parameter has three components: language, territory, and character set. Specify it in the following format, including the punctuation:
NLS_LANG = language_territory.charset
For example, if the Oracle Installer does not populate NLS_LANG, then its value is AMERICAN_AMERICA.US7ASCII. The language is AMERICAN, the territory is AMERICA, and the character set is US7ASCII.
Each component of the NLS_LANG parameter controls the operation of a subset of globalization support features:
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, AMERICAN, FRENCH, or GERMAN. The language argument specifies default values for the territory and character set arguments. If the language is not specified, then the value defaults to AMERICAN.
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 the territory is not specified, then the value is derived from the language value.
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, WE8ISO8859P1, WE8DEC, WE8MSWIN1252, or JA16EUC. Each language has a default character set associated with it.
The three arguments of NLS_LANG can be specified in many combinations, as in the following examples:
NLS_LANG = AMERICAN_AMERICA.WE8MSWIN1252 NLS_LANG = FRENCH_CANADA.WE8DEC NLS_LANG = JAPANESE_JAPAN.JA16EUC
Note that illogical combinations can be set but do 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 the WE8DEC character set does not support any Japanese characters, you cannot store Japanese data if you use this definition for NLS_LANG.
The rest of this section includes the following topics:
| See Also:
Appendix A, "Locale Data" for a complete list of supported languages, territories, and character sets |
Set NLS_LANG as an environment variable at the command line. For example, in the UNIX operating system, 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.
Set NLS_LANG so that the language is AMERICAN, the territory is AMERICA, and the Oracle character set is WE8ISO8859P1:
% setenv NLS_LANG American_America.WE8ISO8859P1
Enter a SELECT statement:
SQL> SELECT last_name, hire_date, ROUND(salary/8,2) salary FROM employees;
You should see results similar to the following:
LAST_NAME HIRE_DATE SALARY ------------------------- --------- ---------- Sciarra 30-SEP-97 962.5 Urman 07-MAR-98 975 Popp 07-DEC-99 862.5
Set NLS_LANG so that the language is FRENCH, the territory is FRANCE, and the Oracle character set is WE8ISO8859P1:
% setenv NLS_LANG French_France.WE8ISO8859P1
Then the query shown in Example 3-1 returns the following output:
LAST_NAME HIRE_DAT SALARY ------------------------- -------- ---------- Sciarra 30/09/97 962,5 Urman 07/03/98 975 Popp 07/12/99 862,5
Note that the date format and the number format have changed. The numbers have not changed, because the underlying data is the same.
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 the database and the client application are automatically the same.
The language and territory components of the NLS_LANG parameter determine the default values for other detailed NLS parameters, such as date format, numeric characters, and linguistic sorting. Each of these detailed parameters can be set in the client environment to override the default values if the NLS_LANG parameter has already been set.
If the NLS_LANG parameter is not set, then 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 the NLS environment dynamically during the session. To do so, you can use the ALTER SESSION statement to change NLS_LANGUAGE, NLS_TERRITORY, and other NLS parameters.
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.
The NLS_LANG character set should reflect the setting of the operating system client. For example, if the database character set is UTF8 and the client has a Windows operating system, you should not set UTF8 as the client character set because there are no UTF8 WIN32 clients. Instead the NLS_LANG setting should reflect the code page of the client.
NLS_LANG is set as a local environment variable on UNIX platforms.
NLS_LANG is set in the registry on Windows platforms. For example, on an English Windows client, the code page is WE8MSWIN1252. An appropriate setting for NLS_LANG is AMERICAN_AMERICA.WE8MSWIN1252.
Setting NLS_LANG correctly allows proper conversion from the client operating system code page to the database character set. When these settings are the same, Oracle assumes that the data being sent or received is encoded in the same character set as the database character set, so no validation or conversion is performed. This can lead to corrupt data if the client code page and the database character set are different and conversions are necessary.
| See Also:
Oracle9i Database Installation Guide for Windows for more information about commonly used values of the |
When a new database is created during the execution of the CREATE DATABASE statement, the NLS database environment is established. The current NLS instance parameters are stored in the data dictionary along with the database and national character sets. The NLS instance parameters are read from the initialization parameter file at instance startup.
You can find the values for NLS parameters by using:
Applications can check the session, instance, and database NLS parameters by querying the following data dictionary views:
NLS_SESSION_PARAMETERS shows the NLS parameters and their values for the session that is querying the view. It does not show information about the character set.NLS_INSTANCE_PARAMETERS shows the current NLS instance parameters that have been explicitly set and the values of the NLS instance parameters.NLS_DATABASE_PARAMETERS shows the values of the NLS parameters that were used when the database was created.Applications can check the following NLS dynamic performance views:
V$NLS_VALID_VALUES lists values for the following NLS parameters: NLS_LANGUAGE, NLS_SORT, NLS_TERRITORY, NLS_CHARACTERSETV$NLS_PARAMETERS shows current values of the following NLS parameters: NLS_CALENDAR, NLS_CHARACTERSET, NLS_CURRENCY, NLS_DATE_FORMAT, NLS_DATE_LANGUAGE, NLS_ISO_CURRENCY, NLS_LANGUAGE, NLS_NUMERIC_CHARACTERS, NLS_SORT, NLS_TERRITORY, NLS_NCHAR_CHARACTERSET, NLS_COMP, NLS_LENGTH_SEMANTICS, NLS_NCHAR_CONV_EXP, NLS_TIMESTAMP_FORMAT, NLS_TIMESTAMP_TZ_FORMAT, NLS_TIME_FORMAT, NLS_TIME_TZ_FORMAT
User applications can query client NLS settings with the OCINlsGetInfo() function.
| See Also:
Chapter 8, "OCI Programming in a Global Environment" for the description of |
This section contains information about the following parameters:
|
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.)YES and NO)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
When the language is French, the server message
ORA-00942: table or view does not exist
appears 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 for your operating system. Multiple versions of these files can exist, one for each supported language, using the following filename convention:
<product_id><language_abbrev>.MSB
For example, the file containing the server messages in French is called ORAF.MSB, because F is the language abbreviation for French.
Messages are stored in these files in one specific character set, depending on the language and the operating system. If this character set is different from the database character set, then message text is automatically converted to the database character set. If necessary, it will then be converted to the client character set if the client character set 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 specific to the operating system. You can alter the NLS_LANGUAGE parameter by changing its value in the initialization parameter file and then restarting the instance.
| See Also:
Your operating system-specific Oracle documentation for more information about the default value of |
All messages and text should be in the same language. For example, when you run an Oracle Developer application, the messages and boilerplate text that you see originate from three sources:
NLS determines the language used for the first two kinds of text. The application is responsible for the language used in its messages and boilerplate text.
The following examples show behavior that results from setting NLS_LANGUAGE to different values.
Use the ALTER SESSION statement to set NLS_LANGUAGE to Italian:
ALTER SESSION SET NLS_LANGUAGE=Italian;
Enter a SELECT statement:
SQL> SELECT last_name, hire_date, ROUND(salary/8,2) salary FROM employees;
You should see results similar to the following:
LAST_NAME HIRE_DATE SALARY ------------------------- --------- ---------- Sciarra 30-SET-97 962.5 Urman 07-MAR-98 975 Popp 07-DIC-99 862.5
Note that the month name abbreviations are in Italian.
| See Also:
"Overriding Default Values for NLS_LANGUAGE and NLS_TERRITORY During a Session" for more information about using the |
Use the ALTER SESSION statement to change the language to German:
SQL> ALTER SESSION SET NLS_LANGUAGE=German;
Enter the same SELECT statement:
SQL> SELECT last_name, hire_date, ROUND(salary/8,2) salary FROM employees;
You should see results similar to the following:
LAST_NAME HIRE_DATE SALARY ------------------------- --------- ---------- Sciarra 30-SEP-97 962.5 Urman 07-MÄR-98 975 Popp 07-DEZ-99 862.5
Note that the language of the month abbreviations has changed.
|
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
When the territory is FRANCE, numbers are 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 specific to the operating system.
If NLS_LANG is specified in the client environment, then the value of NLS_TERRITORY 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. Modifying NLS_TERRITORY resets all derived NLS session parameters to default values for the new territory.
To change the territory to France during a session, issue the following ALTER SESSION statement:
ALTER SESSION SET NLS_TERRITORY=France;
The following examples show behavior that results from different settings of NLS_TERRITORY and NLS_LANGUAGE.
Enter the following SELECT statement:
SQL> SELECT TO_CHAR(salary,'L99G999D99') salary FROM employees;
When NLS_TERRITORY is set to AMERICA and NLS_LANGUAGE is set to AMERICAN, results similar to the following should appear:
SALARY -------------------- $24,000.00 $17,000.00 $17,000.00
Use an ALTER SESSION statement to change the territory to Germany:
ALTER SESSION SET NLS_TERRITORY = Germany; Session altered.
Enter the same SELECT statement as before:
SQL> SELECT TO_CHAR(salary,'L99G999D99') salary FROM employees;
You should see results similar to the following:
SALARY -------------------- €24.000,00 €17.000,00 €17.000,00
Note that the currency symbol has changed from $ to €. The numbers have not changed because the underlying data is the same.
| See Also:
"Overriding Default Values for NLS_LANGUAGE and NLS_TERRITORY During a Session" for more information about using the |
Use an ALTER SESSION statement to change the language to German:
ALTER SESSION SET NLS_LANGUAGE = German; Sitzung wurde geändert.
Note that the server message now appears in German.
Enter the same SELECT statement as before:
SQL> SELECT TO_CHAR(salary,'L99G999D99') salary FROM employees;
You should see the same results as in Example 3-6:
SALARY -------------------- €24.000,00 €17.000,00 €17.000,00
Use an ALTER SESSION statement to change the territory to America:
ALTER SESSION SET NLS_TERRITORY = America; Sitzung wurde geändert.
Enter the same SELECT statement as in the other examples:
SQL> SELECT TO_CHAR(salary,'L99G999D99') salary FROM employees;
You should see output similar to the following:
SALARY -------------------- $24.000,00 $17.000,00 $17.000,00
Note that the currency symbol changed from € to $ because the territory changed from Germany to America.
Default values for NLS_LANGUAGE and NLS_TERRITORY can be overridden during a session by using the ALTER SESSION statement.
Set the NLS_LANG environment variable so that the language is Italian, the territory is Italy, and the character set is WE8DEC:
% setenv NLS_LANG Italian_Italy.WE8DEC
Enter a SELECT statement:
SQL> SELECT last_name, hire_date, ROUND(salary/8,2) salary FROM employees;
You should see output similar to the following:
LAST_NAME HIRE_DATE SALARY ------------------------- --------- ---------- Sciarra 30-SET-97 962,5 Urman 07-MAR-98 975 Popp 07-DIC-99 862,5
Note the language of the month abbreviations and the decimal character.
Use ALTER SESSION statements to change the language, the date format, and the decimal character:
SQL> ALTER SESSION SET NLS_LANGUAGE=german; Session wurde geändert. SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD.MON.YY'; Session wurde geändert. SQL> ALTER SESSION SET NLS_NUMERIC_CHARACTERS='.,'; Session wurde geändert.
Enter the SELECT statement shown in Example 3-9:
SQL> SELECT last_name, hire_date, ROUND(salary/8,2) salary FROM employees;
You should see output similar to the following:
LAST_NAME HIRE_DATE SALARY ------------------------- --------- ---------- Sciarra 30.SEP.97 962.5 Urman 07.MÄR.98 975 Popp 07.DEZ.99 862.5
Note the language of the month abbreviations, the date format, and the decimal character.
The behavior of the NLS_LANG environment variable implicitly determines the language environment of the database for each session. When a session connects to a database, an ALTER SESSION statement is automatically executed 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, then the changes are propagated to all instances to which that user session is connected.
Oracle enables you to control the display of date and time. This section contains the following topics:
Different date formats 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 section includes the following parameters:
The NLS_DATE_FORMAT parameter defines the default date format to use with the TO_CHAR and TO_DATE functions. The NLS_TERRITORY parameter determines the default value of NLS_DATE_FORMAT. The value of NLS_DATE_FORMAT can be any valid date format mask. 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'
To set the default date format to display Roman numerals for the month, include the following line in the initialization parameter file:
NLS_DATE_FORMAT = "DD RM YYYY"
Enter the following SELECT statement:
SELECT TO_CHAR(SYSDATE) currdate FROM dual;
You should see the following output if today's date is February 12, 1997:
CURRDATE --------- 12 II 1997
The value of NLS_DATE_FORMAT 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 (month, day, and year), two one-byte strings (the two slashes), and the two-byte terminator for the format mask. The format for the value of NLS_DATE_FORMAT cannot exceed 24 bytes.
You can alter the default value of NLS_DATE_FORMAT by:
ALTER SESSION SET NLS_DATE_FORMAT statement
| See Also:
Oracle9i SQL ReferenceOracle9i SQL Reference for more information about date format elements and the |
If a table or index is partitioned on a date column, and if the date format specified by NLS_DATE_FORMAT does not specify the first two digits of the year, then you must use the TO_DATE function with a 4-character format mask for the year.
For example:
TO_DATE('11-jan-1997', 'dd-mon-yyyy')
| See Also:
Oracle9i SQL Reference for more information about partitioning tables and indexes and using |
|
Parameter type: |
String |
|
Parameter scope: |
Initialization Parameter, Environment Variable, and |
|
Default value: |
Derived from |
|
Range of values: |
Any valid language name |
The NLS_DATE_LANGUAGE parameter specifies the language for the day and month names produced by the TO_CHAR and TO_DATE functions. NLS_DATE_LANGUAGE overrides the language that is specified implicitly by NLS_LANGUAGE. NLS_DATE_LANGUAGE has the same syntax as the NLS_LANGUAGE parameter, and all supported languages are valid values.
NLS_DATE_LANGUAGE also determines the language used for:
TO_CHAR and TO_DATE functionsNLS_DATE_FORMAT)Set the date language to French:
ALTER SESSIONS SET NLS_DATE_LANGUAGE = FRENCH
Enter a SELECT statement:
SELECT TO_CHAR(SYSDATE, 'Day:Dd Month yyyy') FROM dual;
You should see output similar to the following:
TO_CHAR(SYSDATE,'DAY:DDMONTHYYYY') ------------------------------------------------------------ Vendredi:07 Décembre 2001
When numbers are spelled in words using the TO_CHAR function, the English spelling is always used. For example, enter the following SELECT statement:
SQL> SELECT TO_CHAR(TO_DATE('12-Oct-2001'),'Day: ddspth Month') FROM dual;
You should see output similar to the following:
TO_CHAR(TO_DATE('12-OCT-2001'),'DAY:DDSPTHMONTH') -------------------------------------------------------------------- Vendredi: twelfth Octobre
Month and day abbreviations are determined by NLS_DATE_LANGUAGE. Enter the following SELECT statement:
SELECT TO_CHAR(SYSDATE, 'Dy:dd Mon yyyy') FROM dual;
You should see output similar to the following:
TO_CHAR(SYSDATE,'DY:DDMO ------------------------ Ve:07 Dec 2001
The default date format uses the month abbreviations determined by NLS_DATE_LANGUAGE. For example, if the default date format is DD-MON-YYYY, then insert a date as follows:
INSERT INTO tablename VALUES ('12-Fév-1997');
Different 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 |
This section contains information about the following parameters:
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'
SQL> SELECT TO_TIMESTAMP('11-nov-2000 01:00:00.336', 'dd-mon-yyyy hh:mi:ss.ff') FROM dual;
You should see output similar to the following:
TO_TIMESTAMP('11-NOV-200001:00:00.336','DD-MON-YYYYHH:MI:SS.FF') --------------------------------------------------------------------------- 11-NOV-00 01:00:00.336000000
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:
ALTER SESSION SET NLS_TIMESTAMP_FORMAT statement
| See Also:
Oracle9i SQL Reference for more information about the |
NLS_TIMESTAMP_TZ_FORMAT defines the default format for the timestamp with time zone. It is used with the TO_CHAR and TO_TIMESTAMP_TZ functions.
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.
The format value must be surrounded by quotation marks. For example:
NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF TZH:TZM'
The following example of the TO_TIMESTAMP_TZ function uses the format value that was specified for NLS_TIMESTAMP_TZ_FORMAT:
SQL> SELECT TO_TIMESTAMP_TZ('2000-08-20, 05:00:00.55 America/Los_Angeles', 'yyyy-mm-dd hh:mi:ss.ff TZR') FROM dual;
You should see output similar to the following:
TO_TIMESTAMP_TZ('2000-08-20,05:00:00.44AMERICA/LOS_ANGELES','YYYY-MM-DDHH:M --------------------------------------------------------------------------- 20-AOU-00 05:00:00.440000000 AMERICA/LOS_ANGELES
You can change the value of NLS_TIMESTAMP_TZ_FORMAT by:
ALTER SESSION statement.
| See Also:
Oracle9i SQL Reference for more information about the |
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 list 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 time zone of the operating system of the server. If the operating system's time zone is not a valid Oracle time zone, then the rdatabase time zone defaults to UTC. Oracle's time zone information is derived from the public domain time zone data available at ftp://elsie.nci.nih.gov/pub/. Oracle's time zone information may not reflect the most recent time zone data available from this site.
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 ORA_SDTZ environment variable 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, then the session time zone will be the same as the database time zone. If it is set to OS_TZ, then 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, then 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, then 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
This section includes the following topics:
The following calendar information is stored for each territory:
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, as shown in Table 3-5.
| 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 |
- |
- |
- |
- |
- |
The first day of the week is determined by the NLS_TERRITORY parameter.
Some countries use week numbers for scheduling, planning, and bookkeeping. Oracle supports this convention. In the ISO standard, the week number can be different from the week number of the calendar year. For example, 1st Jan 1988 is in ISO week number 53 of 1987. An ISO week always starts on a Monday and ends on a Sunday.
To support the ISO standard, Oracle provides the IW date format element. It returns the ISO week number.
Table 3-6 shows an example in which January 1 occurs in a week that has four or more days in the first calendar week of the year. The week containing January 1 is the first ISO week of 1998.
Table 3-7 shows an example in which January 1 occurs in a week that has three or fewer days in the first calendar week of the year. The week containing January 1 is the 53rd ISO week of 1998, and the following week is the first ISO week of 1999.
The first calendar week of the year is determined by the NLS_TERRITORY parameter.
Oracle supports six calendar systems in addition to Gregorian, the default:
The calendar system is specified by the NLS_CALENDAR parameter.
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:
| See Also:
Appendix A, "Locale Data" for a list of calendar systems, their default date formats, and the character sets in which dates are displayed |
Set NLS_CALENDAR to Japanese Imperial:
SQL> ALTER SESSIONS SET NLS_CALENDAR='English Hijrah';
Enter a SELECT statement to display SYSDATE:
SELECT SYSDATE FROM dual;
You should see output similar to the following:
SYSDATE -------------------- 24 Ramadan 1422
This section includes the following topics:
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, applications must be able to display numeric information in the format expected at the client site.
Examples of numeric formats are shown in Table 3-8.
| Country | 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 |
Numeric formats are derived from the setting of the NLS_TERRITORY parameter, but they can be overridden by the NLS_NUMERIC_CHARACTERS parameter.
This parameter specifies the decimal character and group separator. The group separator is the character that separates integer groups to show thousands and millions, for example. The group separator is the character returned by the G number format mask. The decimal character separates the integer and decimal parts of a number. Setting NLS_NUMERIC_CHARACTERS overrides the values derived from the setting of NLS_TERRITORY.
Any character can be the decimal or group separator. The two characters specified must be single-byte, and the 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 (>). Either character can be a space.
The characters are specified in the following format:
NLS_NUMERIC_CHARACTERS = "decimal_charactergroup_separator"
To set the decimal character to a comma and the grouping separator to a period, define NLS_NUMERIC_CHARACTERS as follows:
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ",.";
Both characters are single-byte and are different from each other.
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.
Enter a SELECT statement:
SELECT TO_CHAR(4000, '9G999D99') FROM dual;
You should see output similar to the following:
TO_CHAR(4 --------- 4.000,00
You can change the default value of NLS_NUMERIC_CHARACTERS by:
NLS_NUMERIC_CHARACTERS in the initialization parameter file and then restart the instanceALTER SESSION statement to change the parameter's value during a session
| See Also:
Oracle9i SQL Reference for more information about the |
This section includes the following topics:
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€ |
|
Japan |
¥1,234.56 |
|
UK |
£1,234.56 |
|
US |
$1,234.56 |
NLS_CURRENCY specifies the character string returned by the L number format mask, the local currency symbol. Setting NLS_CURRENCY overrides the setting defined implicitly by NLS_TERRITORY.
Connect to the sample schema order entry schema:
SQL> connect oe/oe Connected.
Enter a SELECT statement similar to the following:
SQL> SELECT TO_CHAR(order_total, 'L099G999D99') "total" FROM orders WHERE order_id > 2450;
You should see output similar to the following:
total --------------------- $078,279.60 $006,653.40 $014,087.50 $010,474.60 $012,589.00 $000,129.00 $003,878.40 $021,586.20
You can change the default value of NLS_CURRENCY by:
ALTER SESSION statement
| See Also:
Oracle9i SQL Reference for more information about the |
|
Parameter type: |
String |
|
Parameter scope: |
Initialization Parameter, Environment Variable, and |
|
Default value: |
Derived from |
|
Range of values: |
Any valid territory name |
NLS_ISO_CURRENCY specifies the character string returned by the C number format mask, the ISO currency symbol. Setting NLS_ISO_CURRENCY overrides 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 specifications define unique currency symbols for specific territories or countries. For example, the ISO currency symbol for the US dollar is USD. For the Australian dollar, it is AUD.
More ISO currency symbols are shown in Table 3-10.
| Country | Example |
|---|---|
|
Estonia |
1 234 567,89 EEK |
|
Germany |
1.234.567,89 EUR |
|
Japan |
1,234,567.89 JPY |
|
UK |
1,234,567.89 GBP |
|
US |
1,234,567.89 USD |
NLS_ISO_CURRENCY has the same syntax as the NLS_TERRITORY parameter, and all supported territories are valid values.
This example assumes that you are connected as oe/oe in the sample schema.
To specify the ISO currency symbol for France, set NLS_ISO_CURRENCY as follows:
ALTER SESSION SET NLS_ISO_CURRENCY = FRANCE;
Enter a SELECT statement:
SQL> SELECT TO_CHAR(order_total, 'C099G999D99') "TOTAL" FROM orders WHERE customer_id = 146;
You should see output similar to the following:
TOTAL ------------------ EUR017,848.20 EUR027,455.30 EUR029,249.10 EUR013,824.00 EUR000,086.00
You can change the default value of NLS_ISO_CURRENCY by:
ALTER SESSION statement
| See Also:
Oracle9i SQL Reference for more information about the |
Use NLS_DUAL_CURRENCY to override the default dual currency symbol defined implicitly by NLS_TERRITORY.
NLS_DUAL_CURRENCY was introduced to support the euro currency symbol during the euro transition period. Table 3-11 lists the character sets that support the euro symbol.
The members of the European Monetary Union (EMU) now use the euro as their currency as of January 1, 2002. Setting NLS_TERRITORY to correspond to a country in the EMU (Austria, Belgium, Finland, France, Germany, Greece, Ireland, Italy, Luxembourg, the Netherlands, Portugal, and Spain) results in the default values for NLS_CURRENCY and NLS_DUAL_CURRENCY being set to EUR.
During the transition period (1999 through 2001), Oracle support for the euro was provided in Oracle8i and later as follows:
NLS_CURRENCY was defined as the primary currency of the countryNLS_ISO_CURRENCY was defined as the ISO currency code of a given territoryNLS_DUAL_CURRENCY was defined as the secondary currency symbol (usually the euro) for a given territoryBeginning with Oracle9i release 2 (9.2), the value of NLS_ISO_CURRENCY results in the ISO currency symbol being set to EUR for EMU member countries. For example, suppose NLS_ISO_CURRENCY is set to FRANCE. Enter the following SELECT statement:
SELECT TO_CHAR(TOTAL, 'C099G999D99') "TOTAL" FROM orders WHERE customer_id=585;
You should see output similar to the following:
TOTAL ------- EUR12.673,49
Customers who must retain their obsolete local currency symbol can override the default for NLS_DUAL_CURRENCY or NLS_CURRENCY by defining them as parameters in the initialization file on the server and as environment variables on the client.
It is not possible to override the ISO currency symbol that results from the value of NLS_ISO_CURRENCY.
|
Parameter type: |
String |
|
Parameter scope: |
Environment Variable |
|
Default value: |
Derived from |
|
Range of values: |
Any valid name |
NLS_MONETARY_CHARACTERS specifies the character that separates groups of numbers in monetary expressions. For example, when the territory is America, the thousands separator is a comma, and the decimal separator is a period.
|
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. For example, a space is a valid value of NLS_CREDIT.
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. For example, a minus sign (-) is a valid value of NLS_DEBIT.
This parameter can be specified only in the client environment.
It can be retrieved through the OCIGetNlsInfo() function.
You can choose how to sort data by using linguistic sort parameters.
This section includes the following topics:
NLS_SORT specifies the type of sort for character data. It overrides the value that is defined implicitly by NLS_LANGUAGE.
The syntax of NLS_SORT is:
NLS_SORT = BINARY | sort_name
BINARY specifies a binary sort. sort_name specifies a linguistic sort sequence.
To specify the linguistic sort sequence called German, set NLS_SORT 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 that uses the same name. Oracle offers two kinds of linguistic sorts: monolingual and multilingual. In addition monolingual sorts can be extended to handle special cases. Extended monolingual sorts usually sort characters differently than the ASCII values of the characters. For example, ch and ll are treated as only one character in XSPANISH, the extended Spanish sort. In other words, the SPANISH sort uses modern Spanish collation rules, while XSPANISH uses traditional Spanish sorting rules.
You can alter the default value of NLS_SORT by:
ALTER SESSION statement
See Also:
|
|
Parameter type: |
String |
|
Parameter scope: |
Initialization Parameter, Environment Variable and |
|
Default value: |
Binary |
|
Range of values: |
|
You can use NLS_COMP 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, you must use the NLSSORT SQL function. Sometimes this can be tedious, especially when the linguistic sort has already been specified in the NLS_SORT session parameter. You can use NLS_COMP to indicate that the comparisons must be linguistic according to the NLS_SORT session parameter. Do this 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;
When NLS_COMP is set to ANSI, a linguistic index improves the performance of the linguistic comparison.
To enable a linguistic index, use the following 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. Its default value is derived from the value of NLS_TERRITORY.
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 (.).
This section includes the following topic:
|
Parameter type: |
String |
|
Parameter scope: |
Initialization Parameter, |
|
Default value: |
|
|
Range of values: |
|
NLS_NCHAR_CONV_EXCP determines whether an error is reported when there is data loss during an implicit or explicit character type conversion. The default value results in no error being reported.
| See Also:
Chapter 10, "Character Set Migration" for more information about data loss during character set conversion |
This section includes the following topic:
|
Parameter type: |
String |
|
Parameter scope: |
Dynamic, Initialization Parameter, |
|
Default value: |
|
|
Range of values: |
|
By default, the character datatypes CHAR and VARCHAR2 are specified in bytes, not characters. Hence, the specification CHAR(20) in a table definition allows 20 bytes for storing character data.
This works well if the database character set uses a single-byte character encoding scheme because the number of characters will be the same as the number of bytes. If the database character set uses a multibyte character encoding scheme, then the number of bytes no longer equals the number of characters because a character can consist of one or more bytes. Thus, column widths must be chosen with care to allow for the maximum possible number of bytes for a given number of characters. You can overcome this problem by switching to character semantics when defining the column size.
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 always uses byte semantics.
See Also:
|
|
 Copyright © 1996, 2002 Oracle Corporation. All Rights Reserved. |
|