10.2. Using NLS Parameters
Ultimately, Oracle globalization support options are defined by NLS parameter settings. By assigning values to specific NLS parameters, you can control when, where, and how Oracle will utilize globalization support functionality. These settings can be specified in a variety of ways, and their effects may vary accordingly.
On the server side, NLS parameters are read from initialization parameter settings at instance startup time. The values are stored in the data dictionary, as are the database and national character set settings.
On the client side, NLS parameters can be defined as environment variables (such as NLS_LANG), or they can be set at the session level by using the ALTER SESSION statement. NLS parameters can also be defined inside SQL function calls with a scope limited to only the current function. Therefore, it is vital to understand the order of precedence that Oracle follows concerning NLS parameter settings.
In the following sections, you'll learn about many of the different NLS parameters, how to set them, and what effect they will have on the system. You'll also learn how Oracle prioritizes NLS parameter settings. Lastly, you will learn how to use NLS data dictionary and dynamic performance views to access NLS information from the database.
10.2.1. Setting NLS Parameters
Oracle's globalization support is designed to be very simple to use. In many environments, globalization needs can be met by setting a single client side parameter (NLS_LANG). This is because Oracle automatically derives lower-level specifics from the high-level settings. For instance, if the NLS_TERRITORY parameter is set to AMERICA, Oracle assumes that currency should be displayed as dollars, commas should be used to separate thousands of dollars, and so on.
However, the granularity provided by Oracle's globalization support allows almost unlimited variations for users with even the most demanding globalization needs.
NLS parameters can be classified into the following categories:
Language and territory parameters
Date and time parameters
Calendar parameters
Numeric, list, and monetary parameters
Length semantics
Each category offers one or more individual parameters that can be set to meet your exact globalization needs.
In the following sections, you will learn how to set the NLS_LANG client-side environment variable to specify the NLS environment for your session. You'll also learn about each of the different categories of NLS parameter settings, and the different options they offer.
10.2.1.1. Using the NLS_LANG Parameter
NLS_LANG is a client-side environment variable that defines the language, territory, and character set for the client. It is functionally equivalent to setting the NLS_LANGUAGE, NLS_TERRITORY, and NLS_CHARACTERSET parameters individually.
For most clients, the NLS_LANG parameter is all that needs to be set to define the entire globalization environment. This is true because the NLS_LANGUAGE and NLS_TERRITORY settings define the default settings for nearly all other NLS parameters.
The NLS_LANGUAGE parameter, for instance, specifies the default conventions to be used for all of the following globalization elements:
Language for server messages
Day and month names and abbreviations
Symbols to represent AM, PM, AD, and BC
Sorting sequence for character data
Affirmative and negative response strings (YES, NO)
The NLS_TERRITORY parameter specifies the default conventions used for these globalization elements:
Date format
Decimal character
Group separator
Local currency symbol
ISO currency symbol
Dual currency symbol
First day of the week
Credit/debit symbols
ISO week flag
List separator
Therefore, no other NLS parameters need to be set unless the default settings don't meet your needs.
The format for setting the NLS_LANG parameter is as follows:
NLS_LANG = language_territory.characterset
For example, the following are all valid:
NLS_LANG=AMERICAN_AMERICA.US7ASCII NLS_LANG=JAPANESE_JAPAN.JA16EUC NLS_LANG=FRENCH_CANADA.WE8ISO8859P1
The language element controls the conventions used for Oracle messages, sorting, day, and month names. If language is not set, Oracle will default to AMERICAN. Each language is identified by a unique name such as FRENCH or GERMAN. Languages also impose a default territory and character set that will be used unless overridden.
The territory element determines the default date, monetary format, and numeric format conventions. If the territory is not defined, the default territory value from the language setting will be used. Territories carry distinct names such as AMERICA, CANADA, or GERMANY.
The character set element determines the client character set. Normally this would be the Oracle character set that matches the character set of the operating system or terminal. Character sets have unique identifiers such as WE8IS08859P1, US7ASCII, or JA16EUC.
All NLS_LANG definition components are optional. For example, the following is valid to set the language component independently of the other components:
NLS_LANG=FRENCH
It is also possible to set the territory and character set components independently, but the following conventions must be followed:
Territory must be preceded by an underscore character (_).
Character set must be preceded by a period (.).
For example, to set the territory to AMERICA, you could use this syntax:
NLS_LANG=_AMERICA
Oracle Character Set Naming ConventionThe naming convention for Oracle character sets is as follows: region number_of_bits standard_character_set_name [S][C] where
For example, US7ASCII is a seven-bit United States code commonly referred to as ASCII (American Standard Code for Information Interchange). The Unicode character sets UTF-8 and UTF-E defy Oracle's standard character set naming convention. |
To set the client character set to UTF-8, you could use this syntax:
NLS_LANG=.UTF8
|
In the following example, you'll set the NLS_LANG parameter to FRENCH_FRANCE.WEISO8859P1 and see how this affects your session. Remember that NLS_LANG is an environment variable setting, so it must be set in the operating system before connecting to Oracle:
$ export NLS_LANG=French_France.WE8ISO8859P1
$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.1.0.2.0 - Production on Dim. Août 29 23:11:07 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select prod_id, time_id,
round(sum(amount_sold),2) amount
from sh.sales
group by prod_id, time_id;
PROD_ID TIME_ID AMOUNT
---------- -------- -------------------------
140 23/02/98 831,08
140 02/03/98 1427,66
140 08/03/98 1432,76
140 18/03/98 1916,1
140 23/03/98 894,18
140 27/03/98 1234,82
146 02/01/98 587,39
146 18/01/98 606,6
146 23/01/98 419,75
146 27/01/98 436,54146 02/02/98 402,96
140 02/02/98 1091,74
140 03/02/98 247,5
140 14/02/98 1447,72
140 16/02/98 41,09As you can see in this example, the date and number formats follow the conventions established in the NLS_LANG settings.
10.2.1.2. Using Language and Territory Parameters
NLS language and territory functionality can also be defined individually using the NLS_LANGUAGE and NLS_TERRITORY parameters.
On the server side, these parameters can be set as initialization parameters. They will then become the default settings for the Oracle instance. For example, the following lines could be inserted into the INIT.ORA file:
NLS_LANGUAGE=French NLS_TERRITORY=France
When the database instance is next started, these settings will become the default settings for the instance.
On the client side, these parameters can be set within a session by using the ALTER SESSION statement, as shown here:
SQL> alter session set NLS_LANGUAGE=French; Session altered. SQL> alter session set NLS_TERRITORY=France; Session altered.
NLS parameters modified using ALTER SESSION have a higher precedence than those set through environment variables such as NLS_LANG. Therefore, they will override the previously set parameter values. This topic will be covered later in this chapter.
10.2.1.3. Using Date and Time Parameters
NLS date and time functionality can also be defined individually using the following NLS parameters:
All of these parameters can be set within a session by using the ALTER SESSION statement. They can also be defined as initialization parameters and will then become default settings for the entire instance.
10.2.1.3.1. NLS_DATE_FORMAT
The NLS_DATE_FORMAT parameter specifies the default format for dates in the current session. It can be defined as any valid date format mask such as:
SQL> ALTER SESSION SET NLS_DATE_FORMAT = MM/DD/YY; Session altered.
You can even append text literals into the date format, if you wish, by enclosing the literal in double quotes. You must also enclose the entire format string in apostrophes (single quotes), as shown here:
SQL> alter session set NLS_DATE_FORMAT ='"Today''s date is "MM/DD/YYYY'; Session altered. SQL> select sysdate from dual; SYSDATE -------------------------- Today's date is 08/30/2004
Note that normal quoting rules apply inside the text literal. Therefore, two apostrophes were required to create the string "Today's".
10.2.1.3.2. NLS_DATE_LANGUAGE
The NLS_DATE_LANGUAGE parameter governs the language used in the following situations:
Day and month names and abbreviations displayed by the functions TO_CHAR and TO_DATE
Day and month names returned by the default date format (NLS_DATE_FORMAT)
Abbreviations for AM, PM, AD, and BC
NLS_DATE_LANGUAGE accepts any valid language as a value and can be set as shown here:
SQL> alter session set nls_date_language=Italian; Session altered.
SQL> select to_char(sysdate,'Day:Dd Month YYYY') from dual; TO_CHAR(SYSDATE,'DAY:DDMONT --------------------------- Lunedì :30 Agosto 2004
10.2.1.3.3. NLS_TIMESTAMP_FORMAT
The NLS_TIMESTAMP_FORMAT parameter is used to set the default date format for both TIMESTAMP and TIMESTAMP WITH TIME ZONE datatypes. An example is shown here:
SQL> alter session set nls_timestamp_format='MM/DD/YYYY HH24:MI:SS.FF'; Session altered. SQL> select startup_time 2 from sys.dba_hist_snapshot 3 where rownum < 3; STARTUP_TIME ------------------------------------------------------------------------ 10/22/2004 08:06:09:000 10/22/2004 08:06:09:000 SQL> select next_run_date 2 from sys.dba_scheduler_jobs; NEXT_RUN_DATE --------------------------------------------------------------------------- 2004/11/12 03:00 −05:00
The TIMESTAMP and TIMESTAMP WITH TIME ZONE datatypes will be covered later in this chapter.
10.2.1.3.4. NLS_TIMESTAMP_TZ_FORMAT
Like the NLS_TIMESTAMP_FORMAT parameter, the NLS_TIMESTAMP_TZ_FORMAT parameter is used to set the default date format for TIMESTAMP and TIMESTAMP WITH TIME ZONE datatypes. However, as the name suggests, it adds the option of time zone formatting, as shown here:
SQL> alter session set nls_timestamp_tz_format = 'YYYY/MM/DD HH:MI TZH:TZM'; Session altered.
SQL> select startup_time 2 from sys.dba_hist_snapshot 3 where rownum < 3; STARTUP_TIME ------------------------------------------------------------------------ 10/22/2004 08:06:09:000 10/22/2004 08:06:09:000 SQL> select next_run_date 2 from sys.dba_scheduler_jobs; NEXT_RUN_DATE --------------------------------------------------------------------------- 2004/11/12 03:00 −05:00
As you can see in the example, the TZH:TZM element shows the time zone offset in hours and minutes.
10.2.1.4. Using Calendar Parameters
Different geographical areas can use different calendaring systems. Oracle 10g'sglobalization support defines seven distinct calendars, all of which are fully supported:
Gregorian
Japanese Imperial
ROC Official
Persian
Thai Buddha
Arabic Hijrah
English Hijrah
For each of these calendars, the following information is maintained:
First day of the week While the United States and many other countries consider Sunday to represent the first day of the week, other countries, such as Germany, consider Monday to be the first day of the week.
First calendar week of the year Many countries use the week number for things like bookkeeping and scheduling. However, an International Standards Organization (ISO) week can differ from the calendar week number. (ISO weeks run from Monday through Sunday.) Oracle supports both conventions.
Number of days/months in a year The number of days and months in a year can differ between calendars, as shown in Table 10.2.
First year of the era Different regions may also choose a notable year in which to start, much like the Gregorian calendar starts with Anno Domini (Latin for "the year of the Lord"), also known as the Common Era. The Islamic calendar, for example, starts with the year of the Hegria (622 AD, when the prophet Mohammed and his followers migrated from Mecca to Medina).
The NLS_CALENDAR parameter is used to specify which calendar Oracle should use, as shown here:
SQL> alter session set NLS_CALENDAR = 'Persian'; Session altered. SQL> select sysdate from dual; SYSDATE ------------------ 10 Shahruoar 1383
| Calendar | Description |
|---|---|
| Gregorian | The standard calendar used by most of the world. The Gregorian calendar has 365 days in each year, with 366 days on leap years. The number of days in a month varies. Years are counted from the beginning of the common era or Anno Domini. |
| Japanese Imperial | Same as Gregorian, but the year starts with the beginning of each Imperial era. |
| ROC Official | Same as Gregorian, but the year starts with the founding of the Republic of China. |
| Persian | The first six months have 31 days each. The next five have 30 days each. The last month has 29 days (30 in a leap year). |
| Thai Buddha | Same as Gregorian, but the year begins with B.E. (Buddhist Era), which starts with the death of Gautama Buddha. |
| Arabic Hijrah | Has 12 months with 354 or 355 days. |
| English Hijrah | Has 12 months with 354 or 355 days. |
10.2.1.5. Using Numeric, List, and Monetary Parameters
Number-formatting conventions define how Oracle should display large numbers and numeric lists.
In the United States, for example, the following convention is followed:
1,234,567.89
Germany, on the other hand, uses a convention that is diametrically opposite:
1.234.567,89
In this section, you'll learn to use the various numeric, list, and monetary NLS parameters.
10.2.1.5.1. NLS_NUMERIC_CHARACTERS
The NLS_NUMERIC_CHARACTERS parameter defines the characters that represent the decimal and group separator (for example, thousands, millions, and so on) elements in the number format mask. These elements are represented by the letters D and G respectively in the number format mask. Any single-byte character can be assigned, with the following exceptions:
The decimal character and the group separator cannot be the same character.
They cannot be numeric.
They cannot have mathematical significance (+, −, <, >).
When setting this parameter, the decimal character comes before the group separator, as shown here:
SQL> alter session set NLS_NUMERIC_CHARACTERS=",.";
Session altered.
SQL> select cust_id, to_char(sum(amount_sold), '9G999G999D99') big_sales
from sales
group by cust_id
having sum(amount_sold) > 30000;
CUST_ID BIG_SALES
---------- -------------
2994 30.200,18
3618 30.312,59
9038 45.075,65
12783 33.611,56As you can see, the decimal character is now represented by a comma. The group separator, on the other hand, is now represented by a period.
10.2.1.5.2. NLS_LIST_SEPARATOR
The NLS_LIST_SEPARATOR parameter specifies the character used to separate values in a list of values. The following restrictions apply to the NLS_LIST_SEPARATOR parameter:
It cannot be numeric.
It cannot be the same character as the numeric or monetary decimal character.
It cannot have mathematical significance (+, −, <, >).
NOTE
The NLS_LIST_SEPARATOR parameter is strictly a client-side setting. It has no meaning on the server. Therefore, it is set through a client-side environment variable but does not execute an implicit ALTER SESSION when a server connection is established.
10.2.1.5.3. NLS_CURRENCY
The NLS_CURRENCY parameter defines the currency symbol that will be displayed by the element L in the number format mask, as shown here:
SQL> alter session set NLS_CURRENCY = "£";
Session altered.
SQL> select to_char(123.45,'L9G999G999D99') amount
from dual;
AMOUNT
-----------------------
£123.45The NLS_CURRENCY parameter is not limited to a single character. It can be set to a string as well:
SQL> alter session set NLS_CURRENCY = " USD";
Session altered.
SQL> select to_char(123.45,'9G999G999D99L') amount
2 from dual;
AMOUNT
-----------------------
123.45 USDNotice in the example that a space is embedded at the beginning of the string. Without the space, the output would appear as shown here:
AMOUNT
-----------------------
123.45USD10.2.1.5.4. NLS_ISO_CURRENCY
The NLS_ISO_CURRENCY parameter is used to prevent ambiguity in the currency symbol. For example, the dollar sign ($) can be used for both Australian and American dollars. NLS_ISO_CURRENCY uses a unique text string in place of the currency sign. Several common examples are shown here:
USD: United States
AUD: Australia
EEK: Estonia
EUR: Germany
GBP: United Kingdom
The NLS_ISO_CURRENCY parameter defines the currency that will be displayed by the C element of the number format mask. It can be modified using the ALTER SESSION statement, but instead of a text string, it requires a valid territory name, as follows:
SQL> alter session set NLS_ISO_CURRENCY=France;
Session altered.
SQL> select to_char(123.45,'9G999G999D99C') amount
2 from dual;
AMOUNT
--------------------
123.45EUR10.2.1.6. Using the NLS_LENGTH_SEMANTICS Parameter
Single-byte character sets always use one byte to store one character. This makes storage calculation a breeze. But when using a multi-byte character set, such as Unicode, a single character may use several bytes of storage. Column sizing becomes much more difficult in this situation.
Length semantics, originally introduced in Oracle 9i, make it possible to size columns using either bytes or characters. The method of calculating the length of character strings in bytes is known as byte semantics. Calculating the length in characters is referred to as character semantics.
The NLS_LENGTH_SEMANTICS parameter defines the default method of length semantics to either BYTE (the default) or CHAR. An example is shown here:
SQL> alter system set NLS_LENGTH_SEMANTICS = CHAR; System altered.
Consider the following example:
SQL> create table test_table ( Last_name VARCHAR2(25)); Table created.
When length semantics are set to CHAR, the LAST_NAME column in this table will hold 25 characters, no matter how many actual bytes of storage are required.
When length semantics are set to BYTE, the LAST_NAME column will allocate 25 bytes of storage. If the character set requires 3 bytes to store a single character (or symbol), only eight characters can be stored.
The default setting can be overridden by declaring the length semantics directly in the CREATE TABLE statement. For example, when defining a character column, character semantics can be forced using the following syntax:
SQL> create table test_table ( Last_name VARCHAR2(25 CHAR)); Table created.
This example forces the use of character semantics, regardless of the setting of the NLS_LENGTH_SEMANTICS parameter.
There are a few exceptions to consider when dealing with length semantics:
NCHAR, NVARCHAR, CLOB, and NCLOB datatypes are not affected by the NLS_LENGTH_SEMANTICS parameter value. These are datatypes designed specifically for multi-byte character data; therefore they will always use character semantics.
Tables in the SYS and SYSTEM tablespaces are not governed by the NLS_LENGTH_SEMANTICS parameter. All Data dictionary tables always use byte semantics.
10.2.2. Prioritizing NLS Parameters
Oracle databases often represent only one tier in a multi-tier environment. For instance, let's assume that Arren is a user in France. He uses a custom, client-side application that connects to an application server in Italy. The application server connects to a transaction-processing gateway in Sweden. The transaction-processing gateway connects to the Oracle database in the United States.
Each of these machines may have NLS settings appropriate for their respective locale, none of which match the settings of the database server. How does the database server determine the NLS settings to honor?
There are several different ways in which NLS parameters can be specified. Therefore, when conflicting settings are issued, Oracle needs to have a method of prioritizing to determine which setting will ultimately be used.
NLS parameters can be defined using any of the following methods:
In SQL functions
Default values
In the following sections, you will learn about each of the methods of setting NLS parameter values, as well as how Oracle chooses to prioritize them.
10.2.2.1. Setting Server Initialization Parameters
NLS settings can be defined as initialization parameters on the server. Initialization parameters are loaded at instance startup time, as in this example:
NLS_LANGUAGE=FRENCH
The effect of initialization parameter settings will be seen only on the server. They have no effect on the client side. They will, however, govern sessions created by the client to the server, unless the client NLS environment overrides them.
10.2.2.2. Setting Client Environment Variables
Environment variables on the client side will govern local client-side NLS operations (operations that don't involve the database). They will also override server-side NLS settings for sessions created from the client.
In the following example, the environment variable NLS_LANGUAGE is set to French before opening a session. Note that in a Windows environment, the environment variable could be set either using the set command or in the Environment tab in the System Properties window.
$ export NLS_LANGUAGE=French $ sqlplus "/ as sysdba" SQL*Plus: Release 10.1.0.2.0 - Production on Dim. Août 29 09:26:48 2004 Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options SQL> select to_char(sysdate, 'Mon') from dual; TO_CH ----- Août
By setting the client-side environment variable NLS_LANG, the server's NLS settings were overridden for the session. The client program accomplishes this by issuing an implicit ALTER SESSION statement when a new session is opened.
10.2.2.3. Using the ALTER SESSION Statement
Setting NLS parameters using the ALTER SESSION statement also overrides the server-side NLS settings for the current session, as in this example:
SQL> ALTER SESSION set NLS_SORT = FRENCH; Session altered.
Using ALTER SESSION also overrides any previous ALTER SESSION settings. Therefore, an explicit ALTER SESSION statement overrides settings from the client environment variables (which perform an implicit ALTER SESSION call).
10.2.2.4. Setting NLS Parameters in SQL Functions
NLS parameters can also be set inside certain SQL functions. Inline NLS parameter settings have the highest priority and will override any other NLS settings. However, their scope is limited to the immediate SQL function, as shown here:
SQL> select to_char(sysdate, 'DD/MON/YYYY','nls_date_language=Italian')from dual; TO_CHAR(SYS ----------- 29/AGO/2004 SQL> select to_char(sysdate, 'DD/MON/YYYY') from dual; TO_CHAR(SYS ----------- 29/AUG/2004
As you can see in this example, the inline NLS parameter setting affected only the function in which it was called. It had no effect on the subsequent statement.
NOTE
Only specific SQL functions will accept inline NLS parameter settings.
10.2.2.5. Prioritization Summary
As you learned in the preceding sections, there are five distinct methods in which NLS parameters can be specified. Oracle prioritizes these methods to ensure that conflicting settings can be resolved. Table 10.3 encapsulates these methods for NLS parameter prioritization, as well as the scope for each method.
| Method | Priority | Scope |
|---|---|---|
| Set in SQL functions | 1 | Current SQL function |
| Explicit ALTER SESSION statement | 2 | Current session |
| Client environment variable (implicit ALTER SESSION statement) | 3 | Current session |
| Set by server initialization parameter | 4 | Instance |
| Default | 5 | Instance |
10.2.3. Using NLS Views
Information relating to Oracle NLS settings is stored in the data dictionary and inside fixed tables in memory. This information consists of NLS settings for the session, instance, and database. You can also view a list of the valid values that may be specified when setting NLS parameters.
The following views can be queried to find NLS information from the data dictionary and from dynamic performance tables:
NLS_SESSION_PARAMETERS
NLS_INSTANCE_PARAMETERS
NLS_DATABASE_PARAMETERS
V$NLS_VALID_VALUES
We will look at each of these views in the following sections.
10.2.3.1. NLS_SESSION_PARAMETERS
The NLS_SESSION_PARAMETERS view offers an insight into the current NLS settings for your session. Here is an example:
SQL> select * from nls_session_parameters; PARAMETER VALUE ------------------------------ --------------------------- NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY $ NLS_COMP BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE 17 rows selected.
You will notice that the NLS_SESSION_PARAMETERS view is restricted to show only the current session and nothing more. You may also see settings here that you don't remember specifying. If so, the values represent either the default setting or the value derived from a higher level NLS parameter. For example, if NLS_TERRITORY is set to AMERICA, the NLS_CURRENCY parameter will automatically be set to use dollars.
10.2.3.2. NLS_INSTANCE_PARAMETERS
The NLS_INSTANCE_PARAMETERS view returns NLS settings for the entire instance, rather than for a single session. These are settings that have been set explicitly through initialization parameters or ALTER SYSTEM statements. Here is an example:
SQL> select * from nls_instance_parameters;
PARAMETER VALUE ------------------------------ -------------- NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_SORT NLS_DATE_LANGUAGE NLS_DATE_FORMAT NLS_CURRENCY NLS_NUMERIC_CHARACTERS NLS_ISO_CURRENCY NLS_CALENDAR NLS_TIME_FORMAT NLS_TIMESTAMP_FORMAT NLS_TIME_TZ_FORMAT NLS_TIMESTAMP_TZ_FORMAT NLS_DUAL_CURRENCY NLS_COMP NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE 17 rows selected.
The results from the NLS_INSTANCE_PARAMETERS view show that many parameters have not been explicitly set. Instead, they derive their value from higher-level parameters. For example, NLS_SORT derives its value from NLS_LANGUAGE, while the currency-, date-, and time-related parameters are derived from NLS_TERRITORY.
10.2.3.3. NLS_DATABASE_PARAMETERS
The NLS_DATABASE_PARAMETERS view shows NLS settings for the database itself. These represent the default values that will govern the instance, unless they are overridden by initialization parameter settings.
An example is shown here:
SQL> select * from nls_database_parameters; PARAMETER VALUE ------------------------------ ---------------------------- NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS ., NLS_CHARACTERSET WE8ISO8859P1 NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY $ NLS_COMP BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_RDBMS_VERSION 10.1.0.2.0 20 rows selected.
The values shown in the NLS_DATABASE_PARMETERS view are set at database creation time, based on the parameters used in the CREATE DATABASE statement.
10.2.3.4. V$NLS_VALID_VALUES
The V$NLS_VALID_VALUES dynamic performance view lists all valid values for each of the following NLS parameters: NLS_LANGUAGE, NLS_SORT, NLS_TERRITORY, and NLS_CHARACTERSET.
The following example shows a truncated listing:
SQL> select * from v$nls_valid_values where value like '%GER%'; PARAMETER VALUE ------------------------------ ------------------ LANGUAGE GERMAN LANGUAGE GERMAN_DIN TERRITORY GERMANY TERRITORY ALGERIA SORT GERMAN SORT XGERMAN SORT GERMAN_DIN SORT XGERMAN_DIN