Specifying NLS Parameters in SQL Functions
Using Locale-Dependent Functions with NLS Parameters
Working in a Global Environment 6-31
For example, there are two ways to ensure that the following query is evaluated so
that the language specified for dates is
AMERICAN
.
Use
ALTER SESSION
to set the
NLS2008LANGUAGE
and
NLS_CALENDAR
parameters.
ALTER SESSION SET NLS2008LANGUAGE=American;
SELECT last_name FROM employees WHERE hire_date > '01-JAN-1999';
Specify the
NLS2008LANGUAGE
parameter in the
TO_DATE
function in the
WHERE
clause of the SQL statement.
SELECT last_name FROM employees
WHERE hire_date > TO_DATE('01-JAN-1999','DD-MON-YYYY',
'NLS2008LANGUAGE = AMERICAN');
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.
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 multibyte characters. Except
where explicitly stated, character functions operate character by character, rather than
byte by byte.
When SQL functions evaluate views and triggers, default values from the current
session are used for the NLS function parameters. When SQL functions evaluate
CHECK
constraints, they use the default values that were specified for the NLS
parameters when the database was created.
Specifying NLS Parameters in SQL Functions
NLS parameters are specified in SQL functions as 'parameter
=
value'. For example:
'NLS2008LANGUAGE = AMERICAN'
You can specify the following NLS parameters in SQL functions:
NLS2008LANGUAGE
NLS_NUMERIC_CHARACTERS
NLS_CURRENCY
NLS_ISO_CURRENCY
NLS_DUAL_CURRENCY
NLS_CALENDAR
NLS_SORT
In some languages, some lowercase characters correspond to more than one uppercase
character, or some uppercase characters correspond to more than one lowercase
characters. As a result, the length of the output from the
NLS_UPPER
,
NLS_LOWER
,
and
NLS_INITCAP
functions can differ from the length of the input. The following
table shows which NLS parameters are valid for specific SQL functions.
See Also:
Oracle Database Globalization Support Guide for a complete
discussion of locale-dependent SQL functions with optional NLS
parameters
