Locale-Dependent SQL Functions with Optional NLS Parameters

All SQL functions whose behavior depends on globalization support conventions allow NLS parameters to be specified. These functions are:

TO_CHAR
TO_DATE
TO_NUMBER
NLS_UPPER
NLS_LOWER
NLS_INITCAP
NLSSORT

Explicitly specifying the optional NLS parameters for these functions enables the functions to be evaluated independently of the session's NLS parameters. This feature can be important for SQL statements that contain numbers and dates as string literals.

For example, the following query is evaluated correctly if the language specified for dates is AMERICAN and the calender is specified as GREGORIAN.

Setting NLS_DATE_LANGUAGE=American, NLS_CALENDAR=Gregorian

ALTER SESSION SET NLS_DATE_LANGUAGE=American;
ALTER SESSION SET NLS_CALENDAR=Gregorian;
SELECT last_name FROM employees WHERE hire_date > '01-JAN-1999';

The previous query can be made independent of the current date language by using a statement similar to the following:

Setting NLS_LANGUAGE in a Query

SELECT last_name FROM employees
     WHERE hire_date > TO_DATE('01-JAN-1999','DD-MON-YYYY',
     'NLS_DATE_LANGUAGE = AMERICAN');

In this way, SQL statements that are independent of the session language can be defined where necessary. These statements are necessary when string literals appear in SQL statements in views, CHECK constraints, or triggers.

Note:

Only SQL statements that must be independent of the session NLS parameter values should explicitly specify optional NLS parameters in locale-dependent SQL functions. Using session default values for NLS parameters in SQL functions usually results in better performance.

All character functions support both single-byte and multi-byte characters. Except where explicitly stated, character functions operate character by character, rather than byte by byte.

The remainder of this section contains the following topics: