Skip Headers
Oracle® Database 2 Day + .NET Developer's Guide
11g Release 2 (11.2)

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

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

9 Including Globalization Support

This chapter contains:

See Also:

Introduction to Global Applications

This chapter discusses global application development with Oracle Database in .NET. It addresses the basic tasks associated with developing applications that are ready for global deployment, such as developing locale awareness and presenting data with cultural conventions of the user's locale. It also discusses globalization support features available in Oracle Data Provider for .NET.

Building a global-ready application that supports different locales requires good development practices.

A locale refers to a national language and the region in which the language is spoken. The application itself must be aware of the user's locale preference and be able to present content following the cultural convention expected by the user. It is important to present data with appropriate locale characteristics, such as the correct date and number formats. Oracle Database is fully internationalized to provide a global platform for developing and deploying global applications.

Developing Global Applications with the .NET Framework

When planning a global-ready application, you have to consider two main tasks:

In the .NET Framework, the System.Globalization namespace contains classes that define information related to culture, such as language, country and region, calendars, format patterns for dates, currency, and numbers, and the sort order for strings. These classes simplify the process of developing a global-ready application, so that passing a CultureInfo object that represents the user's culture to methods in System.Globalization namespace initiates the correct set of rules and data.

The .NET Framework also supports the creation and localization of resources, and offers a model for packaging and deploying them. Localizing the application's resources for specific cultures supports development of translated versions of the application. The .NET Framework base class library provides several classes in the System.Resources namespace for building and manipulating application resources.

Presenting Data in the Correct User Local Convention

Data in the application must be presented in a way that meets the user's expectations, or its meaning can be misinterpreted. For example, 12/11/05 implies December 11, 2005 in the United States and November 12, 2005 in the United Kingdom. Similar confusion exists for number and monetary formats. For example, the period (.) is a decimal separator in the United States and a thousand separator throughout Europe.

Different languages have their own sorting rules: some languages are collated according to the letter sequence in the alphabet, others according to stroke count in the letter, still others are ordered by the pronunciation of the words. Presenting data that is not sorted according to the linguistic sequence that the user is accustomed to can make searching for information difficult and time-consuming.

Depending on the application logic and the volume of data retrieved from the database, it may be more appropriate to format the data at the database level rather than at the application level. Oracle Database offers many features that refine the presentation of data when the user locale preference is known.

Connecting to SQL*Plus

Several of the following examples require that you use SQL*Plus to connect as a user with database administrator privileges such as SYS or SYSTEM.

See Also:

"Locking and Unlocking User Accounts" in the Oracle Database 2 Day DBA for further information

Using Oracle Date Formats

There are three different date presentation formats in Oracle Database: standard, short, and long. The following steps illustrate the difference between the short and long date formats for United States and Germany.

To change the Oracle date format:

  1. From a Windows command prompt, enter the following

    C:\>sqlplus "sys as sysdba"
    Enter password:passwd
    

    where passwd is the Sys password that was established when the database was installed. The password does not appear when you type the characters. Description of nls00.gif follows
    Description of the illustration nls00.gif

  2. Enter this command at the SQL prompt:

    SQL> ALTER SESSION SET NLS_TERRITORY=america NLS_LANGUAGE=american;
    

    This message appears: Session altered.

    There is no problem with setting a parameter to its current setting. You may want to do this for security. To determine what your current settings are enter:

    SQL> select * from v$nls_parameters;  
    

    or

    select * from v$nls_parameters where parameter = 'NLS_LANGUAGE';
    
  3. At the SQL prompt, enter the following query:

    SQL> SELECT employee_id "ID",
      SUBSTR (first_name,1,1)||'. '||last_name "Name",
      TO_CHAR (hire_date, 'DS') "Short Hire",
      TO_CHAR (hire_date, 'DL') "Long Hire Date"
      FROM hr.employees
      WHERE employee_id < 105;
    

    Note that you must use hr.employees in order to access the employees table in the hr schema because you are currently logged in as sys, not hr.

    The result of the query returns in the American format specified in Step 1.

    Description of nls01.gif follows
    Description of the illustration nls01.gif

  4. Enter the following command at the SQL prompt:

    SQL> ALTER SESSION SET NLS_TERRITORY=germany NLS_LANGUAGE=german;
    

    This message appears: Session altered.

  5. At the SQL prompt, enter the query from Step 3.

    The result of the query returns in the German format specified in Step 4.

    Description of nls02.gif follows
    Description of the illustration nls02.gif

Using Oracle Number Formats

There are also differences in the decimal character and group separator. The following steps illustrate these difference between United States and Germany.

To change the Oracle number format:

  1. Enter the following command at the SQL prompt:

    SQL> ALTER SESSION SET NLS_TERRITORY=america NLS_LANGUAGE=american;
    

    This message appears: Session altered.

  2. At the SQL prompt, enter the following query:

    SQL> SELECT employee_id "ID",
      SUBSTR (first_name,1,1)||'. '||last_name "Name",
      TO_CHAR (salary, '99G999D99') "Salary"
      FROM hr.employees
      WHERE employee_id < 105;
    

    The result of the query returns in the American format specified in Step 1.

    Description of nls03.gif follows
    Description of the illustration nls03.gif

  3. Enter the following command at the SQL prompt:

    SQL> ALTER SESSION SET NLS_TERRITORY=germany;
    

    This message appears: Session altered.

  4. At the SQL prompt, enter the query in Step 2.

    The result of the query returns in the German format specified in Step 3.

    Description of nls04.gif follows
    Description of the illustration nls04.gif

Using Oracle Linguistic Sorts

Spain traditionally treats ch, ll, and ñ as letters of their own, ordered after c, l and n, respectively. The following steps illustrate the effect of using a Spanish sort against the employee names Chen, Chung, and Colmenares.

To change the Oracle linguistic sort:

  1. Enter the following command at the SQL prompt.

    SQL> ALTER SESSION SET NLS_SORT=binary;
    

    This message appears: Session altered.

  2. At the SQL prompt, enter the following query:

    SQL> SELECT employee_id "ID",
              last_name "Name"
      FROM hr.employees
      WHERE last_name LIKE 'C%'
      ORDER BY last_name;
    

    The result of the query returns in the binary sort specified in Step 1.

    Description of nls05.gif follows
    Description of the illustration nls05.gif

  3. Enter the following command at the SQL prompt.

    SQL> ALTER SESSION SET NLS_SORT=spanish_m;
    

    This message appears: Session altered.

  4. At the SQL prompt, enter the query in Step 2.

  5. The result of the query returns in the Spanish sort specified in Step 3.

    Description of nls06.gif follows
    Description of the illustration nls06.gif

Oracle Error Messages

The NLS_LANGUAGE parameter also controls the language of the database error messages. Setting this parameter prior to submitting a SQL query ensures the return of local language-specific error messages, as shown in these steps:

To change the Oracle NLS language parameter:

  1. Enter the following command at the SQL prompt.

    SQL> ALTER SESSION SET NLS_LANGUAGE=american;
    

    This message appears: Session altered.

  2. At the SQL prompt, enter the following query.

    SQL> SELECT * FROM managers;
    

    The result of the query return the error message in the language specified in Step 1.

    Description of nls07.gif follows
    Description of the illustration nls07.gif

  3. Enter the following command at the SQL prompt.

    SQL> ALTER SESSION SET NLS_LANGUAGE=french;
    

    This message appears: Session altered.

  4. At the SQL prompt, enter the query in Step 2.

    The result of the query returns the error message in the language specified in Step 3.

    Description of nls08.gif follows
    Description of the illustration nls08.gif

  5. Reset your language, local, and sort settings back to their original values.

Synchronizing the .NET and Oracle Database Locale Environments

When you are developing global applications, always synchronize the user locale settings between the database and clients. Otherwise, the application may present conflicting culture-sensitive information. For example, a .NET application must map the Culture ID of the application user to the correct NLS_LANGUAGE and NLS_TERRITORY parameter values before performing SQL operations.

Table 9-1 shows some of the more common locales, as defined in .NET and Oracle environments.

Table 9-1 Common NLS_LANGUAGE and NLS_TERRITORY Parameters

Culture Culture ID NLS_LANGUAGE NLS_TERRITORY

Chinese (P.R.C.)

zh-CN

SIMPLIFIED CHINESE

CHINA

Chinese (Taiwan)

zh-TW

TRADITIONAL CHINESE

TAIWAN

English (U.S.A.)

en-US

AMERICAN

AMERICA

English (U.K.)

en-GB

ENGLISH

UNITED KINGDOM

French (Canada)

fr-CA

CANADIAN FRENCH

CANADA

French (France)

fr-FR

FRENCH

FRANCE

German

de

GERMAN

GERMANY

Italian

it

ITALIAN

ITALY

Japanese

ja

JAPANESE

JAPAN

Korean

ko

KOREAN

KOREA

Portuguese (Brazil)

pt-BR

BRAZILIAN PORTUGUESE

BRAZIL

Portuguese

pt

PORTUGUESE

PORTUGAL

Spanish

es

SPANISH

SPAIN


Client Globalization Support in Oracle Data Provider for .NET

Oracle Data Provider for .NET enables applications to manipulate culture-sensitive data, such as ensuring proper string format, date, time, monetary, numeric, sort order, and calendar support using culture conventions defined in the Oracle Database. The default globalization settings are determined by the client's NLS_LANG parameter, which is defined in the Windows Registry of the local computer. When the OracleConnection Open method establishes a connection, it implicitly opens a session with globalization parameters specified by the value of the NLS_LANG parameter.

Client Globalization Settings

The client globalization parameter settings are read-only and remain constant throughout the lifetime of the application. Changing the OracleGlobalization object properties does not change the globalization settings of the session or the thread. The following sections describe how to modify the globalization settings at the session and thread level.

Your .NET application can obtain globalization settings by calling the OracleGlobalization.GetClientInfo() static method. The OracleGlobalization sample code below demonstrates how to obtain some of the values in .NET.

Visual C#:

using System;
using Oracle.DataAccess.Client; 
 
class ClientGlobalizationSample
{
  static void Main()
  {
    OracleGlobalization ClientGlob = OracleGlobalization.GetClientInfo();
    Console.WriteLine("Client machine language: " + ClientGlob.Language);
    Console.WriteLine("Client characterset: " + ClientGlob.ClientCharacterSet);
  }
}

Visual Basic:

Imports System
Imports Oracle.DataAccess.Client
 
Class ClientGlobalizationSample
  Shared Sub Main()
    Dim ClientGlob As OracleGlobalization = OracleGlobalization.GetClientInfo()
    Console.WriteLine("Client machine language: " + ClientGlob.Language)
    Console.WriteLine("Client characterset: " + ClientGlob.ClientCharacterSet)
  End Sub
End Class

Using Session Globalization Settings

Session globalization parameters are initially identical to client globalization settings, but they can be modified. To modify the session parameters, you must establish a connection to the database, and then call the GetSessionInfo() method of an OracleConnection object to retrieve the session globalization settings. Next, you modify the globalization settings as needed, then save the settings back to the OracleConnection object through the SetSessionInfo(OracleGlobalization) method.

To specify the globalization session setting:

  1. Open the application HR_Connect_CS or HR_Connect_VB.

  2. Make a copy of Form3.xx, which you finished at the end of Chapter 4 and name it Form5.xx, following the instructions in Appendix B, "Copying a Form".

  3. Open Form1 of the project, and switch to design view.

  4. From the View menu, select Toolbox.

  5. From the Toolbox, under Windows Forms, drag and drop a Button onto Form1.

  6. Right-click the new Button, select Properties. The Properties window appears.

  7. In the Properties window, set these properties:

    In the properties window, if you click Events (lightning bolt icon), date_change_Click() now shows as the Event for the date button.

  8. Open the new date_change_Click() method just created and add the following code to change the date format from the standard DD-MON-RR to YYYY-MM-DD and to update the DataSet.

    Visual C#:

    si.DateFormat = "YYYY-MM-DD";
    conn.SetSessionInfo(si);
     
    ds.Clear();
    da.Fill(ds);
    departments.DataSource = ds.Tables[0];
    

    Visual Basic:

    si.DateFormat = "YYYY-MM-DD"
    conn.SetSessionInfo(si)
     
    ds.Clear()
    da.Fill(ds)
    departments.DataSource = ds.Tables(0)
    

    Note that the ds.Clear() call will clear the old results before posting the changed data.

    Also, the si class variable will be declared and session globalization information retrieved in Step 10 and Step 11.

  9. Within the appropriate method, add the code indicated.

    Visual C#: In the Form1() method

    date_change.Enabled = false;
    

    Visual Basic: In the Form1_Load method

    date_change.Enabled = false
    
    Description of nls10.gif follows
    Description of the illustration nls10.gif

  10. Add the following class variable to the existing Form1 class declarations right after the public Form1() block with this code as indicated.

    Visual C#:

    private OracleGlobalization si;
    

    Visual Basic:

    private si As OracleGlobalization
    
    Description of nls11.gif follows
    Description of the illustration nls11.gif

  11. Within the connect_Click() method try block, add the indicated code which does the following:

    • Retrieve the value of the OracleGlobalization object.

    • Retrieve data from the EMPLOYEES table (note the new query).

    • Enable the Change Date Format button.

    The changed code is in bold typeface.

    Visual C#:

    conn.Open();
    connect.Enabled = false;
    
    si = conn.GetSessionInfo();
     
    string sql = "select employee_id, first_name, last_name, TO_CHAR(hire_date)" +
       " \"Hire Date\" from employees where employee_id < 105";
    cmd = new OracleCommand(sql, conn);
    cmd.CommandType = CommandType.Text;
    
    da = new OracleDataAdapter(cmd);
    cb = new OracleCommandBuilder(da);
    ds = new DataSet();
     
    da.Fill(ds);
     
    departments.DataSource = ds.Tables[0];
     
    save.Enabled = true;
    date_change.Enabled = true;
    

    Visual Basic:

    conn.Open()
    connect.Enabled = false
    
    si = conn.GetSessionInfo()
    
    Dim sql As String = "select employee_id, first_name, last_name, " & _
        "TO_CHAR(hire_date) ""Hire Date"" from employees where employee_id < 105"
    cmd = new OracleCommand(sql, conn)
    cmd.CommandType = CommandType.Text
    
    da = new OracleDataAdapter(cmd)
    cb = new OracleCommandBuilder(da)
    ds = new DataSet()
     
    da.Fill(ds)
     
    departments.DataSource = ds.Tables[0]
     
    save.Enabled = true
    date_change.Enabled = true
    
  12. Save Form1.

  13. Run the application using the F5 keyboard shortcut.

    The application successfully connects to the database so the data grid is populated with the results of the query.

    Description of nls12.gif follows
    Description of the illustration nls12.gif

  14. Click Change Date Format.

    Description of nls13.gif follows
    Description of the illustration nls13.gif

    Note that the date format changed from the original DD-MON-RR to YYYY-MM-DD.

  15. Close the application.

Thread-Based Globalization Settings

Thread-based globalization parameter settings are specific to each thread. Initially, these settings are identical to the client globalization parameters, but they can be changed programmatically. When converting ODP.NET Types to and from strings, use the thread-based globalization parameters, if applicable.

Thread-based globalization parameter settings are obtained by calling the GetThreadInfo() static method of the OracleGlobalization class. A call to SetThreadInfo() static method sets the globalization settings of the thread.

ODP.NET classes and structures rely solely on the OracleGlobalization settings when manipulating culture-sensitive data. They do not use .NET thread culture information. If the application uses only .NET types, OracleGlobalization settings have no effect. However, when conversions are made between ODP.NET Types and .NET Types, OracleGlobalization settings are used where applicable.

Note:

Changes to the System.Threading.Thread. CurrentThread.CurrentCulture property do not impact the OracleGlobalization settings of the thread or the session. The reverse is also true.