Oracle® Database 2 Day Developer's Guide, 11g Release 1 (11.1) Part Number B28843-01 |
|
|
View PDF |
This chapter discusses how to develop applications in a globalization support environment, and shows the use of Unicode programming using both SQL and PL/SQL. Unicode programming enables you to write SQL and PL/SQL code that is compatible with multiple languages.
This chapter contains the following sections:
See Also:
Oracle Database Globalization Support Guide for a complete discussion of globalization support with Oracle Database, including setting up the globalization support environment
Oracle Database SQL Language Reference for information about date and time formats
Oracle Database globalization support enables you to store, process, and retrieve data in native languages. It ensures that database utilities, error messages, and sort order, plus date, time, monetary, numeric, and calendar conventions, automatically adapt to any native language and locale.
Globalization support includes National Language Support (NLS) features. National Language Support is the ability to choose a national language and store data in a specific character set. Globalization support enables you to develop multilingual applications and software products that can be accessed and run simultaneously from anywhere in the world. An application can render content of the user interface and process data in the native language and locale preferences of the user.
See Also:
Oracle Database Globalization Support Guide for a complete discussion of globalization support with Oracle Database, including setting up the globalization support environment
Oracle Database standard features include:
Language support: This feature enables you to store, process, and retrieve data in native languages. Through the use of Unicode databases and data types, Oracle Database supports most contemporary languages. See "Setting NLS Parameters".
Territory support: This feature supports cultural conventions that are specific to geographical locations. The default local time format, date format, numeric conventions, and monetary conventions depend on the local territory setting. See "Setting Language and Territory Parameters".
Date and time formats: This feature supports local formats for displaying the hour, day, month, and year. Time zones and daylight saving support are also available. See "Setting Date and Time Parameters".
Monetary and numeric formats: This feature supports local formats for representing currency, credit, debit symbols, and numbers. See "Using Monetary Parameters" and "Using Numeric Formats".
Calendars feature: This feature supports seven different calendar systems in use around the world: Gregorian, Japanese Imperial, ROC Official (Republic of China), Thai Buddha, Persian, English Hijrah, and Arabic Hijrah. See "Setting Calendar Definitions".
Linguistic sorting: This feature supports linguistic definitions for culturally accurate sorting and case conversion. See "Using Linguistic Sort and Search".
Character set support: This feature supports a large number of single-byte, multibyte, and fixed-width encoding schemes that are based on national, international, and vendor-specific standards. See Oracle Database Installation Guide for your platform for a listing of the character sets supported by Oracle Database.
Character semantics: This feature supports character semantics. It is useful for defining the storage requirements for multibyte strings of varying widths in terms of characters instead of bytes. See "Using Length Semantics".
Unicode support: This feature supports Unicode, which is a universal encoded character set that enables you to store information in any language, using a single character set. You can use SQL and PL/SQL to insert and retrieve Unicode data. See "Developing Globalized Applications".
See Also:
Oracle Database Globalization Support Guide for a complete discussion of globalization support with Oracle Database, including setting up the globalization support environment
Oracle Database SQL Language Reference for information about date and time formats
In SQL Developer, the National Language Support Parameters report lists the values of parameters for globalization support.
The NLS parameter values in this report are used at the start of all sessions within SQL Developer, as explained in "Using NLS Parameter Values in the SQL Developer Environment".
To view the National Language Support Parameters report:
In the SQL Developer window, click the Reports tab to display the Reports navigator.
Click the plus sign (+) next to the Data Dictionary Reports node to expand it.
Click the plus sign (+) next to the About Your Database node to expand it.
Click the National Language Support Parameters item.
In the Select Connection dialog box, set Connection to hr_conn
.
Click OK.
The report in the National Language Support Parameters pane shows the current value for NLS parameters for the database associated with the selected connection: NLS_CALENDAR
, NLS_CHARSET
, NLS_COMP
, NLS_CURRENCY
, NLS_DATE_FORMAT
, and so on.
See Also:
Oracle Database Reference for information about the V$NLS_PARAMETERS
view, which displays the NLS settings for the current database instance
Oracle Database SQL Developer User's Guide for a discussion of SQL Developer preferences, including NLS parameters
Oracle Database SQL Developer User's Guide for a discussion of SQL Developer reports
Oracle Database Globalization Support Guide for a complete discussion of globalization support with Oracle Database, including setting up the globalization support environment
Oracle Database SQL Language Reference for information about date and time formats
In an Oracle database, NLS parameter values are initially determined by database initialization parameters. The DBA can set values in the initialization file, and any changes to that file will take effect at the next database startup. Database users can change specific parameter values for the current session (the current connection to the database) by using a statement in the form:
ALTER SESSION SET parameter-name = "value";
When you are using SQL Developer, be aware that the parameter values from the database initialization file are not used. Instead, SQL Developer initially (after installation) uses parameter values that include the following:
NLS_LANG,"AMERICAN" NLS_TERR,"AMERICA" NLS_CHAR,"AL32UTF8" NLS_SORT,"BINARY" NLS_CAL,"GREGORIAN" NLS_DATE_LANG,"AMERICAN" NLS_DATE_FORM,"DD-MON-RR"
These and other NLS parameter values, which are used for all sessions in SQL Developer (such as SQL Worksheet windows and the National Language Support Parameters report, for all connections), are visible in the Database: NLS Parameters preferences pane.
To change the value of any NLS parameter, you have the following options:
Change the value for use with all SQL Developer connections (current and future) by using the Database: NLS Parameters preferences pane, as explained in "Changing NLS Parameter Values for All Sessions".
Change the value for the current connection only by using the ALTER SESSION
statement in the SQL Worksheet window.
Thus, you have great flexibility in testing different language settings during database application development. For example, you can use ALTER SESSION
to see the output of subsequent PL/SQL statements with different language settings, and then revert to the SQL Developer default settings by disconnecting and reconnecting.
For example, assume that the NLS_LANGUAGE
value in the preferences is set to FRENCH
, and that today is March 1, 2007. If you enter SELECT sysdate FROM dual;
in the SQL Worksheet and click the Run Script icon, the output is:
SYSDATE ------------------------- 01-MARS -07
If you enter ALTER SESSION SET NLS_LANGUAGE='AMERICAN';
and enter the preceding SELECT
statement again, the output is:
SYSDATE ------------------------- 01-MAR-07
Continuing with this example, if you disconnect from the current connection and reconnect to it, the session NLS_LANGUAGE
value is FRENCH
(as specified in the preferences), and the SELECT
statement output is:
SYSDATE ------------------------- 01-MARS -07
See Also:
Oracle Database SQL Developer User's Guide for a discussion of SQL Developer preferences, including NLS parameters
Oracle Database Globalization Support Guide for a complete discussion of globalization support with Oracle Database, including setting up the globalization support environment
Oracle Database SQL Language Reference for information about date and time formats
The SQL Developer user preferences for NLS Parameters establish values for use with all SQL Developer connections (current and future). You can view and change the parameter values in the Database: NLS Parameters preferences pane.
Note that these preferences are also displayed in the NLS Parameter Values report, as explained in "Viewing the Current NLS Parameter Values".
To change National Language Support Parameter values:
In the SQL Developer window, click Tools, then Preferences.
In the Preferences dialog box, expand the Database node and select NLS Parameters.
Each text label is a descriptive term for a corresponding NLS_
xxx
parameter.
Make any desired changes to the parameter values.
For example, to change the NLS_LANGUAGE
parameter value to reflect a Spanish language environment, for Language select SPANISH
.
Click OK.
See Also:
Oracle Database SQL Developer User's Guide for a discussion of SQL Developer preferences, including NLS parameters
Oracle Database Globalization Support Guide for a complete discussion of globalization support with Oracle Database, including setting up the globalization support environment
Oracle Database SQL Language Reference for information about date and time formats
This section describes how to set up a globalization support environment.
See Also:
Oracle Database Globalization Support Guide for a complete discussion of globalization support with Oracle Database, including setting up the globalization support environment
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 for Oracle software. It sets the language and territory used by the client application and the database. It also sets the client character set, which is the character set for data entered or displayed by a client program.
The NLS_LANG
parameter sets the language and territory environment used by both the server session (for example, SQL statement processing) and the client application (for example, display formatting in Oracle tools).
While the default NLS_LANG
behavior defined during installation is appropriate for most situations, 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.
Note that you cannot modify the setting for the client character set with 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.
See Also:
Oracle Database Globalization Support Guide for a complete discussion of globalization support with Oracle Database, including setting up the globalization support environment
National Language Support (NLS) parameters determine the locale-specific behavior on both the client and the server. NLS parameters can be specified several ways. You can alter parameters for the user session and override the parameters in SQL functions.
You can alter the NLS parameters settings by:
Setting NLS parameters in an ALTER SESSION
statement to override the default values that are set for the session in the initialization parameter file, or that are set by the client with environment variables. For example:
ALTER SESSION SET NLS_SORT = french;
Note that the changes that you make with the ALTER SESSION
statement apply only to the current user session and are not present the next time you log in.
Using NLS parameters within a SQL function to 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')
Additional methods for setting the NLS parameters include the use of NLS environment variables on the client, which may be platform-dependent, 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 Linux system:
% setenv NLS_SORT FRENCH
See Also:
"Setting NLS Parameters" in Oracle Database Globalization Support Guide for details on setting the NLS parameters
Oracle Database SQL Language Reference for more information about the ALTER SESSION
statement
Oracle Database SQL Language Reference for more information about SQL functions, including the TO_CHAR
function
Oracle Database Administrator's Guide for information about the initialization parameter file
Oracle Database Reference for information about initialization parameters used for globalization support
Setting different NLS parameters for local territories allows the database session to use different cultural settings. For example, you can set the euro (EUR
) as the primary currency and the Japanese yen (JPY
) as the secondary currency for a given database session, even when the territory is defined as AMERICA
.
See Also:
Oracle Database Globalization Support Guide for locale information, including supported languages and territories
The NLS_LANGUAGE
parameter can be set to any valid language name. The default is derived from the NLS_LANG
setting. NLS_LANGUAGE
specifies the default conventions for the following session characteristics:
Language for server messages
Language for day and month names and their abbreviations (specified in the SQL functions TO_CHAR
and TO_DATE
)
Symbols for equivalents of AM
, PM
, AD
, and BC
Default sorting sequence for character data when the ORDER
BY
clause is specified (the GROUP BY
clause uses a binary sort order unless ORDER
BY
is specified.)
To set the NLS_LANGUAGE parameter:
You can change the NLS_LANGUAGE
parameter value and see the effect in the display of results from a query. The following examples show the effect of setting NLS_LANGUAGE
first to Italian and then to German.
In SQL Developer, make a note of the current language in which Oracle Database was installed.
Under Connections, expand Data Dictionary reports, then About Your Database, and then National Language Support Parameters. In the Select Connection dialog box, select hr_conn
from the list of connections. The current language is listed after NLS_LANGUAGE
.
Set the language to Italian.
ALTER SESSION SET NLS_LANGUAGE=ITALIAN;
Enter a SELECT
statement to check the format of the output after the change to Italian.
SELECT last_name, hire_date, ROUND(salary/8,2) salary FROM employees WHERE employee_id IN (111, 112, 113);
The output from the example should be similar to the following. Note that the abbreviations for month names are in Italian.
LAST_NAME HIRE_DATE SALARY ------------------------- --------- ---------- Sciarra 30-SET-97 962.5 Urman 07-MAR-98 975 Popp 07-DIC-99 862.5
Set the language to German.
ALTER SESSION SET NLS_LANGUAGE=GERMAN;
Enter the same SELECT
statement to check the format of the output after the change to German.
SELECT last_name, hire_date, ROUND(salary/8,2) salary FROM employees WHERE employee_id IN (111, 112, 113);
The output from the example should be similar to the following. Note that the abbreviations for month names are now in German.
LAST_NAME HIRE_DATE SALARY ------------------------- --------- ---------- Sciarra 30-SEP-97 962.5 Urman 07-MRZ-98 975 Popp 07-DEZ-99 862.5
Set NLS_LANGUAGE
back to its original setting listed in Step 1. For example:
ALTER SESSION SET NLS_LANGUAGE=AMERICAN;
See Also:
Oracle Database Reference for more information on the NLS_LANGUAGE
parameter
Oracle Database Globalization Support Guide for locale information, including supported languages and territories
The NLS_TERRITORY
parameter can be set to any valid territory name. The default is derived from the NLS_LANG
setting. NLS_TERRITORY
specifies the conventions for the following default date and numeric formatting characteristics:
Date format
Decimal character and group separator
Local currency symbol
ISO currency symbol
Dual currency symbol
Modifying the NLS_TERRITORY
parameter resets all derived NLS session parameters to default values for the new territory.
To set the NLS_TERRITORY parameter:
You can change the NLS_LANGUAGE
parameter value and see the effect in the display of results from a query. The following examples show the effect of setting NLS_TERRITORY
to Germany.
In SQL Developer, enter a SELECT
statement to check the format of the output with the initial SQL Developer default settings.
SELECT TO_CHAR(salary,'L99G999D99') salary FROM employees WHERE employee_id IN (100, 101, 102);
For example, if NLS_TERRITORY
is AMERICA
, the output from the example is similar to the following.
SALARY -------------------- $24,000.00 $17,000.00 $17,000.00 3 rows selected
Make a note of the current territory in which Oracle Database was installed.
Under Connections, expand Data Dictionary reports, then About Your Database, and then National Language Support Parameters. In the Select Connection dialog box, select hr_conn
from the list of connections. The current territory is listed after NLS_TERRITORY
.
Set NLS_TERRITORY
to Germany.
ALTER SESSION SET NLS_TERRITORY=GERMANY;
Enter the same SELECT
statement to check the format of the output after the change.
SELECT TO_CHAR(salary,'L99G999D99') salary FROM employees WHERE employee_id IN (100, 101, 102);
The output from the example should be similar to the following. The thousands separator changed to a period (.) and the decimal character changed to a comma (,). The currency symbol changed from dollars ($
) to euros (€). However, the numbers have not changed because the underlying data is the same. (That is, currency exchange rates are not factored in.)
SALARY -------------------- €24.000,00 €17.000,00 €17.000,00 3 rows selected
Set NLS_TERRITORY
back to its original setting listed in Step 2. For example:
ALTER SESSION SET NLS_TERRITORY=AMERICA;
See Also:
Oracle Database Reference for more information on the NLS_TERRITORY
parameter
Oracle Database Globalization Support Guide for locale information, including supported languages and territories
You can control the display of the date and time, allowing different conventions for displaying the hour, day, month, and year based on the local formats. For example, in the United Kingdom, the date is displayed using the DD/MM/YYYY
format, while China commonly uses the YYYY-MM-DD
format.
See Also:
Oracle Database Globalization Support Guide for information about date/time data types and time zone support
Oracle Database SQL Language Reference for information about date and time formats
You can use several different date formats, as shown in the following table.
Country | Description | Example |
---|---|---|
Estonia | dd.mm.yyyy | 28.02.2005 |
Germany | dd.mm.rr | 28.02.05 |
China | yyyy-mm-dd | 2005-02-28 |
UK | dd/mm/yyyy | 28/02/2005 |
U.S. | mm/dd/yyyy | 02/28/2005 |
To use the NLS_DATE_FORMAT parameter:
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 the NLS_DATE_FORMAT
parameter. The value of NLS_DATE_FORMAT
can be any valid date format model. For example:
NLS_DATE_FORMAT = "MM/DD/YYYY"
To add string literals to the date format, enclose the string literal with double quotation marks. Note that when double quotation marks are included in the date format, the entire value must be enclosed by single quotation marks. For example:
NLS_DATE_FORMAT = '"Date: "MM/DD/YYYY'
The Oracle default date format may not always correspond to the cultural-specific convention used in a given territory. You can use the short date and long date format in SQL, using the 'DS'
and 'DL'
format models, respectively, to obtain dates in localized formats. The following example shows the use of the short and long date formats.
In SQL Developer, make a note of the current territory and date format in which Oracle Database was installed.
Under Connections, expand Data Dictionary reports, then About Your Database, and then National Language Support Parameters. In the Select Connection dialog box, select hr_conn
from the list of connections. The current date format is listed after NLS_DATE_FORMAT
and the current territory is listed after NLS_TERRITORY
.
Set NLS_TERRITORY
to America.
ALTER SESSION SET NLS_TERRITORY = America;
Select the dates using the format models.
SELECT hire_date, TO_CHAR(hire_date,'DS') "Short", TO_CHAR(hire_date,'DL') "Long" FROM employees WHERE employee_id IN (111, 112, 113);
The output from the example should be similar to the following.
HIRE_DATE Short Long ---------------- -------- ----------------------------------- 30-SEP-97 9/30/1997 Tuesday, September 30, 1997 07-MAR-98 3/7/1998 Saturday, March 07, 1998 07-DEC-99 12/7/1999 Tuesday, December 07, 1999
Set NLS_TERRITORY
and NLS_DATE_FORMAT
back to their original settings listed in Step 1. For example:
ALTER SESSION SET NLS_TERRITORY=AMERICA; ALTER SESSION SET NLS_DATE_FORMAT="MM/DD/YYYY";
To use the NLS_DATE_LANGUAGE parameter:
The NLS_DATE_LANGUAGE
parameter specifies the language for the day and month produced by the TO_CHAR
and TO_DATE
functions. NLS_DATE_LANGUAGE
overrides the language that is specified implicitly by NLS_LANGUAGE
. The NLS_DATE_LANGUAGE
parameter has the same syntax as the NLS_LANGUAGE
parameter, and all supported languages are valid values.
The NLS_DATE_LANGUAGE
parameter also determines the language used for:
Month and day abbreviations returned by the TO_CHAR
and TO_DATE
functions
Month and day abbreviations used by the default date format (NLS_DATE_FORMAT
)
The default date format uses the month abbreviations determined by the NLS_DATE_LANGUAGE
parameter. For example, if the default date format is DD-MON-YYYY
and NLS_DATE_LANGUAGE
=
FRENCH
, then insert a date as follows:
INSERT INTO
table_name
VALUES ('12-Févr.-2007');
The following example shows the effect of setting NLS_DATE_LANGUAGE
to French.
In SQL Developer, make a note of the current day and month language in which Oracle Database was installed.
Under Connections, expand Data Dictionary reports, then About Your Database, and then National Language Support Parameters. In the Select Connection dialog box, select hr_conn
from the list of connections. The current day and month language is listed after NLS_DATE_LANGUAGE
.
Set NLS_DATE_LANGUAGE
to French.
ALTER SESSION SET NLS_DATE_LANGUAGE = FRENCH;
Select the current system date.
SELECT TO_CHAR(SYSDATE, 'Day:Dd Month yyyy') FROM DUAL
The output from the example should be similar to the following.
TO_CHAR(SYSDATE,'DAY:DDMONTHYYYY') ---------------------------------- Lundi :05 Mars 2007
Set NLS_DATE_LANGUAGE
back to its original setting listed in Step 1. For example:
ALTER SESSION SET NLS_DATE_LANGUAGE=AMERICAN;
See Also:
Oracle Database Reference for more information on the NLS_DATE_FORMAT
parameter
Oracle Database Reference for more information on the NLS_DATE_LANGUAGE
parameter
Oracle Database SQL Language Reference for information about date format models
Oracle Database Globalization Support Guide for information about date/time data types and time zone support
This section shows how to use the NLS_TIMESTAMP_FORMAT
and NLS_TIMESTAMP_TZ_FORMAT
parameters. Some of the time format examples are in the following table.
Country | Description | Example |
---|---|---|
Estonia | hh24:mi:ss | 13:50:23 |
Germany | hh24:mi:ss | 13:50:23 |
China | hh24:mi:ss | 13:50:23 |
UK | hh24:mi:ss | 13:50:23 |
U.S. | hh:mi:ssxff am | 1:50:23.555 PM |
The NLS_TIMESTAMP_FORMAT
parameter defines the default date format for the TIMESTAMP
and TIMESTAMP WITH LOCAL TIME ZONE
data types. The NLS_TERRITORY
parameter determines the default value of NLS_TIMESTAMP_FORMAT
. The value of NLS_TIMESTAMP_FORMAT
can be any valid datetime format model.
The following example shows a value for NLS_TIMESTAMP_FORMAT
:
NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF'
The NLS_TIMESTAMP_TZ_FORMAT
parameter defines the default date format for the TIMESTAMP
and TIMESTAMP WITH LOCAL TIME ZONE
data types. It is used with the TO_CHAR
and TO_TIMESTAMP_TZ
functions. The NLS_TERRITORY
parameter determines the default value of the NLS_TIMESTAMP_TZ_FORMAT
parameter. The value of NLS_TIMESTAMP_TZ_FORMAT
can be any valid datetime format model.
The format value must be surrounded by quotation marks. For example:
NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF TZH:TZM'
To set and use NLS_TIMESTAMP_TZ:
The following example sets the NLS_TIMESTAMP_TZ_FORMAT value. It also shows the format being set explicitly in a SELECT statement, using the TO_TIMESTAMP_TZ
function.
In SQL Developer, make a note of the current time format in which Oracle Database was installed.
Under Connections, expand Data Dictionary reports, then About Your Database, and then National Language Support Parameters. In the Select Connection dialog box, select hr_conn
from the list of connections. The current time format is listed after NLS_TIMESTAMP_TZ_FORMAT
.
Set NLS_TIMESTAMP_TZ_FORMAT
.
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF TZH:TZM';
Set NLS_TIMESTAMP_TZ_FORMAT
back to its original setting listed in Step 1. For example:
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH.MI.SSXFF AM TZR';
See Also:
Oracle Database Reference for more information on the NLS_TIMESTAMP_TZ_FORMAT
parameter
Oracle Database SQL Language Reference for information about date format models
Oracle Database Globalization Support Guide for information about date/time data types and time zone support
This section describes calendar definition.
See Also:
Oracle Database Globalization Support Guide for locale information, including supported calendars
The following calendar information is stored for each territory:
First Day of the Week: Some cultures consider Sunday to be the first day of the week; others consider Monday to be the first day of the week.
The first day of the week is determined by the NLS_TERRITORY
parameter.
First Calendar Week of the Year: 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
2005
is in ISO week number 53 of 2004. An ISO week starts on Monday and ends on Sunday.
To support the ISO standard, Oracle provides the IW date format element. It returns the ISO week number.
The first calendar week of the year is determined by the NLS_TERRITORY
parameter.
Number of Days and Months in a Year: Oracle supports six calendar systems in addition to the Gregorian calendar, which is the default. These additional calendar systems are:
Japanese Imperial uses the same number of months and days as the Gregorian calendar, but the year starts with the beginning of each Imperial Era.
ROC Official uses the same number of months and days as the Gregorian calendar, but the year starts with the founding of the Republic of China.
Persian has 31 days for each of the first 6 months. The next 5 months have 30 days each. The last month has either 29 days or 30 days (leap year).
Thai Buddha uses a Buddhist calendar.
Arabic Hijrah has 12 months and 354 or 355 days.
English Hijrah has 12 months and 354 or 355 days.
The calendar system is specified by the NLS_CALENDAR
parameter.
First Year of Era: The Islamic calendar starts from the year of the Hegira. The Japanese Imperial calendar starts from the beginning of an Emperor's reign. For example, 1998 is the tenth year of the Heisei era.
See Also:
Oracle Database Globalization Support Guide for locale information, including supported calendars
Many different calendar systems are in use throughout the world. The NLS_CALENDAR
parameter specifies which calendar system Oracle Database uses. The default value is Gregorian
. The value can be any valid calendar format name.
The NLS_CALENDAR
parameter can have one of the following values:
Arabic Hijrah
English Hijrah
Gregorian
Japanese Imperial
Persian
ROC Official (Republic of China)
Thai Buddha
To set the NLS_CALENDAR parameter:
The following example sets the NLS_CALENDAR value to English Hijrah, and it displays the value for the first day of Ramadan in the year 1424 H. The other NLS parameters reflect the default SQL Developer settings.
In SQL Developer, make a note of the current calendar format in which Oracle Database was installed.
Under Connections, expand Data Dictionary reports, then About Your Database, and then National Language Support Parameters. In the Select Connection dialog box, select hr_conn
from the list of connections. The current date format is listed after NLS_DATE_FORMAT
and the current territory is listed after NLS_CALENDAR
.
Set NLS_CALENDAR
to English Hijrah.
ALTER SESSION SET NLS_CALENDAR='English Hijrah';
Display the start of Ramadan for the year 1424 H (in Gregorian calendar year 2007).
SELECT TO_DATE('01-Ramadan-1428') FROM DUAL;
The output from the example should be similar to the following.
TO_DATE('01-RAMADAN-1428') ------------------------- 13 September 2007
Set NLS_CALENDAR
back to its original setting listed in Step 1. For example:
ALTER SESSION SET NLS_CALENDAR='GREGORIAN';
See Also:
Oracle Database Reference for more information on the NLS_CALENDAR
parameter
Oracle Database Globalization Support Guide for locale information, including supported calendars
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 the following table.
Country | Numeric Formats |
---|---|
Estonia | 1 234 567,89 |
Germany | 1.234.567,89 |
China | 1,234,567.89 |
UK | 1,234,567.89 |
U.S. | 1,234,567.89 |
Numeric formats are derived from the NLS_TERRITORY
parameter setting, but they can be overridden by the NLS_NUMERIC_CHARACTERS
parameter.
See Also:
Oracle Database Globalization Support Guide for a complete discussion of setting up the globalization support environment
The NLS_NUMERIC_CHARACTERS
parameter specifies the group separator and decimal character. 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 model. The decimal character separates the integer and decimal parts of a number. Setting the NLS_NUMERIC_CHARACTERS
parameter overrides the default values derived from the setting of NLS_TERRITORY
. The value can be any two valid numeric characters for the group separator and decimal character.
Any character can be the decimal character or group separator. The two characters specified must be single-byte, and the characters must be different from each other. The characters cannot be a numeric character or any of the following characters: plus sign (+), minus sign (-), less than sign (<), greater than sign (>). Either character can be a space.
To set the decimal character to a comma and the grouping separator to a period, specify the NLS_NUMERIC_CHARACTERS
parameter as follows:
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ",.";
SQL statements can include numbers represented as numeric or text literals. Numeric literals are not enclosed in quotation marks. They are part of the SQL language syntax, and always use a period as the decimal character and never contain a group separator. Text literals are enclosed in single quotation marks. They are implicitly or explicitly converted to numbers, if required, according to the current NLS settings.
To set the NLS_NUMERIC_CHARACTERS parameter:
The following example formats 4000 with the group separator and decimal character specified in the ALTER SESSION
statement.
In SQL Developer, make a note of the current numeric characters format in which Oracle Database was installed.
Under Connections, expand Data Dictionary reports, then About Your Database, and then National Language Support Parameters. In the Select Connection dialog box, select hr_conn
from the list of connections. The current numeric characters format is listed after NLS_NUMERIC_CHARACTERS
.
Set NLS_NUMERIC_CHARACTERS
to the specified group separator and decimal character.
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ",.";
Use double quotation marks.
Display 4000 using the format mask '
9G999D99'
.
SELECT TO_CHAR(4000, '9G999D99') FROM DUAL;
The output from the example should be similar to the following. The group separator is the period (.) and the decimal character is the comma (,).
TO_CHAR(4000,'9G999D99') ------------------------ 4.000,00
Set NLS_NUMERIC_CHARACTERS
back to its original setting listed in Step 1. For example:
ALTER SESSION SET NLS_NUMERIC_CHARACTERS=". ";
See Also:
Oracle Database Reference for more information on the NLS_NUMERIC_CHARACTERS
parameter
Oracle Database Globalization Support Guide for a complete discussion of setting up the globalization support environment
You can define radix symbols and thousands separators by locales. For example, in the U.S., the decimal point is a period (.), while it is a comma (,) in France. Because $1,234 has different meanings in different countries, it is important to display the amount appropriately by locale.
See Also:
Oracle Database Globalization Support Guide for a complete discussion of setting up the globalization support environment, including monetary parameters
Different currency formats are used throughout the world. Some typical formats are shown in the following table.
Country | Currency Format |
---|---|
Estonia | 1 234,56 kr |
Germany | 1.234,56€ |
China | ¥1,234.56 |
UK | £1,234.56 |
U.S. | $1,234.56 |
See Also:
Oracle Database Globalization Support Guide for a complete discussion of setting up the globalization support environment, including monetary parameters
The NLS_CURRENCY
parameter specifies the character string returned by the L
number format model, the local currency symbol. Setting NLS_CURRENCY
overrides the default setting defined implicitly by NLS_TERRITORY
. The value can be any valid currency symbol string.
To see the effect of changing the NLS_CURRENCY value:
The following example displays salary amounts using a format that includes the L
number format model, which is replaced by the NLS_CURRENCY value.
Display salaries with a value greater than eleven thousand.
SELECT TO_CHAR(salary, 'L099G999D99') "salary" FROM employees WHERE salary > 11000
The output from the example should be similar to the following. The dollar sign ($) is the L
number format model in this case.
salary --------------------- $024,000.00 $017,000.00 $017,000.00 $012,000.00 $014,000.00 $013,500.00 $012,000.00 $011,500.00 $013,000.00 $012,000.00 10 rows selected
See Also:
Oracle Database Reference for more information on the NLS_CURRENCY
parameter.
Oracle Database Globalization Support Guide for a complete discussion of setting up the globalization support environment, including monetary parameters
The NLS_ISO_CURRENCY
parameter specifies the character string returned by the C
number format model, the ISO currency symbol. Setting NLS_ISO_CURRENCY
overrides the default value defined implicitly by NLS_TERRITORY
. The value can be any valid string.
Local currency symbols can be ambiguous. For example, a dollar sign ($) can refer to U.S. dollars or Australian dollars. ISO specifications define unique currency symbols for specific territories or countries. For example, the ISO currency symbol for the U.S. dollar is USD, and the ISO currency symbol for the Australian dollar is AUD.
The NLS_ISO_CURRENCY
parameter has the same syntax as the NLS_TERRITORY
parameter, and all supported territories are valid values.
To set the NLS_ISO_CURRENCY parameter:
The following example sets the ISO currency symbol for France, and formats salaries using the appropriate format model.
In SQL Developer, make a note of the ISO currency format in which Oracle Database was installed.
Under Connections, expand Data Dictionary reports, then About Your Database, and then National Language Support Parameters. In the Select Connection dialog box, select hr_conn
from the list of connections. The current ISO currency format is listed after NLS_ISO_CURRENCY
.
Set NLS_ISO_CURRENCY
to France.
ALTER SESSION SET NLS_ISO_CURRENCY=FRANCE;
Display selected salaries using that format.
SELECT TO_CHAR(salary, 'C099G999D99') "Salary" FROM employees WHERE department_id = 60;
The output from the example should be similar to the following.
Salary ------------------ EUR009,000.00 EUR006,000.00 EUR004,800.00 EUR004,800.00 EUR004,200.00 5 rows selected
Set NLS_ISO_CURRENCY
back to its original setting listed in Step 1. For example:
ALTER SESSION SET NLS_ISO_CURRENCY=AMERICA;
See Also:
Oracle Database Reference for more information on the NLS_ISO_CURRENCY
parameter
Oracle Database Globalization Support Guide for a complete discussion of setting up the globalization support environment, including monetary parameters
Use the NLS_DUAL_CURRENCY
parameter to override the default dual currency symbol defined implicitly by NLS_TERRITORY
. The value can be any valid symbol.
NLS_DUAL_CURRENCY
was introduced to support the euro currency symbol during the euro transition period.
See Also:
Oracle Database Reference for more information on the NLS_DUAL_CURRENCY
parameter
Oracle Database Globalization Support Guide for a complete discussion of setting up the globalization support environment, including monetary parameters
Different languages have their own sorting rules. Some languages are collated according to the letter sequence in the alphabet, some according to the number of stroke counts in the letter, and some are ordered by the pronunciation of the words. Treatment of letter accents also differs among languages. For example, in Danish, Æ is sorted after Z, while Y and Ü are considered to be variants of the same letter.
You can define how to sort data by using linguistic sort parameters. The basic linguistic definition treats strings as sequences of independent characters.
See Also:
Oracle Database Globalization Support Guide for a complete discussion of linguistic sort and string searching
The NLS_SORT
parameter specifies the collating (linguistic sort) sequence for ORDER
BY
queries. It overrides the default NLS_SORT
value that is derived from the NLS_LANGUAGE
parameter. The value of NLS_SORT
can be BINARY
or any valid linguistic sort name:
NLS_SORT
=
BINARY
|
sort_name
If the value is BINARY
, then the collating sequence is based on the numeric code of the characters in the underlying encoding scheme. Depending on the data type, this will either be in the binary sequence order of the database character set or the national character set. If the value is a named linguistic sort, sorting is based on the order of the defined sort. Most, but not all, languages supported by the NLS_LANGUAGE
parameter also support a linguistic sort with the same name.
To set the NLS_SORT parameter:
You can change the NLS_SORT
parameter value and see the effect in the display of results from a query. The following examples show the effect of setting NLS_SORT
first to Binary and then to Spanish (SPANISH_M
). Spain traditionally treats ch, ll, and ñ as letters of their own, ordered after c, l, and n, respectively.
In SQL Developer, make a note of the current collating format in which Oracle Database was installed.
Under Connections, expand Data Dictionary reports, then About Your Database, and then National Language Support Parameters. In the Select Connection dialog box, select hr_conn
from the list of connections. The current collating format is listed after NLS_SORT
.
Set NLS_SORT
to binary.
ALTER SESSION SET NLS_SORT=BINARY;
Enter a SELECT
statement with an ORDER BY
clause, to check the output after the change.
SELECT last_name FROM employees WHERE last_name LIKE 'C%' ORDER BY last_name;
The output from the example should be similar to the following.
LAST_NAME ------------------------- Cabrio Cambrault Cambrault Chen Chung Colmenares 6 rows selected
Set NLS_SORT
to SPANISH_M
.
ALTER SESSION SET NLS_SORT=spanish_m;
Enter the same SELECT
statement, to check the output after the change.
SELECT last_name FROM employees WHERE last_name LIKE 'C%' ORDER BY last_name;
The output from the example should be similar to the following. Note that Colmenares now comes before Chen.
LAST_NAME ------------------------- Cabrio Cambrault Cambrault Colmenares Chen Chung 6 rows selected
Set NLS_SORT
back to its original setting listed in Step 1. For example:
ALTER SESSION SET NLS_SORT=BINARY;
See Also:
Oracle Database Reference for more information on the NLS_SORT
parameter
Oracle Database Globalization Support Guide for a complete discussion of linguistic sort and string searching
When using comparison operators, characters are compared according to their binary codes in the designated encoding scheme. A character is greater than another if it has a higher binary code. Because the binary sequence of characters may not match the linguistic sequence for a particular language, those comparisons might not be linguistically correct.
The value of the NLS_COMP
parameter affects the comparison behavior of SQL operations. The value can be BINARY
(default) or LINGUISTIC
. You can use the NLS_COMP
parameter to avoid the cumbersome process of using the NLSSORT
function in SQL statements when you want to perform a linguistic comparison instead of a binary comparison. When NLS_COMP
is set to LINGUISTIC
, SQL performs a linguistic comparison based on the value of the NLS_SORT
parameter.
To set the NLS_COMP parameter:
You can change the NLS_COMP
parameter value and see the effect in the display of results from a query. The following examples show the effect of performing a binary comparison followed by a Spanish linguistic sensitive comparison against the employee names.
In SQL Developer, make a note of the current comparison operators format in which Oracle Database was installed.
Under Connections, expand Data Dictionary reports, then About Your Database, and then National Language Support Parameters. In the Select Connection dialog box, select hr_conn
from the list of connections. The current comparison operators format is listed after NLS_COMP
.
Set NLS_SORT
to Spanish and NLS_COMP
to BINARY
.
ALTER SESSION SET NLS_SORT=spanish_m NLS_COMP=binary;
Enter a SELECT
statement to return employees whose last name starts with C
.
SELECT last_name FROM employees WHERE last_name LIKE 'C%';
The output from the example should be similar to the following.
LAST_NAME ------------------------- Cabrio Cambrault Cambrault Chen Chung Colmenares 6 rows selected
Set NLS_COMP
to LINGUISTIC
.
ALTER SESSION SET NLS_COMP=linguistic;
Enter the same SELECT
statement, to check the output after the change.
SELECT last_name FROM employees WHERE last_name LIKE 'C%';
The output from the example should be similar to the following. Note that two fewer rows are returned this time. Chen and Chung are not returned because in Spanish ch
is treated as a separate character that follows c
, so ch
is excluded when a Spanish linguistic-sensitive comparison is performed.
LAST_NAME ------------------------- Cabrio Cambrault Cambrault Colmenares 4 rows selected
Set NLS_COMP
back to its original setting listed in Step 1. For example:
ALTER SESSION SET NLS_COMP=BINARY;
See Also:
Oracle Database Reference for more information on the NLS_COMP
parameter.
Oracle Database Globalization Support Guide for a complete discussion of linguistic sort and string searching
Operations inside a database are sensitive to the case and the accents of the characters. Sometimes, you might need to perform case-insensitive or accent-insensitive comparisons. Use the NLS_SORT
session parameter to specify a case-insensitive or accent-insensitive sort.
To specify a case-insensitive or accent-insensitive sort:
Append _CI
to an Oracle sort name for a case-insensitive sort. For example:
BINARY_CI
: accent-sensitive and case-insensitive binary sort
GENERIC_M_CI
: accent-sensitive and case-insensitive GENERIC_M
sort
Append _AI
to an Oracle sort name for an accent-insensitive and case-insensitive sort. For example:
BINARY_AI
: accent-insensitive and case-insensitive binary sort
FRENCH_M_AI
: accent-insensitive and case-insensitive FRENCH_M
sort
See Also:
Oracle Database Reference for more information on the NLS_SORT
parameter.
Oracle Database Globalization Support Guide for a complete discussion of linguistic sort and string searching
In single-byte character sets, the number of bytes and the number of characters in a string are the same. In multibyte character sets, a character or code point consists of one or more bytes. Calculating the number of characters based on byte length can be difficult in a variable-width character set. Calculating column length in bytes is called byte semantics, while measuring column length in characters is called character semantics.
Character semantics is useful to define the storage requirements for multibyte strings of varying widths. For example, in a Unicode database (AL32UTF8), suppose that you need to define a VARCHAR2
column that can store up to five Chinese characters together with five English characters. Using byte semantics, this column requires 15 bytes for the Chinese characters, which are 3 bytes long, and 5 bytes for the English characters, which are 1 byte long, for a total of 20 bytes. Using character semantics, the column requires 10 characters.
The expressions in the following list use byte semantics. Note the BYTE
qualifier in the VARCHAR2
expression and the B
suffix in the SQL function name.
VARCHAR2(20 BYTE)
SUBSTRB(
string
, 1, 20)
The expressions in the following list use character semantics. Note the CHAR
qualifier in the VARCHAR2
expression.
VARCHAR2(20 CHAR)
SUBSTR(
string
, 1, 20)
See Also:
Oracle Database Globalization Support Guide for a complete discussion of choosing or changing a character set, including length semantics
The NLS_LENGTH_SEMANTICS
parameter specifies BYTE
(default) or CHAR
semantics. By default, the character data types CHAR
and VARCHAR2
are specified in bytes, not characters. Therefore, the specification CHAR(20)
in a table definition allows 20 bytes for storing character data.
The NLS_LENGTH_SEMANTICS
parameter enables you to create CHAR
, VARCHAR2
, and LONG
columns using either byte-length or character-length semantics. NCHAR
, NVARCHAR2
, CLOB
, and NCLOB
columns are always character-based. Existing columns are not affected.
To set the NLS_LENGTH_SEMANTICS parameter:
In SQL Developer, make a note of the current semantics format in which Oracle Database was installed.
Under Connections, expand Data Dictionary reports, then About Your Database, and then National Language Support Parameters. In the Select Connection dialog box, select hr_conn
from the list of connections. The current semantics format is listed after NLS_LENGTH_SEMANTICS
.
Set NLS_LENGTH_SEMANTICS
to BYTE
.
ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;
Create the following table:
CREATE TABLE SEMANTICS_BYTE(SOME_DATA VARCHAR2(20));
Check the data types of table SEMANTICS_BYTE
.
Select the Connections tab, and then expand the hr_conn
connection to display all the tables under Tables. Select the SEMANTICS_BYTE
table. The data type for its SOME_DATA
column is listed as VARCHAR2(20 BYTE)
.
Set NLS_LENGTH_SEMANTICS
to CHAR
.
ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR;
Create the following table:
CREATE TABLE SEMANTICS_CHAR(SOME_DATA VARCHAR2(20));
Check the data types of table SEMANTICS_CHAR
.
Select the Connections tab, and then expand the hr_conn
connection to display all the tables under Tables. Select the SEMANTICS_CHAR
table. The data type for its SOME_DATA
column is listed as VARCHAR2(20 CHAR)
.
Drop the SEMANTICS_BYTE
and SEMANTICS_CHAR
tables.
In the Tables navigation hierarchy, right-click the name of each table, and from the menu, select Table, and then Drop. Click Apply, and then in the confirmation dialog box, click OK.
Set NLS_LENGTH_SEMANTICS
back to its original setting listed in Step 1. For example:
ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;
See Also:
Oracle Database Reference for more information on the NLS_LENGTH_SEMANTICS
parameter.
Oracle Database Globalization Support Guide for a complete discussion of choosing or changing a character set, including length semantics
This section describes Unicode-related features in SQL and PL/SQL that you can deploy for multiple language applications. You can insert and retrieve Unicode data. Data is transparently converted among the database and client programs, which ensures that client programs are independent of the database character set and national character set.
See Also:
Oracle Database Globalization Support Guide for a complete discussion of programming with Unicode
Unicode is a universal encoded character set that enables you to store information in any language, using a single character set. Unicode provides a unique code value for every character, regardless of the platform, program, or language.
Unicode has the following advantages:
It simplifies character set conversion and linguistic sort functions.
It improves performance compared with native multibyte character sets.
It supports the Unicode data type based on the Unicode standard.
You can store Unicode characters in an Oracle database in two ways:
You can create a Unicode database that enables you to store UTF-8 encoded characters as SQL CHAR
data types.
You can support multiple language data in specific columns by using Unicode data types. You can store Unicode characters into columns of the SQL NCHAR
data types regardless of how the database character set has been defined. The NCHAR
data type is an exclusively Unicode data type.
See Also:
Oracle Database Globalization Support Guide for a complete discussion of programming with Unicode
There are two SQL NCHAR
data types: NCHAR
and NVARCHAR2
.
In SQL Developer, you can specify these data types in the dialog box for creating or editing a table, by selecting the appropriate value for Type for each column. You can also use the SQL Worksheet to enter a CREATE TABLE
statement and specify each column name and data type.
See Also:
Oracle Database Globalization Support Guide for a complete discussion of programming with Unicode
When you define a table column or a PL/SQL variable as the NCHAR
data type, the length is specified as the number of characters. For example, the following statement creates a column with a maximum length of 30 characters:
CREATE TABLE table1 (column1 NCHAR(30));
The maximum number of bytes in a column is the product of the maximum number of characters and the maximum number of bytes for each character.
For example, if the national character set is UTF8, then the maximum byte length is 30 characters times 3 bytes for each character, or 90 bytes.
The national character set, which is used for all NCHAR
data types, is defined when the database is created. The national character set can be either UTF8 or AL16UTF16. The default is AL16UTF16.
The maximum column size allowed is 2000 characters when the national character set is UTF8 and 1000 when it is AL16UTF16. The actual data is subject to the maximum byte limit of 2000. The two size constraints must be satisfied at the same time. In PL/SQL, the maximum length of the NCHAR
data is 32,767 bytes. You can define an NCHAR
variable of up to 32,767 characters, but the actual data cannot exceed 32,767 bytes. If you insert a value that is shorter than the column length, then Oracle pads the value with blanks to whichever length is smaller: maximum character length or maximum byte length.
See Also:
Oracle Database Globalization Support Guide for a complete discussion of programming with Unicode
The NVARCHAR2
data type specifies a variable-length character string that uses the national character set. When you create a table with an NVARCHAR2
column, you specify the maximum number of characters for the column. Lengths for NVARCHAR2
are in units of characters, just as for NCHAR
. Oracle Database subsequently stores each value in the column exactly as you specify it, if the value does not exceed the maximum length of the column. It does not pad the string value to the maximum length.
The maximum column size allowed is 4000 characters when the national character set is UTF8, and it is 2000 when AL16UTF16. The maximum length of an NVARCHAR2
column in bytes is 4000. Both the byte limit and the character limit must be met, so the maximum number of characters that is allowed in an NVARCHAR2
column is the number of characters that can be written in 4000 bytes.
In PL/SQL, the maximum length for an NVARCHAR2
variable is 32,767 bytes. You can define NVARCHAR2
variables up to 32,767 characters, but the actual data cannot exceed 32,767 bytes.
The following statement creates a table with one NVARCHAR2
column whose maximum length in characters is 2000 and maximum length in bytes is 4000.
CREATE TABLE table2 (column2 NVARCHAR2(2000));
See Also:
Oracle Database Globalization Support Guide for a complete discussion of programming with Unicode
You can input Unicode string literals in SQL and PL/SQL as follows:
Put the letter N
before a string literal that is enclosed with single quotation marks. This explicitly indicates that the following string literal is an NCHAR
string literal. For example, N'résumé'
is an NCHAR
string literal. See "NCHAR Literal Replacement" for limitations of this method.
Use the NCHR(
n
)
SQL function. The NCHR(
n
)
SQL function returns a unit of character code in the national character set, which is AL16UTF16 or UTF8. The result of concatenating several NCHR(
n
)
functions is NVARCHAR2
data. In this way, you can bypass the client and server character set conversions and create an NVARCHAR2
string directly. For example, NCHR(32)
represents a blank character.
Because NCHR(
n
)
is associated with the national character set, portability of the resulting value is limited to applications that use the same national character set. If this is a concern, then use the UNISTR
function to remove portability limitations.
Use the UNISTR
('string'
)
SQL function. The UNISTR
('string'
)
function converts a string to the national character set. To ensure portability and to preserve data, include only ASCII characters and Unicode encoding in the following form: \xxxx
, where xxxx
is the hexadecimal value of a character code value in UTF-16 encoding format. For example, UNISTR('G\0061ry')
represents 'Gary'
. The ASCII characters are converted to the database character set and then to the national character set. The Unicode encoding is converted directly to the national character set.
The last two methods can be used to encode any Unicode string literals.
See Also:
Oracle Database Globalization Support Guide for a complete discussion of programming with Unicode
As part of a SQL or PL/SQL statement, the text of any literal, with or without the prefix N
, is encoded in the same character set as the rest of the statement. On the client side, the statement is in the client character set, determined by the character set defined in the NLS_LANG
parameter. On the server side, the statement is in the database character set.
When the SQL or PL/SQL statement is transferred from client to the database, its character set is converted accordingly. If the database character set does not contain all characters used in the text literals, data is lost in this conversion. This affects NCHAR
string literals more than the CHAR
text literals, because the N'
literal is designed to be independent of the database character set, and it should be able to include any data that the client character set allows.
To avoid data loss during conversion to an incompatible database character set, you can use NCHAR
literal replacement by setting the client environment variable ORA_NCHAR_LITERAL_REPLACE
to TRUE
. This causes N literals on the client side to be replaced by an internal format, which the database decodes to Unicode when the statement is executed. By default, NCHAR
literal replacement is disabled, to maintain backward compatibility.
See Also:
Oracle Database Globalization Support Guide for a complete discussion of programming with Unicode
All SQL functions whose behavior depends on globalization support conventions allow NLS parameters to be specified. These functions are TO_CHAR
, TO_DATE
, TO_NUMBER
, NLS_UPPER
, NLS_LOWER
, NLS_INITCAP
, and NLSSORT
.
Specifying NLS parameters for these functions enables the functions to be evaluated independently of the session's NLS parameters. This feature can be important for SQL statements that contain numbers and dates as string literals.
For example, there are two ways to ensure that the following query is evaluated so that the language specified for dates is AMERICAN
.
Use ALTER SESSION
to set the NLS_DATE_LANGUAGE
and NLS_CALENDAR
parameters.
ALTER SESSION SET NLS_DATE_LANGUAGE=American; SELECT last_name FROM employees WHERE hire_date > '01-JAN-1999';
Specify the NLS_DATE_LANGUAGE
function in the TO_DATE
function in the WHERE
clause of the SQL statement.
SELECT last_name FROM employees WHERE hire_date > TO_DATE('01-JAN-1999','DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN');
This way, SQL statements that are independent of the session language can be defined where necessary. These statements are necessary when string literals appear in SQL statements in views, CHECK
constraints, or triggers.
Only SQL statements that must be independent of the session NLS parameter values should explicitly specify optional NLS parameters in locale-dependent SQL functions. Using session default values for NLS parameters in SQL functions usually results in better performance.
All character functions support both single-byte and multibyte characters. Except where explicitly stated, character functions operate character by character, rather than byte by byte.
When SQL functions evaluate views and triggers, default values from the current session are used for the NLS function parameters. When SQL functions evaluate CHECK
constraints, they use the default values that were specified for the NLS parameters when the database was created.
See Also:
Oracle Database Globalization Support Guide for a complete discussion of locale-dependent SQL functions with optional NLS parameters
NLS parameters are specified in SQL functions as 'parameter
=
value
'. For example:
'NLS_DATE_LANGUAGE = AMERICAN'
You can specify the following NLS parameters in SQL functions:
NLS_DATE_LANGUAGE NLS_NUMERIC_CHARACTERS NLS_CURRENCY NLS_ISO_CURRENCY NLS_DUAL_CURRENCY NLS_CALENDAR NLS_SORT
In some languages, some lowercase characters correspond to more than one uppercase character, or some uppercase characters correspond to more than one lowercase characters. As a result, the length of the output from the NLS_UPPER
, NLS_LOWER
, and NLS_INITCAP
functions can differ from the length of the input. The following table shows which NLS parameters are valid for specific SQL functions.
SQL Function | Valid NLS Parameters |
---|---|
TO_DATE |
NLS_DATE_LANGUAGE, NLS_CALENDAR |
TO_NUMBER |
NLS_NUMERIC_CHARACTERS, NLS_CURRENCY, NLS_ISO_CURRENCY, NLS_DUAL_CURRENCY, |
TO_CHAR |
NLS_DATE_LANGUAGE, NLS_NUMERIC_CHARACTERS, NLS_CURRENCY, NLS_ISO_CURRENCY, NLS_DUAL_CURRENCY, NLS_CALENDAR |
TO_NCHAR |
NLS_DATE_LANGUAGE, NLS_NUMERIC_CHARACTERS, NLS_CURRENCY, NLS_ISO_CURRENCY, NLS_DUAL_CURRENCY, NLS_CALENDAR |
NLS_UPPER |
NLS_SORT |
NLS_LOWER |
NLS_SORT |
NLS_INITCAP |
NLS_SORT |
NLSSORT |
NLS_SORT |
Example 6-1 shows several SELECT
statements that demonstrate how to use NLS parameters in SQL functions. After you perform these SELECT
statements (which you can do as a group in SQL Workshop), examine the output of each statement in the Script Output pane. (The output for most of the statements is very long.)
Example 6-1 Using NLS Parameters in SQL Functions
SELECT TO_DATE('1-JAN-99', 'DD-MON-YY', 'NLS_DATE_LANGUAGE = American') "01/01/99" FROM DUAL; SELECT TO_CHAR(hire_date, 'DD/MON/YYYY', 'NLS_DATE_LANGUAGE = French') "Hire Date" FROM employees; SELECT TO_CHAR(SYSDATE, 'DD/MON/YYYY', 'NLS_DATE_LANGUAGE = ''Traditional Chinese'' ') "System Date" FROM DUAL; SELECT TO_CHAR(13000, '99G999D99', 'NLS_NUMERIC_CHARACTERS = '',.''') "13K" FROM DUAL; SELECT TO_CHAR(salary, '99G999D99L', 'NLS_NUMERIC_CHARACTERS = '',.'' NLS_CURRENCY = ''EUR''') salary FROM employees; SELECT TO_CHAR(salary, '99G999D99C', 'NLS_NUMERIC_CHARACTERS = ''.,'' NLS_ISO_CURRENCY = Japan') salary FROM employees; SELECT NLS_UPPER(last_name, 'NLS_SORT = Swiss') "Last Name" FROM employees; SELECT last_name FROM employees ORDER BY NLSSORT(last_name, 'NLS_SORT = German');
See Also:
Oracle Database Globalization Support Guide for a complete discussion of locale-dependent SQL functions with optional NLS parameters
You cannot use the NLS parameters NLS_LANGUAGE
, NLS_TERRITORY
, and NLS_DATE_FORMAT
in SQL functions except for NLSSORT
.
The NLS_LANGUAGE
parameter can interfere with the session value of NLS_DATE_LANGUAGE
. For example, if you specify NLS_LANGUAGE
in the TO_CHAR
function, Oracle Database will ignore its if it different from the NLS_DATE_LANGUAGE
parameter value for the session.
The NLS_DATE_FORMAT
and NLS_TERRITORY_FORMAT
parameters are not accepted as parameters because they can interfere with the required format models. A date format must be specified if an NLS parameter is used in a TO_CHAR
or TO_DATE
function, so NLS_DATE_FORMAT
and NLS_TERRITORY_FORMAT
parameters are not valid for these conversion functions. Oracle Database will return an error if you specify NLS_DATE_FORMAT
or NLS_TERRITORY_FORMAT
in the TO_CHAR
or TO_DATE
functions.
See Also:
Oracle Database Globalization Support Guide for a complete discussion of locale-dependent SQL functions with optional NLS parameters