| Oracle® Database Express Edition 2 Day Developer Guide 10g Release 2 (10.2) Part Number B25108-01 |
|
|
View PDF |
| Oracle® Database Express Edition 2 Day Developer Guide 10g Release 2 (10.2) Part Number B25108-01 |
|
|
View PDF |
This section discusses how to develop applications in a globalization support environment, providing information about SQL and PL/SQL Unicode programming in a global environment.
This section contains the following topics:
See Also:
Oracle Database Express Edition Installation Guide for Linux or Oracle Database Express Edition Installation Guide for Microsoft Windows for information about setting globalization parameters and environmental variables
Oracle Database Globalization Support Guide for a complete discussion of globalization support with Oracle Database Express Edition, including setting up the globalization support environment
Oracle Database SQL Reference for information about date and time formats
Oracle Database Express Edition 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.
Oracle Database XE 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 from anywhere in the world simultaneously. An application can render content of the user interface and process data in the native language and locale preferences of the user.
This section contains the following topics:
Note:
There are two distributions of Oracle Database Express Edition: one for Western Europe and the other for all languages.The Western European version includes a database created using a single-byte LATIN1 (WE8MSWIN1252) character set. The database can store Western European language text, such as French, Spanish, Portuguese, Italian, Dutch, German, Danish, Swedish, Norwegian, Finnish, Icelandic, as well as English. Database error messages are available in Brazilian Portuguese, English, French, German, Italian, and Spanish. The Oracle Database XE browser-based user interface is available in English only.
The Universal version includes a multi-byte Unicode (AL32UTF8) database. The database is suitable for data of all languages, including Greek, Russian, Polish, Romanian, Hungarian, Arabic, Hebrew, Turkish, Chinese, Japanese, Korean, and all the Western European languages listed in the previous package. Both the database error messages and Oracle Database XE browser-based user interface are available in Brazilian Portuguese, Chinese (Simplified and Traditional), English, French, German, Italian, Japanese, Korean and Spanish.
The smaller, Western European version is suitable for Western European language deployment, in environments where working with an English-only development interface is acceptable. The Universal package offers support for development and deployment in all languages, and it should be used when a Unicode database is desired.
Oracle Database XE 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 datatypes, Oracle Database XE supports most contemporary languages.
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.
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.
Monetary and numeric formats
This feature supports local formats for representing currency, credit, debit symbols, and numbers.
See "Monetary Parameters" and "Numeric and List Parameters".
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.
Linguistic sorting
This feature supports linguistic definitions for culturally accurate sorting and case conversion.
Character set support
This feature supports a large number of single-byte, multi-byte, and fixed-width encoding schemes that are based on national, international, and vendor-specific standards.
See Oracle Database Express Edition Installation Guide for Linux or Oracle Database Express Edition Installation Guide for Microsoft Windows for a listing of the character sets supported by Oracle Database XE.
Character semantics
This feature supports character semantics. It is useful for defining the storage requirements for multi-byte strings of varying widths in terms of characters instead of bytes.
See "Length Semantics".
Unicode support
This features supports Unicode, which is a universal encoded character set that enables you to store information in any language, using a single character set. Oracle Database Express Edition provides products such as SQL and PL/SQL for inserting and retrieving Unicode data.
You can run the SQL examples in this chapter using the SQL Commands page, Script Editor page, or SQL Command Line (SQL*Plus). You will need to log in as the HR user to use the SQL statements in the examples.
For information about running SQL statements on the SQL Commands page or Script Editor page, see "Running SQL Statements". For information about running SQL statements using SQL Command Line, see Appendix A, "Using SQL Command Line".
This section describes how to set up a globalization support environment.
This section contains the following topics:
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 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:
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 Express Edition Installation Guide for Linux or Oracle Database Express Edition Installation Guide for Microsoft Windows for information about the NLS_LANG environmental variable
Oracle Database SQL Reference for information about the ALTER SESSION statement
National Language Support (NLS) parameters determine the locale-specific behavior on both the client and the server. NLS parameters can be specified several ways. In this guide, altering parameters for the user session and overriding the parameters in SQL functions are discussed. Both of these techniques are accomplished through the use of SQL statements.
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.
See Also:
"Setting NLS Parameters" in Oracle Database Globalization Support Guide for details on setting the NLS parameters
Oracle Database SQL Reference for more information about the ALTER SESSION statement
Oracle Database Administrator's Guide for information about the initialization parameter file
Oracle Database Reference for information about initialization parameters used for globalization support
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')
See Also:
"Setting NLS Parameters" in Oracle Database Globalization Support Guide for information about setting the NLS parameters
Oracle Database SQL Reference for more information about SQL functions, including the TO_CHAR function
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:
Oracle Database Express Edition Installation Guide for Linux or Oracle Database Express Edition Installation Guide for Microsoft Windows for information about setting globalization parameters and environmental variablesSetting 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.
This section contains information about the following parameters:
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)
Example 7-1 and Example 7-2 show the results from setting the NLS_LANGUAGE parameter to different values. In Example 7-1, the ALTER SESSION statement is issued to set NLS_LANGUAGE to Italian.
Example 7-1 Setting NLS_LANGUAGE=ITALIAN
ALTER SESSION SET NLS_LANGUAGE=Italian; -- enter a SELECT to check the format of the output after the ALTER SESSION 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:
LAST_NAME HIRE_DATE SALARY------------------------- --------- ----------Sciarra 30-SET-97 962.5Urman 07-MAR-98 975Popp 07-DIC-99 862.5Note that the abbreviations for month names are in Italian.
In Example 7-2, the ALTER SESSION statement is issued to change the language to German.
Example 7-2 Setting NLS_LANGUAGE=GERMAN
ALTER SESSION SET NLS_LANGUAGE=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:
LAST_NAME HIRE_DATE SALARY------------------------- --------- ----------Sciarra 30-SEP-97 962.5Urman 07-MRZ-98 975Popp 07-DEZ-99 862.5Note that the abbreviations for the month names are now in German.
See Also:
Oracle Database Express Edition Installation Guide for Linux or Oracle Database Express Edition Installation Guide for Microsoft Windows for information about the supported languages in the Oracle Database Express Edition
Oracle Database Globalization Support Guide for more information about supported languages
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
The territory can be modified dynamically during the session by specifying the new NLS_TERRITORY value in an ALTER SESSION statement. For example, to change the territory to France during a session, issue the following ALTER SESSION statement:
ALTER SESSION SET NLS_TERRITORY = France;
Modifying the NLS_TERRITORY parameter resets all derived NLS session parameters to default values for the new territory. Example 7-3 and Example 7-4 show the results from different settings of NLS_TERRITORY and NLS_LANGUAGE.
Example 7-3 Setting NLS_LANGUAGE=AMERICAN, NLS_TERRITORY=AMERICA
-- set NLS_LANAGUAGE and NLS_TERRITORY
ALTER SESSION SET NLS_LANGUAGE = American NLS_TERRITORY = America;
-- enter the following SELECT to view the format of the output for currency
SELECT TO_CHAR(salary,'L99G999D99') salary FROM employees
WHERE employee_id IN (100, 101, 102);
When NLS_TERRITORY is set to AMERICA and NLS_LANGUAGE is set to AMERICAN, the results should be similar to the following:
SALARY-------------------- $24,000.00 $17,000.00 $17,000.00In Example 7-4, an ALTER SESSION statement is issued to change the territory to Germany.
Example 7-4 Setting NLS_LANGUAGE=AMERICAN and NLS_TERRITORY=GERMANY
-- set NLS_TERRITORY to Germany for this session
ALTER SESSION SET NLS_TERRITORY = Germany;
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:
SALARY------------------- €24.000,00 €17.000,00 €17.000,00Note that the currency symbol changed from dollars ($) to euros (€). The numbers have not changed because the underlying data is the same.
See Also:
Oracle Database Express Edition Installation Guide for Linux or Oracle Database Express Edition Installation Guide for Microsoft Windows for information about the supported territories in the Oracle Database Express Edition
Oracle Database Globalization Support Guide for more information about supported territories
Oracle Database XE enables you to 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.
This section contains the following topics:
Different date formats are shown in Table 7-1.
Table 7-1 Examples of Short Date Formats
| 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 |
|
US |
mm/dd/yyyy |
02/28/2005 |
This section describes 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 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"
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 examples in this section show the differences among some of the date formats.
Example 7-5 shows the use of the default, short, and long date formats.
Example 7-5 Using the Default, Short, and Long Date Formats
-- Use an ALTER SESSION statement to change the territory to America, -- and the language to American ALTER SESSION SET NLS_TERRITORY = America NLS_LANGUAGE = American; -- After the session is altered, select the dates with 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 results of the query in Example 7-5 should be similar to the following:
HIRE_DATE Short Long--------- ---------- -----------------------------30-SEP-97 9/30/1997 Tuesday, September 30, 199707-MAR-98 3/7/1998 Saturday, March 07, 199807-DEC-99 12/7/1999 Tuesday, December 07, 1999To add string literals to the date format, enclose the string literal with double quotes. 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 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)
Example 7-6 shows how to use NLS_DATE_LANGUAGE to set the date language to French.
Example 7-6 Setting NLS_DATE_LANGUAGE=FRENCH: Month and Day
-- set NLS_DATE_LANAGUAGE for this user session ALTER SESSION SET NLS_DATE_LANGUAGE = FRENCH; -- display 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, depending on the current system date:
TO_CHAR(SYSDATE,'DAY:DDMON--------------------------Jeudi :06 Octobre 2005The 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.-1997');
See Also:
Oracle Database SQL Reference for information about date format modelsDifferent time formats are shown in Table 7-2.
Table 7-2 Examples of Time Formats
| 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 |
|
US |
hh:mi:ssxff am |
1:50:23.555 PM |
This section describes the following parameters:
The NLS_TIMESTAMP_FORMAT parameter defines the default date format for the TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE datatypes. 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 datatypes. 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'
In Example 7-7 the TO_TIMESTAMP_TZ function uses the format value that was specified for NLS_TIMESTAMP_TZ_FORMAT.
Example 7-7 Setting NLS_TIMESTAMP_TZ_FORMAT
-- display August 20, 2005 using the format of NLS_TIMPSTAMP_TZ_FORMAT
SELECT TO_TIMESTAMP_TZ('2005-08-20, 05:00:00.55 America/Los_Angeles',
'yyyy-mm-dd hh:mi:ss.ff TZR') "TIMESTAMP_TZ Format" FROM DUAL;
The output from the example should be similar to the following:
TIMESTAMP_TZ Format--------------------------------------------------------20-AUG-05 05.00.00.550000000 AM AMERICA/LOS_ANGELESThis section contains 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.
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 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.
Oracle supports six calendar systems in addition to the Gregorian calendar, which is the default. The six 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 with 354 or 355 days
English Hijrah—has 12 months with 354 or 355 days
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.
Many different calendar systems are in use throughout the world. The NLS_CALENDAR parameter specifies which calendar system Oracle Database XE 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
In Example 7-8, the NLS_CALENDAR parameter is set to English Hijrah.
Example 7-8 Setting NLS_CALENDAR='English Hijrah'
-- set NLS_CALENDAR with ALTER SESSION ALTER SESSION SET NLS_CALENDAR='English Hijrah'; -- display the current system date SELECT SYSDATE FROM DUAL;
The output from the example should be similar to the following, depending on the current system date:
SYSDATE--------------------24 Ramadan 1422This section contains 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 7-3.
Table 7-3 Examples of Numeric Formats
| Country | Numeric Formats |
|---|---|
|
Estonia |
1 234 567,89 |
|
Germany |
1.234.567,89 |
|
China |
1,234,567.89 |
|
UK |
1,234,567.89 |
|
US |
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.
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.
The SELECT statement in Example 7-9 formats 4000 with the decimal character and group separator specified in the ALTER SESSION statement:
Example 7-9 Setting NLS_NUMERIC_CHARACTERS=",."
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ",."; SELECT TO_CHAR(4000, '9G999D99') FROM DUAL;
The output from the example should be similar to the following:
TO_CHAR(4---------4.000,00Oracle Database XE enables you to define radix symbols and thousands separators by locales. For example, in the US, 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.
This section contains the following topics:
Different currency formats are used throughout the world. Some typical formats are shown in Table 7-4.
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, as shown in Example 7-10.
Example 7-10 Displaying the Local Currency Symbol
-- select and format the salary column from employees SELECT TO_CHAR(salary, 'L099G999D99') "salary" FROM employees WHERE salary > 11000;
The output from the example should be similar to the following:
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.00The 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. 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 specify the ISO currency symbol for France, set NLS_ISO_CURRENCY as shown in Example 7-11.
Example 7-11 Setting NLS_ISO_CURRENCY=FRANCE
-- set NLS_ISO_CURRENCY to France ALTER SESSION SET NLS_ISO_CURRENCY = FRANCE; -- display the salary of selected employees 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.00Different 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.
This section contains the following topics:
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 datatype, 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.
Spain traditionally treats ch, ll, and ñ as letters of their own, ordered after c, l, and n, respectively. Example 7-12 and Example 7-13 illustrate the effect of using a Spanish sort against the employee names Chen and Chung. In Example 7-12, the NLS_SORT parameter is set to BINARY.
In Example 7-12, the LIKE comparison operator is used to specify the records to return with the query. For information about LIKE, see "Restricting Data Using the WHERE Clause".
Example 7-12 Setting NLS_SORT to BINARY
-- set the NLS_SORT for this user session ALTER SESSION SET NLS_SORT=binary; -- select the last name of those employees whose last name begin with C 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--------------CabrioCambraultCambraultChenChungColmenaresIn Example 7-13, the NLS_SORT parameter is set to SPANISH_M.
Example 7-13 Setting NLS_SORT to Spanish
-- set the NLS_SORT for this user session ALTER SESSION SET NLS_SORT=spanish_m; -- select the last name of those employees whose last name begin with C 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--------------CabrioCambraultCambraultColmenaresChenChungNote that the order of last names in the output from the SELECT statement in Example 7-12 and Example 7-13 is different.
See Also:
Oracle Database Globalization Support Guide for more information about supported linguistic sortsWhen 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.
Example 7-14 and Example 7-15 illustrate the effect of performing a binary comparison follow by a Spanish linguistic sensitive comparison against the employee names. In Example 7-14 the NLS_COMP parameter is set to BINARY while NLS_SORT is set to Spanish.
Example 7-14 Setting NLS_COMP to BINARY
-- set NLS_SORT and NLS_COMP for this user session ALTER SESSION SET NLS_SORT=spanish_m NLS_COMP=binary; -- select the last name of those employees whose last name begin 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--------------CabrioCambraultCambraultChenChungColmenaresIn Example 7-15 the NLS_COMP parameter is set to LINGUISTIC while NLS_SORT is set to Spanish.
Example 7-15 Setting NLS_COMP to LINGUISTIC
-- set NLS_SORT and NLS_COMP for this user session ALTER SESSION SET NLS_SORT=spanish_m NLS_COMP=linguistic; -- select the last name of those employees whose last name begin 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--------------CabrioCambraultCambraultColmenaresNote the difference in the output from Example 7-14 and Example 7-15. In Spanish ch is treated as a separate character that follows c, so ch is excluded when a Spanish linguistic-sensitive comparison is performed in Example 7-15.
Operations inside of 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 sortGENERIC_M_CI: accent-sensitive and case-insensitive GENERIC_M sortAppend _AI to an Oracle sort name for an accent-insensitive and case-insensitive sort. For example:
BINARY_AI: accent-insensitive and case-insensitive binary sortFRENCH_M_AI: accent-insensitive and case-insensitive FRENCH_M sortIn single-byte character sets, the number of bytes and the number of characters in a string are the same. In multi-byte 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 multi-byte 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)
This section contains the following topic:
The NLS_LENGTH_SEMANTICS parameter specifies BYTE (default) or CHAR semantics. By default, the character datatypes 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.
Example 7-16 shows an example of creating a table. When the database character set is WE8MSWIN1252, the last_name column of the table can hold up to 10 Western European characters, occupying a maximum of 10 bytes. When the database character set is Unicode (AL32UTF8), the last_name column can still hold up to 10 Unicode characters regardless of the language; however, it can occupy a maximum of 40 bytes.
Example 7-16 Setting Length Semantics and Creating a Table
-- reset NLS parameters back to default here ALTER SESSION SET NLS_LANGUAGE = American NLS_TERRITORY = America; CREATE TABLE temp_employees_table ( employee_id NUMBER(4), last_name VARCHAR2(10 CHAR), job_id VARCHAR2(9), manager_id NUMBER(4), hire_date DATE, salary NUMBER(7,2), department_id NUMBER(2)) ; -- cleanup: drop the table DROP TABLE temp_employees_table;
See Also:
"Length Semantics for Character Datatypes" in Oracle Database ConceptsThis section describes Unicode-related features in SQL and PL/SQL that you can deploy for multiple language applications. Oracle Database XE provides products such as SQL and PL/SQL for inserting and retrieving 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.
This section contains the following topics:
See Also:
Oracle Database SQL Reference for information about SQL
Oracle Database PL/SQL User's Guide and Reference for information about PL/SQL
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 multi-byte character sets.
It supports the Unicode datatype 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 datatypes.
You can support multiple language data in specific columns by using Unicode datatypes. You can store Unicode characters into columns of the SQL NCHAR datatypes regardless of how the database character set has been defined. The NCHAR datatype is an exclusively Unicode datatype.
There are two SQL NCHAR datatypes:
When you define a table column or a PL/SQL variable as the NCHAR datatype, 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 for the column is determined as follows:
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 datatypes, 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.
The NVARCHAR2 datatype 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 XE subsequently stores each value in the column exactly as you specify it, if the value does not exceed the maximum length of the column. Oracle Database XE 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));
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.
Being 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, the data is lost in this conversion. This affects NCHAR string literals more than the CHAR text literals, this is because the N' literals 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 activate the NCHAR literal replacement functionality. It transparently replaces the N' literals on the client side into an internal format, the database then decodes this to Unicode when the statement is executed. You can set the client environment variable ORA_NCHAR_LITERAL_REPLACE to TRUE to enable this functionality. By default, the functionality is switched off to maintain backward compatibility.
All SQL functions whose behavior depends on globalization support conventions allow NLS parameters to be specified. These functions are:
TO_CHARTO_DATETO_NUMBERNLS_UPPERNLS_LOWERNLS_INITCAPNLSSORTExplicitly specifying the optional 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, the following query is evaluated correctly if the language specified for dates is AMERICAN and the calender is specified as GREGORIAN.
Example 7-17 Setting NLS_DATE_LANGUAGE=American, NLS_CALENDAR=Gregorian
ALTER SESSION SET NLS_DATE_LANGUAGE=American; ALTER SESSION SET NLS_CALENDAR=Gregorian; SELECT last_name FROM employees WHERE hire_date > '01-JAN-1999';
The previous query can be made independent of the current date language by using a statement similar to the following:
Example 7-18 Setting NLS_LANGUAGE in a Query
SELECT last_name FROM employees
WHERE hire_date > TO_DATE('01-JAN-1999','DD-MON-YYYY',
'NLS_DATE_LANGUAGE = AMERICAN');
In 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.
Note:
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 multi-byte characters. Except where explicitly stated, character functions operate character by character, rather than byte by byte.
The remainder of this section contains the following topics:
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.
NLS parameters are specified in SQL functions as 'parameter = value'. For example:
'NLS_DATE_LANGUAGE = AMERICAN'
The following NLS parameters can be specified in SQL functions:
NLS_DATE_LANGUAGENLS_NUMERIC_CHARACTERSNLS_CURRENCYNLS_ISO_CURRENCYNLS_DUAL_CURRENCYNLS_CALENDARNLS_SORTTable 7-5 shows which NLS parameters are valid for specific SQL functions.
Table 7-5 SQL Functions and Their Valid NLS Parameters
| SQL Function | Valid NLS Parameters |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Example 7-19 shows how to use NLS parameters in SQL functions.
Example 7-19 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');
Note:
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 theNLS_UPPER, NLS_LOWER, and NLS_INITCAP functions can differ from the length of the input.The following NLS parameters are not accepted in SQL functions except for NLSSORT:
NLS_LANGUAGE
NLS_TERRITORY
NLS_DATE_FORMAT
The NLS_DATE_FORMAT and NLS_TERRITORY_FORMAT parameters are not accepted as parameters because they can interfere with required format models. A date format must be specified if an NLS parameter is in a TO_CHAR or TO_DATE function. As a result, NLS_DATE_FORMAT and NLS_TERRITORY_FORMAT are not valid NLS parameters for the TO_CHAR or TO_DATE functions. If you specify NLS_DATE_FORMAT or NLS_TERRITORY_FORMAT in the TO_CHAR or TO_DATE function, then an error is returned.
NLS_LANGUAGE can interfere with the session value of NLS_DATE_LANGUAGE. If you specify NLS_LANGUAGE in the TO_CHAR function, for example, then its value is ignored if it differs from the session value of NLS_DATE_LANGUAGE.
