9 Including Globalization Support

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.

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 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 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 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 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 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 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 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 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 the illustration nls08.gif
   
   

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

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:

   • Under Appearance, change Text to Change Date Format.

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

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 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 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 the illustration nls12.gif
    
    

14. Click Change Date Format.

    
    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.