<< Specifying NLS Parameters in SQL Functions | Unacceptable NLS Parameters >>

Using Locale-Dependent Functions

Using Locale-Dependent Functions with NLS Parameters

6-32 Oracle Database 2 Day Developer's Guide

Example 61

shows several

SELECT

statements that demonstrate how to use NLS

parameters in SQL functions. After you perform these

SELECT

statements (which you

can do as a group in SQL Workshop), examine the output of each statement in the
Script Output pane. (The output for most of the statements is very long.)

Example 61 Using NLS Parameters in SQL Functions

SELECT TO_DATE('1-JAN-99', 'DD-MON-YY',

'NLS2008LANGUAGE = American') "01/01/99" FROM DUAL;

SELECT TO_CHAR(hire_date, 'DD/MON/YYYY',

'NLS2008LANGUAGE = French') "Hire Date" FROM employees;

SELECT TO_CHAR(SYSDATE, 'DD/MON/YYYY',

'NLS2008LANGUAGE = ''Traditional Chinese'' ') "System Date" FROM DUAL;

SELECT TO_CHAR(13000, '99G999D99',

'NLS_NUMERIC_CHARACTERS = '',.''') "13K" FROM DUAL;

SELECT TO_CHAR(salary, '99G999D99L', 'NLS_NUMERIC_CHARACTERS = '',.''

NLS_CURRENCY = ''EUR''') salary FROM employees;

SELECT TO_CHAR(salary, '99G999D99C', 'NLS_NUMERIC_CHARACTERS = ''.,''

NLS_ISO_CURRENCY = Japan') salary FROM employees;

SELECT NLS_UPPER(last_name, 'NLS_SORT = Swiss') "Last Name" FROM employees;

SELECT last_name FROM employees

ORDER BY NLSSORT(last_name, 'NLS_SORT = German');

SQL Function

Valid NLS Parameters

TO_DATE

NLS2008LANGUAGE, NLS_CALENDAR

TO_NUMBER

NLS_NUMERIC_CHARACTERS, NLS_CURRENCY, NLS_ISO_CURRENCY,

NLS_DUAL_CURRENCY,

TO_CHAR

NLS2008LANGUAGE, NLS_NUMERIC_CHARACTERS, NLS_CURRENCY,

NLS_ISO_CURRENCY, NLS_DUAL_CURRENCY, NLS_CALENDAR

TO_NCHAR

NLS2008LANGUAGE, NLS_NUMERIC_CHARACTERS, NLS_CURRENCY,

NLS_ISO_CURRENCY, NLS_DUAL_CURRENCY, NLS_CALENDAR

NLS_UPPER

NLS_SORT

NLS_LOWER

NLS_SORT

NLS_INITCAP

NLS_SORT

NLSSORT

NLS_SORT

on page 6-33

Oracle Database Globalization Support Guide for a complete
discussion of locale-dependent SQL functions with optional NLS
parameters