Working in a Global Environment

This topic describes how to use Oracle's database access products with Unicode and provides useful information for SQL and PL/SQL programming in a globalization support environment. It includes the following topics:


See Also:

  • Oracle Database Globalization Support Guide for a complete discussion of globalization support with Oracle Database, including setting up the globalization support environment

  • Oracle Database Reference for information on initialization parameters used for globalization support

  • Oracle Database SQL Language Reference for information on date and time formats


About Globalization Support Features

Globalization support features enable you to develop multilingual applications that can be run simultaneously from anywhere in the world. An application can render the content of the user interface, and process data, using the native language and locale preferences of the user.


Note:

In the past, Oracle called globalization support National Language Support (NLS), but NLS is actually a subset of globalization support. NLS is the ability to choose a national language and store data using a specific character set. NLS is implemented with NLS parameters.

This section contains:

About Language Support

Oracle Database enables you to store, process, and retrieve data in native languages. The languages that can be stored in a database are all languages written in scripts that are encoded by Oracle-supported character sets. Through the use of Unicode databases and data types, Oracle Database supports most contemporary languages.

Additional support is available for a subset of the languages. The database can, for example, display dates using translated month names, and can sort text data according to cultural conventions.

In this document, the term language support refers to the additional language-dependent functionality, and not to the ability to store text of a specific language. For example, language support includes displaying dates or sorting text according to specific locales and cultural conventions. Additionally, for some supported languages, Oracle Database provides translated error messages and a translated user interface for the database utilities.


See Also:


About Territory Support

Oracle Database supports cultural conventions that are specific to geographical locations. The default local time format, date format, and numeric and monetary conventions depend on the local territory setting. Setting different NLS parameters enables 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 AMERICA.

About Date and Time Formats

Different countries have different conventions for displaying the hour, day, month, and year. For example:

Country Date Format Example Time Format Example
China yyyy-mm-dd 2005-02-28 hh24:mi:ss 13:50:23
Estonia dd.mm.yyyy 28.02.2005 hh24:mi:ss 13:50:23
Germany dd.mm.rr 28.02.05 hh24:mi:ss 13:50:23
UK dd/mm/yyyy 28/02/2005 hh24:mi:ss 13:50:23
U.S. mm/dd/yyyy 02/28/2005 hh:mi:ssxff am 1:50:23.555 PM

About Calendar Formats

Oracle Database stores this calendar information for each territory:

  • First day of the week

    Sunday in some cultures, Monday in others. Set by the NLS_TERRITORY parameter.

  • First week of the calendar year

    Some countries use week numbers for scheduling, planning, and bookkeeping. In the ISO standard, this week number can differ 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 Database provides the IW date format element, which returns the ISO week number. The first calendar week of the year is set by the NLS_TERRITORY parameter.

  • Number of days and months in a year

    Oracle Database supports six calendar systems in addition to the Gregorian calendar, which is the default. These additional calendar systems are:

    • Japanese Imperial

      Has the same number of months and days as the Gregorian calendar, but the year starts with the beginning of each Imperial Era.

    • ROC Official

      Has the same number of months and days as the Gregorian calendar, but the year starts with the founding of the Republic of China.

    • Persian

      The first six months have 31 days each, the next five months have 30 days each, and the last month has either 29 days or (in leap year) 30 days.

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

About Numeric and Monetary Formats

Different countries have different numeric and monetary conventions. For example:

Country Numeric Format Monetary Format
China 1,234,567.89 ¥1,234.56
Estonia 1 234 567,89 1 234,56 kr
Germany 1.234.567,89 1.234,56
UK 1,234,567.89 £1,234.56
U.S. 1,234,567.89 $1,234.56

About Linguistic Sorting and String Searching

Different languages have different sort orders. In addition, different cultures or countries that use the same alphabets may sort words differently. For example, in Danish, Æ is after Z, while Y and Ü are considered to be variants of the same letter.

Sort order can be case-sensitive or case-insensitive. Case refers to the condition of being uppercase or lowercase. For example, in a Latin alphabet, A is the uppercase glyph for a, the lowercase glyph.

Sort order can ignore or consider diacritics. A diacritic is a mark near or through a character or combination of characters that indicates a different sound than the sound of the character without the diacritic. For example, the cedilla (,) in façade is a diacritic. It changes the sound of c.

Sort order can be phonetic or it can be based on the appearance of the character. For example, sort order can be based on the number of strokes in East Asian ideographs. Another common sorting issue is combining letters into a single character. For example, in traditional Spanish, ch is a distinct character that comes after c, which means that the correct order is: cerveza, colorado, cheremoya. This means that the letter c cannot be sorted until Oracle Database has checked whether the next letter is an h.


See Also:


About 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 for specifying the storage requirements for multibyte strings of varying widths. For example, in a Unicode database (AL32UTF8), suppose that you must have a VARCHAR2 column that can store up to five Chinese characters 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.

About Unicode and SQL National Character Data Types

Unicode is a character encoding system that defines every character in most of the spoken languages in the world. In Unicode, every character has a unique code, regardless of the platform, program, or language.

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 character data types (CHAR, VARCHAR2, CLOB, and LONG).

  • You can declare columns and variables that have SQL national character data types.

The SQL national character data types are NCHAR, NVARCHAR2, and NCLOB. They are also called Unicode data types, because they are used only for storing Unicode data.

The national character set, which is used for all SQL national character data types, is specified when the database is created. The national character set can be either UTF8 or AL16UTF16 (default).

When you declare a column or variable of the type NCHAR or NVARCHAR2, the length that you specify is the number of characters, not the number of bytes.


See Also:

  • Oracle Database Globalization Support Guide for more information about Unicode

  • Oracle Database Globalization Support Guide for more information about storing Unicode characters in an Oracle Database

  • About Globalization Support Features


About Initial NLS Parameter Values

Except in SQL Developer, the initial values of NLS parameters are set by database initialization parameters. The DBA can set the values of initialization parameters in the initialization parameter file, and they take effect the next time the database is started.

In SQL Developer, the initial values of NLS parameters are as shown in Table: Initial Values of NLS Parameters in SQL Developer.

Initial Values of NLS Parameters in SQL Developer

Parameter Initial Value

NLS_CALENDAR

GREGORIAN

NLS_CHARACTERSET

AL32UTF8

NLS_COMP

BINARY

NLS_CURRENCY

$


NLS_DATE_FORMAT

DD-MON-RR

NLS_DATE_LANGUAGE

AMERICAN

NLS_DUAL_CURRENCY

$


NLS_ISO_CURRENCY

AMERICA

NLS_LANGUAGE

AMERICAN

NLS_LENGTH_SEMANTICS

BYTE

NLS_NCHAR_CHARACTERSET

AL16UTF16

NLS_NCHAR_CONV_EXCP

FALSE

NLS_NUMERIC_CHARACTERS

.,

NLS_SORT

BINARY

NLS_TERRITORY

AMERICA

NLS_TIMESTAMP_FORMAT

DD-MON-RR HH.MI.SSXFF AM

NLS_TIMESTAMP_TZ_FORMAT

DD-MON-RR HH.MI.SSXFF AM TZR

NLS_TIME_FORMAT

HH.MI.SSXFF AM

NLS_TIME_TZ_FORMAT

HH.MI.SSXFF AM TZR


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 

Viewing NLS Parameter Values

To view the current values of NLS parameters, use either the SQL Developer report National Language Support Parameters or this query:

SELECT * FROM V$NLS_PARAMETERS;

Changing NLS Parameter Values

You can change the value of one or more NLS parameters in any of these ways:

This section contains:

Changing NLS Parameter Values for All SQL Developer Connections

To change the values of NLS parameters for all SQL Developer connections, current and future, click Tools, then Preferences, then Database, then NLS Parameters.

A list of NLS Parameters and their current values is displayed. You can change any of the parameter values by selecting the desired new value.

When you are finished, click OK.

Changing NLS Parameter Values for the Current SQL Function Invocation

SQL functions whose behavior depends on the values of NLS parameters are called locale-dependent. Some locale-dependent SQL functions have optional NLS parameters. These functions are:

  • TO_CHAR

  • TO_DATE

  • TO_NUMBER

  • NLS_UPPER

  • NLS_LOWER

  • NLS_INITCAP

  • NLSSORT

In all of the preceding functions, you can specify these NLS parameters:

  • NLS_DATE_LANGUAGE

  • NLS_DATE_LANGUAGE

  • NLS_NUMERIC_CHARACTERS

  • NLS_CURRENCY

  • NLS_ISO_CURRENCY

  • NLS_DUAL_CURRENCY

  • NLS_CALENDAR

  • NLS_SORT

In the NLSSORT function, you can also specify these NLS parameters:

  • NLS_LANGUAGE

  • NLS_TERRITORY

  • NLS_DATE_FORMAT

To specify NLS parameters in a function, use this syntax:

'parameter=value' ['parameter=value']...

Suppose that you want NLS_DATE_LANGUAGE to be AMERICAN when this query is evaluated:

SELECT last_name FROM employees WHERE hire_date > '01-JAN-1999';

You can set NLS_DATE_LANGUAGE to AMERICAN before running the query:

ALTER SESSION SET NLS_DATE_LANGUAGE=American;
SELECT last_name FROM employees WHERE hire_date > '01-JAN-1999';

Alternatively, you can set NLS_DATE_LANGUAGE to AMERICAN inside the query, using the locale-dependent SQL function TO_DATE with its optional NLS_DATE_LANGUAGE parameter:

SELECT last_name FROM employees
WHERE hire_date > TO_DATE('01-JAN-1999', 'DD-MON-YYYY',
                          'NLS_DATE_LANGUAGE=AMERICAN');

Tip:

Specify optional NLS parameters in locale-dependent SQL functions only in SQL statements that must be independent of the session NLS parameter values. Using session default values for NLS parameters in SQL functions usually results in better performance.

About Individual NLS Parameters

This section contains:


See Also:


Locale and the NLS_LANG Parameter

A locale is a linguistic and cultural environment in which a system or application runs. The simplest way to specify a locale for Oracle Database software is to set the NLS_LANG parameter.

The NLS_LANG parameter sets the default values of the parameters NLS_LANGUAGE and NLS_TERRITORY for both the server session (for example, SQL statement processing) and the client application (for example, display formatting in Oracle Database tools). The NLS_LANG parameter also sets the character set that the client application uses for data entered or displayed.

The default value of NLS_LANG is set during database installation. You can use the ALTER SESSION statement to change the values of NLS parameters, including those set by NLS_LANG, for your session. However, only the client can change the NLS settings in the client environment.


See Also:

  • Oracle Database Globalization Support Guide for more information about specifying a locale with the NLS_LANG parameter

  • Oracle Database Globalization Support Guide for information about languages, territories, character sets, and other locale data supported by Oracle Database


NLS_LANGUAGE Parameter

Specifies: Default language of the database. Default conventions for:

  • Language for server messages

  • Language for names and abbreviations of days and months that are specified in the SQL functions TO_CHAR and TO_DATE

  • Symbols for default-language equivalents of AM, PM, AD, and BC

  • Default sorting order for character data when the ORDER BY clause is specified

  • Writing direction

  • Affirmative and negative response strings (for example, YES and NO)

Acceptable Values: Any language name that Oracle supports. For a list, see Oracle Database Globalization Support Guide.

Default Value: Set by NLS_LANG, described in Locale and the NLS_LANG Parameter.

Sets default values of:

The following example shows how setting NLS_LANGUAGE to ITALIAN and GERMAN affects server messages and month abbreviations.

Example: NLS_LANGUAGE Affects Server Message and Month Abbreviations

  1. Note the current value of NLS_LANGUAGE.

  2. If the value in the preceding step is not ITALIAN, change it:

    ALTER SESSION SET NLS_LANGUAGE=ITALIAN;
    
  3. Query a nonexistent table:

    SELECT * FROM nonexistent_table;
    

    Result:

    SELECT * FROM nonexistent_table
                  *
    ERROR at line 1:
    ORA-00942: tabella o vista inesistente
    
  4. Run this query:

    SELECT LAST_NAME, HIRE_DATE
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID IN (111, 112, 113);
    

    Result:

    LAST_NAME                 HIRE_DATE
    ------------------------- ---------
    Sciarra                   30-SET-97
    Urman                     07-MAR-98
    Popp                      07-DIC-99
     
    3 rows selected.
    
  5. Change the value of NLS_LANGUAGE to GERMAN:

    ALTER SESSION SET NLS_LANGUAGE=GERMAN;
    
  6. Repeat the query from step 3.

    Result:

    SELECT * FROM nonexistent_table
                  *
    ERROR at line 1:
    ORA-00942: Tabelle oder View nicht vorhanden
    
  7. Repeat the query from step 4.

    Result:

    LAST_NAME                 HIRE_DATE
    ------------------------- ---------
    Sciarra                   30-SEP-97
    Urman                     07-MRZ-98
    Popp                      07-DEZ-99
     
    3 rows selected.
    
  8. Set NLS_LANGUAGE to the value that it had at step 1.

NLS_TERRITORY Parameter

Specifies: Default conventions for:

  • Date format

  • Timestamp format

  • Decimal character and group separator

  • Local currency symbol

  • ISO currency symbol

  • Dual currency symbol

Acceptable Values: Any territory name that Oracle supports. For a list, see Oracle Database Globalization Support Guide.

Default Value: Set by NLS_LANG, described in Locale and the NLS_LANG Parameter.

Sets default values of:

The following example shows how setting NLS_TERRITORY to JAPAN and AMERICA affects the currency symbol.

Example: NLS_TERRITORY Affects Currency Symbol

  1. Note the current value of NLS_TERRITORY.

  2. If the value in step 1 is not JAPAN, change it:

    ALTER SESSION SET NLS_TERRITORY=JAPAN;
    
  3. Run this query:

    SELECT TO_CHAR(SALARY,'L99G999D99') SALARY
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID IN (100, 101, 102);
    

    Result:

    SALARY
    --------------------
              ¥24,000.00
              ¥17,000.00
              ¥17,000.00
     
    3 rows selected.
    
  4. Change the value of NLS_TERRITORY to AMERICA:

    ALTER SESSION SET NLS_TERRITORY=AMERICA;
    
  5. Repeat the query from step 3.

    Result:

    SALARY
    --------------------
              $24,000.00
              $17,000.00
              $17,000.00
     
    3 rows selected.
    
  6. Set NLS_TERRITORY to the value that it had at step 1.

NLS_DATE_FORMAT Parameter

Specifies: Default date format to use with the TO_CHAR and TO_DATE functions.

Acceptable Values: Any any valid datetime format model. For example:

NLS_DATE_FORMAT='MM/DD/YYYY'

For information about datetime format models, see Oracle Database SQL Language Reference.

Default Value: Set by NLS_TERRITORY, described in NLS_TERRITORY Parameter.

The default date format might not correspond to the convention used in a given territory. To get dates in localized formats, you can use the 'DS' (short date) and 'DL' (long date) formats.

The following example shows how setting NLS_TERRITORY to AMERICA and FRANCE affects the default, short, and long date formats.

Example: NLS_TERRITORY Affects Date Formats

  1. Note the current value of NLS_TERRITORY.

  2. If the value instep 1 is not AMERICA, change it:

    ALTER SESSION SET NLS_TERRITORY=AMERICA;
    
  3. Run this query:

    SELECT hire_date "Default",
           TO_CHAR(hire_date,'DS') "Short",
           TO_CHAR(hire_date,'DL') "Long"
    FROM employees
    WHERE employee_id IN (111, 112, 113);
    

    Result:

    Default    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
     
    3 rows selected.
    
  4. Change the value of NLS_TERRITORY to FRANCE:

    ALTER SESSION SET NLS_TERRITORY=FRANCE;
    
  5. Repeat the query from step 3.

    Result:

    Default  Short      Long
    -------- ---------- ---------------------------
    30/09/97 30/09/1997 tuesday 30 september 1997
    07/03/98 07/03/1998 saturday 7 march 1998
    07/12/99 07/12/1999 tuesday 7 december 1999
     
    3 rows selected.
    

    (To get the names of the days and months in French, you must set either NLS_LANGUAGE or NLS_DATE_LANGUAGE to FRENCH before running the query.)

  6. Set NLS_TERRITORY to the value that it had at step 1.

The following example changes the value of NLS_DATE_FORMAT, overriding the default value set by NLS_TERRITORY.

Example: NLS_DATE_FORMAT Overrides NLS_TERRITORY

  1. Note the current values of NLS_TERRITORY and NLS_DATE_FORMAT.

  2. If the value of NLS_TERRITORY in step 1 is not AMERICA, change it:

    ALTER SESSION SET NLS_TERRITORY=AMERICA;
    
  3. If the value of NLS_DATE_FORMAT in step 1 is not 'Day Month ddth', change it:

    ALTER SESSION SET NLS_DATE_FORMAT='Day Month ddth';
    
  4. Run this query (from previous example, step 3):

    SELECT hire_date "Default",
           TO_CHAR(hire_date,'DS') "Short",
           TO_CHAR(hire_date,'DL') "Long"
    FROM employees
    WHERE employee_id IN (111, 112, 113);
    

    Result:

    Default                  Short      Long
    ------------------------ ---------- -----------------------------
    Tuesday   September 30th 9/30/1997  Tuesday, September 30, 1997
    Saturday  March     07th 3/7/1998   Saturday, March 07, 1998
    Tuesday   December  07th 12/7/1999  Tuesday, December 07, 1999
    
    3 rows selected.
    
  5. Set NLS_TERRITORY and NLS_DATE_FORMAT to the values that they had at step 1.

NLS_DATE_LANGUAGE Parameter

Specifies: Language for names and abbreviations of days and months that are produced by:

  • SQL functions TO_CHAR and TO_DATE

  • Default date format (set by NLS_DATE_FORMAT, described in NLS_DATE_FORMAT Parameter)

  • Symbols for default-language equivalents of AM, PM, AD, and BC

Acceptable Values: Any language name that Oracle supports. For a list, see Oracle Database Globalization Support Guide.

Default Value: Set by NLS_LANGUAGE, described in NLS_LANGUAGE Parameter.

The following example shows how setting NLS_DATE_LANGUAGE to FRENCH and SWEDISH affects the displayed system date.

Example: NLS_DATE_LANGUAGE Affects Displayed SYSDATE

  1. Note the current value of NLS_DATE_LANGUAGE.

  2. If the value of NLS_DATE_LANGUAGE in step 1 is not FRENCH, change it:

    ALTER SESSION SET NLS_DATE_LANGUAGE=FRENCH;
    
  3. Run this query:

    SELECT TO_CHAR(SYSDATE, 'Day:Dd Month yyyy') "System Date"
    FROM DUAL;
    

    Result:

    System Date
    --------------------------
    Mercredi:01 Octobre   2008
    
  4. Change the value of NLS_DATE_LANGUAGE to SWEDISH:

    ALTER SESSION SET NLS_DATE_LANGUAGE=SWEDISH;
    
  5. Repeat the query from step 3.

    Result:

    System Date
    -------------------------
    Onsdag :01 Oktober   2008
    
  6. Set NLS_DATE_LANGUAGE to the value that it had at step 1.

NLS_TIMESTAMP_FORMAT and NLS_TIMESTAMP_TZ_FORMAT Parameters

Specify: Default date format for:

  • TIMESTAMP data type

  • TIMESTAMP WITH LOCAL TIME ZONE data type

Acceptable Values: Any any valid datetime format model. For example:

NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH:MI:SS.FF'
NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH:MI:SS.FF TZH:TZM'

For information about datetime format models, see Oracle Database SQL Language Reference.

Default Value: Set by NLS_TERRITORY, described in NLS_TERRITORY Parameter.

NLS_CALENDAR Parameter

Specifies: Calendar system for the database.

Acceptable Values: Any calendar system that Oracle supports. For a list, see Oracle Database Globalization Support Guide.

Default Value: Gregorian

The following example shows how setting NLS_CALENDAR to 'English Hijrah' and Gregorian affects the displayed system date.

Example: NLS_CALENDAR Affects Displayed SYSDATE

  1. Note the current value of NLS_CALENDAR.

  2. If the value of NLS_CALENDAR in step 1 is not 'English Hijrah', change it:

    ALTER SESSION SET NLS_CALENDAR='English Hijrah';
    
  3. Run this query:

    SELECT SYSDATE FROM DUAL;
    

    Result:

    SYSDATE
    -------------------------
    29 Ramadan           1429
    
  4. Change the value of NLS_CALENDAR to 'Gregorian':

    ALTER SESSION SET NLS_CALENDAR='Gregorian';
    
  5. Run this query:

    SELECT SYSDATE FROM DUAL;
    

    Result:

    SYSDATE
    ---------
    30-SEP-08
    
  6. Set NLS_CALENDAR to the value that it had at step 1.

NLS_NUMERIC_CHARACTERS Parameter

Specifies: Decimal character (which separates the integer and decimal parts of a number) and group separator (which separates integer groups to show thousands and millions, for example). The group separator is the character returned by the numeric format element G.

Acceptable Values: Any two different single-byte characters except:

  • A numeric character

  • Plus (+)

  • Minus (-)

  • Less than (<)

  • Greater than (>)

Default Value: Set by NLS_TERRITORY, described in NLS_TERRITORY Parameter.

In a SQL statement, you can represent a number as either:

  • Numeric literal

    A numeric literal is not enclosed in quotation marks, always uses a period (.) as the decimal character, and never contains a group separator.

  • Text literal

    A text literal is enclosed in single quotation marks. It is implicitly or explicitly converted to a number, if required, according to the current NLS settings.

The following example shows how two different NLS_NUMERIC_CHARACTERS settings affect the displayed result of the same query.

Example: NLS_NUMERIC_CHARACTERS Affects Decimal Character and Group Separator

  1. Note the current value of NLS_NUMERIC_CHARACTERS.

  2. If the value of NLS_NUMERIC_CHARACTERS in step 1 is not ",." (decimal character is comma and group separator is period), change it:

    ALTER SESSION SET NLS_NUMERIC_CHARACTERS=",.";
    
  3. Run this query:

    SELECT TO_CHAR(4000, '9G999D99') "Number" FROM DUAL;
    

    Result:

    Number
    ---------
     4.000,00
    
  4. Change the value of NLS_NUMERIC_CHARACTERS to ",." (decimal character is period and group separator is comma):

    ALTER SESSION SET NLS_NUMERIC_CHARACTERS=".,";
    
  5. Run this query:

    SELECT TO_CHAR(4000, '9G999D99') "Number" FROM DUAL;
    

    Result:

    Number
    ---------
     4,000.00
    
  6. Set NLS_NUMERIC_CHARACTERS to the value that it had at step 1.

NLS_CURRENCY Parameter

Specifies: Local currency symbol (the character string returned by the numeric format element L).

Acceptable Values: Any valid currency symbol string.

Default Value: Set by NLS_TERRITORY, described in NLS_TERRITORY Parameter.

The following example changes the value of NLS_CURRENCY, overriding the default value set by NLS_TERRITORY.

Example: NLS_CURRENCY Overrides NLS_TERRITORY

  1. Note the current values of NLS_TERRITORY and NLS_CURRENCY.

  2. If the value of NLS_TERRITORY in step 1 is not AMERICA, change it:

    ALTER SESSION SET NLS_TERRITORY=AMERICA;
    
  3. Run this query:

    SELECT TO_CHAR(salary, 'L099G999D99') "Salary"
    FROM EMPLOYEES
    WHERE salary > 13000;
    

    Result:

    Salary
    ---------------------
              $024,000.00
              $017,000.00
              $017,000.00
              $014,000.00
              $013,500.00
    
  4. Change the value of NLS_CURRENCY to '¥':

    ALTER SESSION SET NLS_CURRENCY='¥';
    
  5. Run this query:

    SELECT TO_CHAR(salary, 'L099G999D99') "Salary"
    FROM EMPLOYEES
    WHERE salary > 13000;
    

    Result:

    Salary
    ---------------------
              ¥024,000.00
              ¥017,000.00
              ¥017,000.00
              ¥014,000.00
              ¥013,500.00
    
  6. Set NLS_TERRITORY and NLS_CURRENCY to the values that they had at step 1.

NLS_ISO_CURRENCY Parameter

Specifies: ISO currency symbol (the character string returned by the numeric format element C).

Acceptable Values: Any valid currency symbol string.

Default Value: Set by NLS_TERRITORY, described in NLS_TERRITORY Parameter.

Local currency symbols can be ambiguous, but ISO currency symbols are unique.

The following example shows that the territories AUSTRALIA and AMERICA have the same local currency symbol, but different ISO currency symbols.

Example: NLS_ISO_CURRENCY

  1. Note the current values of NLS_TERRITORY and NLS_ISO_CURRENCY.

  2. If the value of NLS_TERRITORY in step 1 is not AUSTRALIA, change it:

    ALTER SESSION SET NLS_TERRITORY=AUSTRALIA;
    
  3. Run this query:

    SELECT TO_CHAR(salary, 'L099G999D99') "Local",
            TO_CHAR(salary, 'C099G999D99') "ISO"
    FROM EMPLOYEES
    WHERE salary > 15000;
    

    Result:

    Local                 ISO
    --------------------- ------------------
              $024,000.00      AUD024,000.00
              $017,000.00      AUD017,000.00
              $017,000.00      AUD017,000.00
    
  4. Change the value of NLS_TERRITORY to AMERICA:

    ALTER SESSION SET NLS_TERRITORY=AMERICA;
    
  5. Run this query:

    SELECT TO_CHAR(salary, 'L099G999D99') "Local",
            TO_CHAR(salary, 'C099G999D99') "ISO"
    FROM EMPLOYEES
    WHERE salary > 15000;
    

    Result:

    Local                 ISO
    --------------------- ------------------
              $024,000.00      USD024,000.00
              $017,000.00      USD017,000.00
              $017,000.00      USD017,000.00
    
  6. Set NLS_TERRITORY and NLS_ISO_CURRENCY to the values that they had at step 1.

NLS_DUAL_CURRENCY Parameter

Specifies: Dual currency symbol (introduced to support the euro currency symbol during the euro transition period).

Acceptable Values: Any valid currency symbol string.

Default Value: Set by NLS_TERRITORY, described in NLS_TERRITORY Parameter.

NLS_SORT Parameter

Specifies: Linguistic sort order (collating sequence) for queries that have the ORDER BY clause.

Acceptable Values:

  • BINARY

    Sort order is based on the binary sequence order of either the database character set or the national character set, depending on the data type.

  • Any linguistic sort name that Oracle supports

    Sort order is based on the order of the specified linguistic sort name. The linguistic sort name is usually the same as the language name, but not always. For a list of supported linguistic sort names, see Oracle Database Globalization Support Guide.

Default Value: Set by NLS_LANGUAGE, described in NLS_LANGUAGE Parameter.

The following example shows how two different NLS_SORT settings affect the displayed result of the same query. The settings are BINARY and Traditional Spanish (SPANISH_M). Traditional Spanish treats ch, ll, and ñ as letters that follow c, l, and n, respectively.

Example: NLS_SORT Affects Linguistic Sort Order

  1. Create table for Spanish words:

    CREATE TABLE temp (name VARCHAR2(15));
    
  2. Populate table with some Spanish words:

    INSERT INTO temp (name) VALUES ('laguna');
    INSERT INTO temp (name) VALUES ('llama');
    INSERT INTO temp (name) VALUES ('loco');
    
  3. Note the current value of NLS_SORT.

  4. If the value of NLS_SORT in step 3 is not BINARY, change it:

    ALTER SESSION SET NLS_SORT=BINARY;
    
  5. Run this query:

    SELECT * FROM temp ORDER BY name;
    

    Result:

    NAME
    ---------------
    laguna
    llama
    loco
    
  6. Change the value of NLS_SORT to SPANISH_M (Traditional Spanish):

    ALTER SESSION SET NLS_SORT=SPANISH_M;
    
  7. Repeat the query from step 5.

    Result:

    NAME
    ---------------
    laguna
    loco
    llama
    
  8. Drop the table:

    DROP TABLE temp;
    
  9. Set NLS_SORT to the value that it had at step 3.

Case-Insensitive and Accent-Insensitive Sorts

Operations inside Oracle Database are sensitive to the case and the accents (diacritics) of the characters. To perform a case-insensitive sort, append _CI to the value of the NLS_SORT parameter (for example, BINARY_CI or XGERMAN_CI). To perform a sort that is both case-insensitive and accent-insensitive, append _AI to the value of the NLS_SORT parameter (for example, BINARY_AI or FRENCH_M_AI).

NLS_COMP Parameter

Specifies: Character comparison behavior of SQL operations.

Acceptable Values:

  • BINARY

    SQL compares the binary codes of characters. One character is greater than another if it has a higher binary code.

  • LINGUISTIC

    SQL performs a linguistic comparison based on the value of the NLS_SORT parameter, described in NLS_SORT Parameter.

  • ANSI

    This value is provided only for backward compatibility.

Default Value: BINARY

The following example shows that the result of a query can depend on the NLS_COMP setting.

Example: NLS_COMP Affects SQL Character Comparison

  1. Note the current values of NLS_SORT and NLS_COMP.

  2. If the values of NLS_SORT and NLS_COMP in step 1 are not SPANISH_M (Traditional Spanish) and BINARY, respectively, change them:

    ALTER SESSION SET NLS_SORT=SPANISH_M NLS_COMP=BINARY;
    
  3. Run this query:

    SELECT LAST_NAME FROM EMPLOYEES
    WHERE LAST_NAME LIKE 'C%';
    

    Result:

    LAST_NAME                 
    ------------------------- 
    Cabrio                    
    Cambrault                 
    Cambrault                 
    Chen                      
    Chung                     
    Colmenares                
     
    6 rows selected
    
  4. Change the value of NLS_COMP to LINGUISTIC:

    ALTER SESSION SET NLS_COMP=LINGUISTIC;
    
  5. Repeat the query from step 3.

    Result:

    LAST_NAME                 
    ------------------------- 
    Cabrio                    
    Cambrault                 
    Cambrault                 
    Colmenares                
     
    4 rows selected
    

    This time, Chen and Chung are not returned because Traditional Spanish treats ch as a single character that follows c.

  6. Set NLS_SORT and NLS_COMP to the values that they had in step 1.

NLS_LENGTH_SEMANTICS Parameter

Specifies: Length semantics for columns of the character data types CHAR, VARCHAR2, and LONG; that is, whether these columns are specified in bytes or in characters. (Applies only to columns that are declared after the parameter is set.)

Acceptable Values:

  • BYTE

    New CHAR, VARCHAR2, and LONG columns are specified in bytes.

  • CHAR

    New CHAR, VARCHAR2, and LONG columns are specified in characters.

Default Value: BYTE

Example: NLS_LENGTH_SEMANTICS Affects Storage of VARCHAR2 Column

  1. Note the current values of NLS_LENGTH_SEMANTICS.

  2. If the value of NLS_LENGTH_SEMANTICS in step 1 is not BYTE, change it:

    ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;
    
  3. Create a table with a VARCHAR2 column:

    CREATE TABLE SEMANTICS_BYTE(SOME_DATA VARCHAR2(20));
    
  4. Click the tab Connections.

    The Connections pane shows the connection hr_conn.

  5. Expand hr_conn.

    A list of schema object types appears, including Tables.

  6. Expand Tables.

    A list of tables appears, including SEMANTICS_BYTE.

  7. Select SEMANTICS_BYTE.

    To the right of the Connections pane, the Columns pane shows that for Column Name SOME_DATA, the Data Type is VARCHAR2(20 BYTE).

  8. Change the value of NLS_LENGTH_SEMANTICS to CHAR:

    ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR;
    
  9. Create another table with a VARCHAR2 column:

    CREATE TABLE SEMANTICS_CHAR(SOME_DATA VARCHAR2(20));
    
  10. In the Connections pane, click the Refresh icon.

    The list of tables now includes SEMANTICS_CHAR.

  11. Select SEMANTICS_CHAR.

    The Columns pane shows that for Column Name SOME_DATA, the Data Type is VARCHAR2(20 CHAR).

  12. Select SEMANTICS_BYTE again.

    The Columns pane shows that for Column Name SOME_DATA, the Data Type is still VARCHAR2(20 BYTE).

  13. Set the value of NLS_LENGTH_SEMANTICS to the value that it had in step 1.

SQL and PL/SQL Programming with Unicode

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.

SQL is the fundamental language with which all programs and users access data in an Oracle database either directly or indirectly. PL/SQL is a procedural language that combines the data manipulating power of SQL with the data processing power of procedural languages. Both SQL and PL/SQL can be embedded in other programming languages. This section describes Unicode-related features in SQL and PL/SQL that you can deploy for multilingual applications.

This section contains the following topics:


See Also:

  • Oracle Database SQL Language Reference

  • Oracle Database PL/SQL Language Reference


SQL NCHAR Data Types

There are three SQL NCHAR data types:

The NCHAR Data Type

When you define a table column or a PL/SQL variable as the NCHAR data type, the length is always 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:

maximum number of bytes = (maximum number of characters) x (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 NCHAR data is 32767 bytes. You can define an NCHAR variable of up to 32767 characters, but the actual data cannot exceed 32767 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.


Note:

UTF8 may affect performance because it is a variable-width character set. Excessive blank padding of NCHAR fields decreases performance. Consider using the NVARCHAR2 data type or changing to the AL16UTF16 character set for the NCHAR data type.

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 always in units of characters, just as for NCHAR. Oracle subsequently stores each value in the column exactly as you specify it, if the value does not exceed the column's maximum length. Oracle 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 2000 when it is 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 actually 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 32767 bytes. You can define NVARCHAR2 variables up to 32767 characters, but the actual data cannot exceed 32767 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)); 

The NCLOB Data Type

NCLOB is a character large object containing Unicode characters, with a maximum size of 4 gigabytes. Unlike the BLOB data type, the NCLOB data type has full transactional support so that changes made through SQL, the DBMS_LOB package, or OCI participate fully in transactions. Manipulations of NCLOB value can be committed and rolled back. Note, however, that you cannot save an NCLOB locator in a PL/SQL or OCI variable in one transaction and then use it in another transaction or session.

NCLOB values are stored in the database in a format that is compatible with UCS-2, regardless of the national character set. Oracle translates the stored Unicode value to the character set requested on the client or on the server, which can be fixed-width or variable-width. When you insert data into an NCLOB column using a variable-width character set, Oracle converts the data into a format that is compatible with UCS-2 before storing it in the database.


See Also:

Oracle Database SecureFiles and Large Objects Developer's Guide for more information about the NCLOB data type

Implicit Data Type Conversion Between NCHAR and Other Data Types

Oracle supports implicit conversions between SQL NCHAR data types and other Oracle data types, such as CHAR, VARCHAR2, NUMBER, DATE, ROWID, and CLOB. Any implicit conversions for CHAR and VARCHAR2 data types are also supported for SQL NCHAR data types. You can use SQL NCHAR data types the same way as SQL CHAR data types.

Type conversions between SQL CHAR data types and SQL NCHAR data types may involve character set conversion when the database and national character sets are different. Padding with blanks may occur if the target data is either CHAR or NCHAR.

Exception Handling for Data Loss During Data Type Conversion

Data loss can occur during data type conversion when character set conversion is necessary. If a character in the source character set is not defined in the target character set, then a replacement character is used in its place. For example, if you try to insert NCHAR data into a regular CHAR column and the character data in NCHAR (Unicode) form cannot be converted to the database character set, then the character is replaced by a replacement character defined by the database character set. The NLS_NCHAR_CONV_EXCP initialization parameter controls the behavior of data loss during character type conversion. When this parameter is set to TRUE, any SQL statements that result in data loss return an ORA-12713 error and the corresponding operation is stopped. When this parameter is set to FALSE, data loss is not reported and the unconvertible characters are replaced with replacement characters. The default value is TRUE. This parameter works for both implicit and explicit conversion.

In PL/SQL, when data loss occurs during conversion of SQL CHAR and NCHAR data types, the LOSSY_CHARSET_CONVERSION exception is raised for both implicit and explicit conversion.

Rules for Implicit Data Type Conversion

In some cases, conversion between data types is possible in only one direction. In other cases, conversion in both directions is possible. Oracle defines a set of rules for conversion between data types. Table: Rules for Conversion Between Data Types contains the rules for conversion between data types.

Rules for Conversion Between Data Types

Statement Rule

INSERT/UPDATE statement

Values are converted to the data type of the target database column.

SELECT INTO statement

Data from the database is converted to the data type of the target variable.

Variable assignments

Values on the right of the equal sign are converted to the data type of the target variable on the left of the equal sign.

Parameters in SQL and PL/SQL functions

CHAR, VARCHAR2, NCHAR, and NVARCHAR2 are loaded the same way. An argument with a CHAR, VARCHAR2, NCHAR or NVARCHAR2 data type is compared to a formal parameter of any of the CHAR, VARCHAR2, NCHAR or NVARCHAR2 data types. If the argument and formal parameter data types do not match exactly, then implicit conversions are introduced when data is copied into the parameter on function entry and copied out to the argument on function exit.

Concatenation || operation or CONCAT function

If one operand is a SQL CHAR or NCHAR data type and the other operand is a NUMBER or other non-character data type, then the other data type is converted to VARCHAR2 or NVARCHAR2. For concatenation between character data types, see the row in this table for SQL NCHAR data types and SQL CHAR data types.

SQL CHAR or NCHAR data types and NUMBER data type

Character values are converted to NUMBER data type.

SQL CHAR or NCHAR data types and DATE data type

Character values are converted to DATE data type.

SQL CHAR or NCHAR data types and ROWID data type

Character values are converted to ROWID data type.

SQL NCHAR data types and SQL CHAR data types

Comparisons between SQL NCHAR data types and SQL CHAR data types are more complex because they can be encoded in different character sets.

When CHAR and VARCHAR2 values are compared, the CHAR values are converted to VARCHAR2 values.

When NCHAR and NVARCHAR2 values are compared, the NCHAR values are converted to NVARCHAR2 values.

When there is comparison between SQL NCHAR data types and SQL CHAR data types, character set conversion occurs if they are encoded in different character sets. The character set for SQL NCHAR data types is always Unicode and can be either UTF8 or AL16UTF16 encoding, which have the same character repertoires but are different encodings of the Unicode standard. SQL CHAR data types use the database character set, which can be any character set that Oracle supports. Unicode is a superset of any character set supported by Oracle, so SQL CHAR data types can always be converted to SQL NCHAR data types without data loss.


SQL Functions for Unicode Data Types

SQL NCHAR data types can be converted to and from SQL CHAR data types and other data types using explicit conversion functions. The examples in this section use the table created by the following statement:

CREATE TABLE customers 
  (id NUMBER, name NVARCHAR2(50), address NVARCHAR2(200), birthdate DATE);

Populating the Customers Table Using the TO_NCHAR Function

The TO_NCHAR function converts the data at runtime, while the N function converts the data at compilation time.

INSERT INTO customers VALUES (1000, 
  TO_NCHAR('John Smith'),N'500 Oracle Parkway',sysdate);

Selecting from the Customer Table Using the TO_CHAR Function

The following statement converts the values of name from characters in the national character set to characters in the database character set before selecting them according to the LIKE clause:

SELECT name FROM customers WHERE TO_CHAR(name) LIKE '%Sm%';

You should see the following output:

NAME
--------------------------------------
John Smith

Selecting from the Customer Table Using the TO_DATE Function

Using the N function shows that either NCHAR or CHAR data can be passed as parameters for the TO_DATE function. The data types can mixed because they are converted at runtime.

DECLARE
ndatestring NVARCHAR2(20) := N'12-SEP-1975';
ndstr NVARCHAR2(50);
BEGIN
SELECT name INTO ndstr FROM customers
WHERE (birthdate)> TO_DATE(ndatestring, 'DD-MON-YYYY', N'NLS_DATE_LANGUAGE =
AMERICAN');
END;

As demonstrated in Example: Selecting from the Customer Table Using the TO_DATE Function, SQL NCHAR data can be passed to explicit conversion functions. SQL CHAR and NCHAR data can be mixed together when using multiple string parameters.


See Also:

Oracle Database SQL Language Reference for more information about explicit conversion functions for SQL NCHAR data types

Other SQL Functions

Most SQL functions can take arguments of SQL NCHAR data types as well as mixed character data types. The return data type is based on the type of the first argument. If a non-string data type like NUMBER or DATE is passed to these functions, then it is converted to VARCHAR2. The following examples use the customer table created in "SQL Functions for Unicode Data Types".

INSTR Function

In this example, the string literal 'Sm' is converted to NVARCHAR2 and then scanned by INSTR, to detect the position of the first occurrence of this string in name.

SELECT INSTR(name, N'Sm', 1, 1) FROM customers;

CONCAT Function

SELECT CONCAT(name,id) FROM customers;

id is converted to NVARCHAR2 and then concatenated with name.

RPAD Function

SELECT RPAD(name,100,' ') FROM customers;

The following output results:

RPAD(NAME,100,'')
------------------------------------------
John Smith

The space character ' ' is converted to the corresponding character in the NCHAR character set and then padded to the right of name until the total display length reaches 100.

Unicode String Literals

You can input Unicode string literals in SQL and PL/SQL as follows:

  • Put a prefix N before a string literal that is enclosed with single quote marks. This explicitly indicates that the following string literal is an NCHAR string literal. For example, N'résumé' is an NCHAR string literal. For information about limitations of this method, see NCHAR String Literal Replacement.

  • Use the NCHR(n) SQL function, which 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 run with 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. UNISTR('string') 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 String Literal Replacement

This section provides information on how to avoid data loss when performing NCHAR string literal replacement.

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, which is determined by the client character set defined in NLS_LANG, or specified in the OCIEnvNlsCreate() call, or predefined as UTF-16 in JDBC. 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 server, its character set is converted accordingly. It is important to note that if the database character set does not contain all characters used in the text literals, then the data is lost in this conversion. This problem affects NCHAR string literals more than the CHAR text literals. This is because the N' literals are designed to be independent of the database character set, and should be able to provide any data that the client character set supports.

    To avoid data loss in conversion to an incompatible database character set, you can activate the NCHAR literal replacement functionality. The functionality transparently replaces the N' literals on the client side with an internal format. The database server then decodes this to Unicode when the statement is executed.

  • Because many applications, for example, SQL*Plus, use OCI to connect to a database, and they do not control NCHAR literal replacement explicitly, you can set the client environment variable ORA_NCHAR_LITERAL_REPLACE to TRUE to control the functionality for them. By default, the functionality is switched off to maintain backward compatibility.

Using the UTL_FILE Package with NCHAR Data

The UTL_FILE package handles Unicode national character set data of the NVARCHAR2 data type. NCHAR and NCLOB are supported through implicit conversion. The functions and procedures include the following:

  • FOPEN_NCHAR

    This function opens a file in national character set mode for input or output, with the maximum line size specified. Even though the contents of an NVARCHAR2 buffer may be AL16UTF16 or UTF8 (depending on the national character set of the database), the contents of the file are always read and written in UTF8. UTL_FILE converts between UTF8 and AL16UTF16 as necessary.

  • GET_LINE_NCHAR

    This procedure reads text from the open file identified by the file handle and places the text in the output buffer parameter. The file must be opened in national character set mode, and must be encoded in the UTF8 character set. The expected buffer data type is NVARCHAR2. If a variable of another data type, such as NCHAR, NCLOB, or VARCHAR2 is specified, PL/SQL will perform standard implicit conversion from NVARCHAR2 after the text is read.

  • PUT_NCHAR

    This procedure writes the text string stored in the buffer parameter to the open file identified by the file handle. The file must be opened in the national character set mode. The text string will be written in the UTF8 character set. The expected buffer data type is NVARCHAR2. If a variable of another data type is specified, PL/SQL will perform implicit conversion to NVARCHAR2 before writing the text.

  • PUT_LINE_NCHAR

    This procedure is equivalent to PUT_NCHAR, except that the line separator is appended to the written text.

  • PUTF_NCHAR

    This procedure is a formatted version of a PUT_NCHAR procedure. It accepts a format string with formatting elements \n and %s, and up to five arguments to be substituted for consecutive instances of %s in the format string. The expected data type of the format string and the arguments is NVARCHAR2. If variables of another data type are specified, PL/SQL will perform implicit conversion to NVARCHAR2 before formatting the text. Formatted text is written in the UTF8 character set to the file identified by the file handle. The file must be opened in the national character set mode.

The preceding functions and procedures process text files encoded in the UTF8 character set, that is, in the Unicode CESU-8 encoding. The functions and procedures convert between UTF8 and the national character set of the database, which can be UTF8 or AL16UTF16, as needed.


See Also:

Oracle Database PL/SQL Packages and Types Reference for more information about the UTL_FILE package