8 Including Globalization Support

This chapter discusses global application development with Oracle Database XE 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.

This chapter contains the following sections:

Introduction toGlobal Applications
Developing Global Applications with the .NET Framework
Presenting Data in the Correct User Local Convention
Synchronizing the .NET and Oracle Database Locale Environments
Client Globalization Support in Oracle Data Provider for .NET

Introduction to Global Applications

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 using the correct date and number formats. Oracle Database Express 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:

Globalization is the process of designing applications that can adapt to different cultures.
Localization is the process of translating resources for a specific culture.

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's 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' 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 to which the user is accustomed 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 XE offers many features that refine the presentation of data when the user locale preference is known.

Oracle Date Formats

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

Using SQLPlus, connect to the database and enter the command in Example 8-1 at the SQL prompt.
Example 8-1 Setting NLS_TERRITORY and NLS_LANGUAGE Parameters: United States
```
SQL> ALTER SESSION SET NLS_TERRITORY=america NLS_LANGUAGE=american;
```
This message appears: Session altered.

At the SQL prompt, enter the query in Example 8-2.

Example 8-2 Testing the NLS Date Format Settings

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

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

Description of the illustration nls01.gif
Enter the command in Example 8-3 at the SQL prompt.
Example 8-3 Setting NLS_TERRITORY and NLS_LANGUAGE Parameters: Germany
```
SQL> ALTER SESSION SET NLS_TERRITORY=germany NLS_LANGUAGE=german;
```
This message appears: Session wurde geändert.
At the SQL prompt, enter the query in Example 8-2.
The result of the query returns in the German format specified in Step 4.

Description of the illustration nls02.gif

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.

Enter the command in Example 8-4 at the SQL prompt.
Example 8-4 Setting NLS_TERRITORY Parameter: United States
```
SQL> ALTER SESSION SET NLS_TERRITORY=america;
```
This message appears: Session altered.

At the SQL prompt, enter the query in Example 8-5.

Example 8-5 Testing NLS Number Format Settings

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

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

Description of the illustration nls03.gif
Enter the command in Example 8-6 at the SQL prompt.
Example 8-6 Setting NLS_TERRITORY Parameter: Germany
```
SQL> ALTER SESSION SET NLS_TERRITORY=germany;
```
This message appears: Session altered.
At the SQL prompt, enter the query in Example 8-5.
The result of the query returns in the German format specified in Step 4.

Description of the illustration nls04.gif

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 and Chung.

Enter the command in Example 8-7 at the SQL prompt.
Example 8-7 Setting NLS_SORT Parameter: Binary
```
SQL> ALTER SESSION SET NLS_SORT=binary;
```
This message appears: Session altered.

At the SQL prompt, enter the query in Example 8-8.

Example 8-8 Testing NLS Sort Order Settings

SQL> SELECT employee_id "ID",
  2         last_name "Name",
  3 FROM employees
  4 WHERE employee_id < 105;

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

Description of the illustration nls05.gif
Enter the command in Example 8-9 at the SQL prompt.
Example 8-9 Setting NLS_SORT Parameter: Spanish
```
SQL> ALTER SESSION SET NLS_SORT=spanish_m;
```
This message appears: Session altered.
At the SQL prompt, enter the query in Example 8-8.
The result of the query returns in the Spanish sort specified in Step 4.

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:

Enter the command in Example 8-10 at the SQL prompt.
Example 8-10 Setting NLS_LANGUAGE Parameter: United States
```
SQL> ALTER SESSION SET NLS_LANGUAGE=american;
```
This message appears: Session altered.
At the SQL prompt, enter the query in Example 8-11.
Example 8-11 Testing NLS Error Messages Settings
```
SQL> SELECT * FROM managers;
```
The result of the query return the error message in the language specified in Step 1.

Description of the illustration nls07.gif
Enter the command in Example 8-9 at the SQL prompt.
Example 8-12 Setting NLS_LANGUAGE Parameter: French
```
SQL> ALTER SESSION SET NLS_LANGUAGE=french;
```
This message appears: Session modifée.
At the SQL prompt, enter the query in Example 8-11.
The result of the query returns the error message in the language specified in Step 4.

Description of the illustration nls08.gif

Synchronizing the .NET and Oracle Database Locale Environments

When you are developing global applications across different programming environments, ensure that the user locale settings are always synchronized. Otherwise, the application may present conflicting culture-sensitive information. For example, a .NET application must map the application user's Culture ID to the correct NLS_LANGUAGE and NLS_TERRITORY parameter values before performing SQL operations.

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

Table 8-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 KINGDO`M
French (Canada)	`fr-CA`	`CANADIAN FRENCH`	`CANADA`
French (France)	`fr-FR`	`FRENCH`	`FRANCE`
German	`de`	`GERMAN`	`GERMANY`
Italian	`it`	`ITALIAN`	`ITALY`
Japanese	j`a`	`JAPANESE`	`JAPAN`
Korean	`ko`	`KOREAN`	`KOREA`
Portuguese (Brazil)	`pt-BR`	`BRAZILIAN PORTUGUESE`	`BRAZI`L
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 XE. 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 call Open() establishes a connection, it implicitly opens a session with globalization parameters specified by the value of the NLS_LANG parameter.

Client Globalization Settings

Client globalization settings derive from the Oracle globalization setting, NLS_LANG, in the Windows Registry of the local computer. The client globalization parameter settings are read-only and remain constant throughout the lifetime of the application.

Example 8-13 and Example 8-14 illustrate how these settings can be obtained by calling the OracleGlobalization.GetClientInfo() static method. The properties of the OracleGlobalization object provide the Oracle globalization value settings.

Example 8-13 How to Obtain Oracle Globalization Settings: 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);
  }
}

Example 8-14 How to Obtain Oracle Globalization Settings: VB

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

Session Globalization Settings

Session globalization parameters are initially identical to client globalization settings, but they can be updated. To obtain session globalization settings, first establish a connection to the database, and then call the GetSessionInfo() method of an OracleConnection object. The properties of the resulting OracleGlobalization object represent the globalization settings of the session.

When the OracleConnection object establishes a connection, it implicitly opens a session in which globalization parameters are initialized with values specified by the client's Oracle Globalization (or NLS) Registry settings. The globalization settings of a session can change during its lifetime.

To change globalization session settings programmatically, follow these steps:

Follow the steps in Section "Copying a Project" to create a new copy of the HR_DataSet_ODP_CS (or HR_DataSet_ODP_VB) project. Name the new project HR_Globalization_CS (or HR_Globalization_VB).
Open Form1 of the HR_Globalization_CS (or HR_Globalization_VB) project, and switch to design view (use the Shift+F7 keyboard shortcut).
From the View menu, select Toolbox.
From the Toolbox, under Windows Forms, drag and drop a Button onto Form1.
Right-click the new Button, select Properties. A Properties window appears.
In the Properties window, set these properties:
- Under Appearance, change Text to Change Date Format.
- Under Design, change (Name) to date.
- Click the lightning icon (events), and then click the highlighted Click event. From the drop-down window, select date_Click.
Close the Properties window.

Description of the illustration nls09.gif
Switch to code view using the F7 keyboard shortcut.
Within the Form1() method, add the code shown in Example 8-15 and Example 8-16.
Example 8-15 Disabling the Change Button: C#
```
date.Enabled = false;
```
Example 8-16 Disabling the Change Button: VB
```
date.Enabled = false
```
Description of the illustration nls10.gif
In the Form1 class declarations, add the code shown in Example 8-17 or Example 8-18.
Example 8-17 Creating an OracleGlobalization Object: C#
```
private OracleGlobalization si;
```
Example 8-18 Creating an OracleGlobalization Object: VB
```
private si As OracleGlobalization
```
Description of the illustration nls11.gif

Within the connect_Click() method try block, as shown in Example 8-19 and Example 8-20,

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.

Example 8-19 Retrieving the Globalization Session Information: C#

conn.Open();
connect.Enabled = false;

si = conn.GetSessionInfo();
 
string sql = "select employee_id, first_name, last_name, TO_CHAR(hire_date)
   from employees where employee_id < 105";
OracleCommand 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.Enabled = true;

Example 8-20 Retrieving the Globalization Session Information: VB

conn.Open()
connect.Enabled = false
 
string sql = "select employee_id, first_name, last_name, TO_CHAR(hire date)
   from employees where employee_id < 105"
OracleCommand 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.Enabled = true

Your code also contains a new date_Click() method created in Step 6. Add to the method the code for changing the date format from the standard DD-MON-RR to YYYY-MM-DD , and for updating the DataSet, shown in Example 8-21 and Example 8-22. Note that the ds.Clear() call clears the old results before posting the changed data.
Example 8-21 Changing the Date Format and Updating the DataSet: C#
```
si.DateFormat = "YYYY-MM-DD";
conn.SetSessionInfo(si);
 
ds.Clear();
da.Fill(ds);
departments.DataSource = ds.Tables[0];
```
Example 8-22 Changing the Date Format and Updating the DataSet: VB
```
si.DateFormat = "YYYY-MM-DD"
conn.SetSessionInfo(si)
 
ds.Clear()
da.Fill(ds)
departments.DataSource = ds.Tables[0]
```
Save Form1 using Ctr+S keyboard shortcut.
Run the application using the F5 keyboard shortcut.
After you successfully connect to the database, the data grid is populated with the results of the query. Click Change Date Format.

Description of the illustration nls12.gif
Note that the date format is changed from the original DD-MON-RR to YYYY-MM-DD.

Description of the illustration nls13.gif
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 are 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 thread's globalization settings.

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.