9
SQL and PL/SQL Programming in a Global Environment

This chapter contains information useful for SQL programming in a globalization support environment. It inclu des the following topics:

• Locale- Dependent SQL Functions with Optional NLS Parameters
• Other Locale-Dependent SQL Functions
• Miscellaneous Topics for SQL and PL/SQL Programming in a Global Environment

Locale-Dependent SQL Functions with Optional NLS Paramet ers

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

TO_CHAR

TO_DATE

T O_NUMBER

NLS_UPPER

NL S_LOWER

NLS_INITCAP

N LSSORT

Explicitly specifying the o ptional 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:

SELECT last_name FROM employees WHERE hire_date > '01-JAN-1999';

Such a query can be made independent of the current date language by using a statement similar to t he following:

SELECT last_name FROM employees
     WHERE hire_date &g
t; 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 nece ssary. Such statements are necessary when string literals appear in SQL statements in views, CHECK constraints, or trigg ers.

All character functions support bot h single-byte and multibyte characters. Except where explicitly stated, character functions operate character by character, rather th an byte by byte.

The rest of this section includes the following topics:

• Default Values for NLS Parameters in SQL Functions< /a>
• Specifying NLS Parameters in SQL Functions
• Unacceptable NLS Parameters in SQL Functions

D efault Values for NLS Parameters in SQL Functions

When SQL functions evaluate views and triggers, default values from the current session are used for the NLS function parameters. When SQL functions ev aluate CHECK constraints, they use the default values that were specified for the NLS parameters when the database was c reated.

S pecifying NLS Parameters in SQL Functions

NLS parameters are specifi ed in SQL functions as follows:

'parameter = value'

For example:

'NLS_DATE_LANGUAGE = AMERICAN'

The following NLS parameters can be specified in SQL functions:

NLS_DATE_LANGUAGE

NLS_NUMERIC_CHARACTERS

NLS_CURRENCY

NLS_ISO_CURRENCY

NLS_DUAL_CURRENCY

NLS_CALENDAR

NLS_SORT

Table 9-1 shows wh ich NLS parameters are valid for specific SQL functions.

Table 9-1 SQL Functions and Their Valid NLS Paramet ers  

The following examples show how to use NLS parameters in SQL functions:

TO_DATE ('1-JAN-99', 'DD-MON-YY'
,
   'nls_date_language = American')

TO_CHAR (hire_date, 'DD/MON/Y
YYY',
   'nls_date_language = French')

TO_CHAR (SYSDATE, 'DD/MON/Y
YYY',
    'nls_date_language=''Traditional Chinese'' ')

TO_NUMBER
('13.000,00', '99G999D99',
   'nls_numeric_characters = '',.''')

T
O_CHAR (salary, '9G999D99L', 'nls_numeric_characters = '',.''
   nls_currency = ''EUR''')

TO_CHAR (salary, '9G999D99C', 'nls_numeric_characters = ''.,''
   nls_iso_currency = Ja
pan')

NLS_UPPER (last_name, 'nls_sort = Swiss')

NLSSORT (last_name, 'nls_sort = German')

Unacceptable NLS Parameters in SQL Functions

The following NLS parameters are not accepted in SQL functions except for NLSSORT:

• NLS_LANGUAGE
• NLS_TERRITORY
• NLS_DATE_FORMAT

NLS_DATE_FORMAT and NLS_TERRITORY_FORMAT are not accepted as parameters because they can interfere with required format masks. A date f ormat must always be specified if an NLS parameter is in a TO_CHAR or TO_DATE function. As a result, NLS_DATE_FORMAT and NLS_TERRITORY_FORMAT are not valid NLS parameters for the TO_CHAR or TO_DA TE functions. IF you specify NLS_DATE_FORMAT or NLS_TERRITORY_FORMAT in the TO_CHAR or TO_DATE function, then an error is returned.

NLS_LANGUAGE can int erfere with the session value of NLS_DATE_LANGUAGE. If you specify NLS_LANGUAGE in the TO_CHAR function, for example, then its value is ignored if it differs from the session value of NLS_DATE_LANGUAGE.

Other Locale-Depende nt SQL Functions

This section includes the following topics:

• The CONVERT Function
• SQL Functions for Different Length Semantics
• LIKE Conditions for Different Length Semantics
• Character Set SQL Functions
• The NLSSORT Function

The CONVERT function enables conversion of character data between character sets.

The CONVERT function converts the binary representation of a character string in one ch aracter set to another. It uses exactly the same technique as conversion between database and client character sets. Hence, it uses r eplacement characters and has the same limitations.

The syntax for CONVERT is as follows:

CONVERT(char, dest_
char_set[, source_char_set])

char< /em> is the value to be converted. source_char_set is the source character set and dest_char_set is the destination character set. If the source_char_set parameter is not specified, then it defaults t o the database character set.

SELEC
T SUBSTR ('Fußball', 2 , 4) SUBSTR FROM dual;

SUBS
----
ußba

SELECT SUBSTRB ('Fußball', 2 , 4) SUBSTRB FROM dual;

SUB
<
/a>---
ußb

ALTER SESSION SET NLS_SORT = GERMAN;
SELECT * FR
OM table1
     ORDER BY column1;

ALTER SESSION SET NLS_SORT = GERMAN;
SELECT
* FROM table1
     ORDER BY NLSSORT(column1, 'NLS_SORT=FRENCH');

SELECT * FROM table1
ORDER BY NLSSORT(col
umn1, 'NLS_LANG=PL');

SELECT column1 FROM table1 WHERE column1 > 'B';

WHERE NLSSORT(col) comparison_operator NLSSORT(comparison_string
)

SELECT column1 FROM table1 WHERE NLSSORT(column1) > NLSSO
RT('B');

SELECT last_name FROM employees 
     ORDER BY last_name;
<
a name="1006409">

SELECT last_name FROM employees
     ORDER BY NLSSORT(last_name, 'NLS_SORT = GERMA
N');