Language-Dependent Application Behavior

SQL functions support both single-byte and multibyte characters. Some SQL functions require that appropriate NLS parameters be specified explicitly as a part of their parameter list for the functions to perform properly. In this way, not only can the environment variables override the default behavior at the database level, but you can also allow the SQL functions to override the environment variables' default behavior without having to alter the session. This behavior can be seen in the following SQL statement:

[View full width]
 Select To_char(birthday_date, 'DD.Mon.YYYY', 'NLS_DATE_LANGUATE=FRENCH') as "Birth Date"  from employee_info; 

Table 7.2 shows the SQL functions that use NLS parameters and the parameters that they are willing to accept.

Table 7.2. SQL Functions and NLS Parameters

SQL Function	NLS Parameter
TO_DATE	NLS_DATE_LANGUGE
	NLS_CALENDAR
TO_NUMBER	NLS_NUMERIC_CHARACTER
	NLS_CURRENCY
	NLS_ISO_CURRENCY
TO_CHAR	NLS_DATE_LANGUAGE
	NLS_NUMERIC_CHARACTER
	NLS_CURRENCY
	NLS_ISO_CURRENCY
	NLS_CALENDAR
NLS_UPPER	NLS_SORT
NLS_LOWER	NLS_SORT
NLS_INITCAP	NLS_SORT
NLSSORT	NLS_SORT

Several format mask elements have been defined for the SQL functions (such as TO_CHAR, TO_DATE, and TO_NUMBER) to facilitate the use of National Language Support. These format elements follow:

• Number format mask elements:

  • D is used for decimal separator.

  • G is used for the group (hundreds, thousands, millions…) separator.

  • L is used for local currency symbol.

  • C is used for local ISO currency symbol.

  • U is used for the dual currency symbolused for the euro.

• Date format mask elements:

  • RM, rm are allowed for Roman month number.

  • IW is for ISO week number.

  • IYYY, IYY, IY, and I are for ISO year.

Not only is there support for language-dependent applications there also is support for language-dependent indexing.

Linguistic Index Support

Oracle provides further support for national languages by allowing linguistic indexing. You can realize high performance gains with local sorting based on linguistic indexes. Functional indexes (described further in Chapter 13, "Managing Indexes," and in Chapter 12, "Managing Tables") can be highly specialized to create linguistically sorted indexes. The SQL function NLSSORT returns the string of bytes used to sort the first parameter in the given sorting sequence.

The NLS_COMP parameter is included by Oracle for linguistic comparisons. NLS_COMP is another dynamic initialization parameter that controls how the comparison operators (<, >, between, and =) handle linguistic ordering. If the sort method is BINARY, the default, comparison is based entirely on the binary value of the different strings. When the sort method is ANSI, the comparison operators use the linguistic sorting sequences to determine the ultimate outcome of the comparison operations according to the NLS_SORT parameter that takes precedence at the time at the session level.

There are also linguistic extensions that allow for national language-based loading and importing of data into the database.

Importing and Loading Data Using NLS

If you export data from any given database, that export file will contain data with the source database's character set. When importing data using IMPORT and NLS, the data will be converted from the export file's character set to the database character set of the target database during the import operation.

SQL Loader has also been built with the capability to convert the data from one database character set to the other.

SQL*Loader, using conventional load, will convert the data into the session character set specified by the NLS_LANG environment variable. Using direct load with SQL*Loader, the data is converted directly into the character set of the database. In SQL*Loader conventional loads, input records are parsed according to their field specifications just as they would be in a standard INSERT statement, and each data field is copied to its corresponding bind array. When either the bind array is full or when there is no more data left to read, the array is inserted into the table. On the other hand, direct loads parse the input records according to the field specifications, convert the input field data to the column data types, and build a column array in the same format as the table into which you are putting the data. The column array is passed to a block formatter, which creates data blocks in the specific database block format of the database in question. These blocks are written directly to the database, bypassing most RDBMS processing, typically making them much faster than conventional path load.

The SQL*Loader control file provides SQL*Loader with the information necessary to determine what character set is to be used with each data file.

The following is a SQL*Loader command-line command containing the character set that needs to be used for the given load. In the case of this command, the control file will be loading, apparently, the Spanish employee table and will use the WE8ISO9959P1 character set for the load.

 $sqlldr control=Spanish_employee.ctl characterset=WE8ISO9959P1 

You will likely need, at some point, to determine information on the National Language Support features in the database. As with everything else connected to the database and the instance, you can find information in the data dictionary.