6 Working in a Global Environment

Using the NLS_LANGUAGE Parameter

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.

1. 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.

2. Set the language to Italian.

   ALTER SESSION SET NLS_LANGUAGE=ITALIAN;
   

3. 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
   

4. Set the language to German.

   ALTER SESSION SET NLS_LANGUAGE=GERMAN;
   

5. 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
   

6. Set NLS_LANGUAGE back to its original setting listed in Step 1. For example:

   ALTER SESSION SET NLS_LANGUAGE=AMERICAN;
   

Using the NLS_TERRITORY Parameter

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.

1. 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
   

2. 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.

3. Set NLS_TERRITORY to Germany.

   ALTER SESSION SET NLS_TERRITORY=GERMANY;
   

4. 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
   

5. Set NLS_TERRITORY back to its original setting listed in Step 2. For example:

   ALTER SESSION SET NLS_TERRITORY=AMERICA;
   

Using Date Formats

You can use several different date formats, as shown in the following table.

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.

1. 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.

2. Set NLS_TERRITORY to America.

   ALTER SESSION SET NLS_TERRITORY = America;
   

3. 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
   

4. 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.

1. 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.

2. Set NLS_DATE_LANGUAGE to French.

   ALTER SESSION SET NLS_DATE_LANGUAGE = FRENCH;
   

3. 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
   

4. Set NLS_DATE_LANGUAGE back to its original setting listed in Step 1. For example:

   ALTER SESSION SET NLS_DATE_LANGUAGE=AMERICAN;
   

Using Time Formats

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.

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.

1. 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.

2. Set NLS_TIMESTAMP_TZ_FORMAT.

   ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF TZH:TZM';
   

3. 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';
   

Overview of Calendar Formats

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.

Using the NLS_CALENDAR Parameter

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.

1. 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.

2. Set NLS_CALENDAR to English Hijrah.

   ALTER SESSION SET NLS_CALENDAR='English Hijrah';
   

3. 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
   

4. Set NLS_CALENDAR back to its original setting listed in Step 1. For example:

   ALTER SESSION SET NLS_CALENDAR='GREGORIAN';
   

Using 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.

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.

1. 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.

2. Set NLS_NUMERIC_CHARACTERS to the specified group separator and decimal character.

   ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ",.";
   

   Use double quotation marks.

3. 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
   

4. Set NLS_NUMERIC_CHARACTERS back to its original setting listed in Step 1. For example:

   ALTER SESSION SET NLS_NUMERIC_CHARACTERS=". ";
   

Overview of Currency Formats

Different currency formats are used throughout the world. Some typical formats are shown in the following table.

Using the NLS_CURRENCY Parameter

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.

1. 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
   

Using the NLS_ISO_CURRENCY Parameter

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.

1. 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.

2. Set NLS_ISO_CURRENCY to France.

   ALTER SESSION SET NLS_ISO_CURRENCY=FRANCE;
   

3. 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
   

4. Set NLS_ISO_CURRENCY back to its original setting listed in Step 1. For example:

   ALTER SESSION SET NLS_ISO_CURRENCY=AMERICA;
   

Using the NLS_DUAL_CURRENCY Parameter

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.

Using the NLS_SORT Parameter

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.

1. 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.

2. Set NLS_SORT to binary.

   ALTER SESSION SET NLS_SORT=BINARY;
   

3. 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
   

4. Set NLS_SORT to SPANISH_M.

   ALTER SESSION SET NLS_SORT=spanish_m;
   

5. 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
   

6. Set NLS_SORT back to its original setting listed in Step 1. For example:

   ALTER SESSION SET NLS_SORT=BINARY;
   

Using the NLS_COMP Parameter

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.

1. 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.

2. Set NLS_SORT to Spanish and NLS_COMP to BINARY.

   ALTER SESSION SET NLS_SORT=spanish_m NLS_COMP=binary;
   

3. 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
   

4. Set NLS_COMP to LINGUISTIC.

   ALTER SESSION SET NLS_COMP=linguistic;
   

5. 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
   

6. Set NLS_COMP back to its original setting listed in Step 1. For example:

   ALTER SESSION SET NLS_COMP=BINARY;
   

Using Case-Insensitive and Accent-Insensitive Search

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

Using the NLS_LENGTH_SEMANTICS Parameter

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:

1. 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.

2. Set NLS_LENGTH_SEMANTICS to BYTE.

   ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;
   

3. Create the following table:

   CREATE TABLE SEMANTICS_BYTE(SOME_DATA VARCHAR2(20));
   

4. 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).

5. Set NLS_LENGTH_SEMANTICS to CHAR.

   ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR;
   

6. Create the following table:

   CREATE TABLE SEMANTICS_CHAR(SOME_DATA VARCHAR2(20));
   

7. 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).

8. 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.

9. Set NLS_LENGTH_SEMANTICS back to its original setting listed in Step 1. For example:

   ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;
   

Using the NCHAR Data Type

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.

Using the NVARCHAR2 Data Type

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));