Skip Headers
Oracle® Database 2 Day Developer's Guide,
11g Release 1 (11.1)

Part Number B28843-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

6 Working in a Global Environment

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:

Overview of Globalization

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:

Globalization Support Features

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:

Viewing the Current NLS Parameter Values

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:

  1. In the SQL Developer window, click the Reports tab to display the Reports navigator.

  2. Click the plus sign (+) next to the Data Dictionary Reports node to expand it.

  3. Click the plus sign (+) next to the About Your Database node to expand it.

  4. Click the National Language Support Parameters item.

  5. In the Select Connection dialog box, set Connection to hr_conn.

    Click OK.

    Description of view_nls_param_1.gif follows
    Description of the illustration view_nls_param_1.gif

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

    Description of view_nls_param_2.gif follows
    Description of the illustration view_nls_param_2.gif

See Also:

Using NLS Parameter Values in the SQL Developer Environment

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:

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:

Changing NLS Parameter Values for All Sessions

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:

  1. In the SQL Developer window, click Tools, then Preferences.

  2. In the Preferences dialog box, expand the Database node and select NLS Parameters.

    Description of set_nls_param_1.gif follows
    Description of the illustration set_nls_param_1.gif

    Each text label is a descriptive term for a corresponding NLS_xxx parameter.

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

  4. Click OK.

See Also:

Establishing a Globalization Support Environment

This section describes how to set up a globalization support environment.

See Also:

Choosing a Locale with the NLS_LANG Parameter

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:

Setting NLS Parameters

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 Language and Territory Parameters

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:

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;
    

See Also:

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;
    

See Also:

Setting Date and Time Parameters

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:

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

  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;
    

See Also:

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.

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.

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

See Also:

Setting Calendar Definitions

This section describes calendar definition.

See Also:

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.

See Also:

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

See Also:

Using Numeric Formats

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:

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=". ";
    

See Also:

Using Monetary Parameters

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:

Overview of Currency Formats

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:

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
    

See Also:

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;
    

See Also:

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.

See Also:

Using Linguistic Sort and Search

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:

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;
    

See Also:

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;
    

See Also:

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

See Also:

Using Length Semantics

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:

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;
    

See Also:

Developing Globalized Applications

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:

Overview of 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.

Using SQL Character Data Types

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.

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.

See Also:

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

See Also:

Using Unicode String Literals

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.

NCHAR Literal Replacement

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.

Using Locale-Dependent Functions with NLS Parameters

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.

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:

Specifying NLS Parameters in SQL Functions

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:

Unacceptable NLS Parameters in SQL Functions

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: