9 Including Globalization Support
This chapter contains:
-
Synchronizing the .NET and Oracle Database Locale Environments
-
Client Globalization Support in Oracle Data Provider for .NET
|
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:
-
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 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:
-
From a Windows command prompt, enter the following
C:\>sqlplus "sys as sysdba" Enter password:passwdwhere
passwdis theSyspassword that was established when the database was installed. The password does not appear when you type the characters.
Description of the illustration nls00.gif
-
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';
-
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.employeesin order to access theemployeestable in thehrschema because you are currently logged in assys, nothr.The result of the query returns in the American format specified in Step 1.
Description of the illustration nls01.gif
-
Enter the following command at the SQL prompt:
SQL> ALTER SESSION SET NLS_TERRITORY=germany NLS_LANGUAGE=german;
This message appears:
Session altered. -
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 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:
-
Enter the following command at the SQL prompt:
SQL> ALTER SESSION SET NLS_TERRITORY=america NLS_LANGUAGE=american;
This message appears:
Session altered. -
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 the illustration nls03.gif
-
Enter the following command at the SQL prompt:
SQL> ALTER SESSION SET NLS_TERRITORY=germany;
This message appears:
Session altered. -
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 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:
-
Enter the following command at the SQL prompt.
SQL> ALTER SESSION SET NLS_SORT=binary;
This message appears:
Session altered. -
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 the illustration nls05.gif
-
Enter the following command at the SQL prompt.
SQL> ALTER SESSION SET NLS_SORT=spanish_m;
This message appears:
Session altered. -
At the SQL prompt, enter the query in Step 2.
-
The result of the query returns in the Spanish sort specified in Step 3.
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:
-
Enter the following command at the SQL prompt.
SQL> ALTER SESSION SET NLS_LANGUAGE=american;
This message appears:
Session altered. -
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 the illustration nls07.gif
-
Enter the following command at the SQL prompt.
SQL> ALTER SESSION SET NLS_LANGUAGE=french;
This message appears:
Session altered. -
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 the illustration nls08.gif
-
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.) |
|
|
|
|
Chinese (Taiwan) |
|
|
|
|
English (U.S.A.) |
|
|
|
|
English (U.K.) |
|
|
|
|
French (Canada) |
|
|
|
|
French (France) |
|
|
|
|
German |
|
|
|
|
Italian |
|
|
|
|
Japanese |
j |
|
|
|
Korean |
|
|
|
|
Portuguese (Brazil) |
|
|
|
|
Portuguese |
|
|
|
|
Spanish |
|
|
|
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:
-
Open the application
HR_Connect_CSorHR_Connect_VB. -
Make a copy of
Form3.xx, which you finished at the end of Chapter 4 and name itForm5.xx, following the instructions in Appendix B, "Copying a Form". -
Open Form1 of the project, and switch to design view.
-
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. The Properties window appears.
-
In the Properties window, set these properties:
-
Under Appearance, change Text to
ChangeDateFormat. -
Under Design, change (Name) to
date_change.Form1 should look much like this:
Description of the illustration nls09.gif
In the properties window, if you click Events (lightning bolt icon),
date_change_Click()now shows as the Event for the date button. -
-
Open the new
date_change_Click()method just created and add the following code to change the date format from the standardDD-MON-RRtoYYYY-MM-DDand to update theDataSet.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
siclass variable will be declared and session globalization information retrieved in Step 10 and Step 11. -
Within the appropriate method, add the code indicated.
Visual C#: In the
Form1()methoddate_change.Enabled = false;
Visual Basic: In the
Form1_Loadmethoddate_change.Enabled = false
Description of the illustration nls10.gif
-
Add the following class variable to the existing Form1 class declarations right after the
publicForm1()block with this code as indicated.Visual C#:
private OracleGlobalization si;
Visual Basic:
private si As OracleGlobalization
Description of the illustration nls11.gif!
-
Within the
connect_Click()methodtryblock, add the indicated code which does the following:-
Retrieve the value of the
OracleGlobalizationobject. -
Retrieve data from the
EMPLOYEEStable (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
-
-
Save
Form1. -
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 the illustration nls12.gif
-
Click Change Date Format.
Description of the illustration nls13.gif
Note that the date format changed from the original
DD-MON-RRtoYYYY-MM-DD. -
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 theSystem.Threading.Thread. CurrentThread.CurrentCulture property do not impact the OracleGlobalization settings of the thread or the session. The reverse is also true. |
