Release 1 (9.0.1)
Part Number A90236-02
 Home |
 Book List |
 Contents |
 Index |  Master Index |  Feedback |
| Oracle9i Globalization Support Guide Release 1 (9.0.1) Part Number A90236-02 |
|
This chapter contains information useful for SQL programming in a globalization support environment. It includes the following topics:
All SQL functions whose behavior depends on NLS conventions allow NLS parameters to be specified. These functions are:
Explicitly specifying the optional NLS parameters for these functions allows the function evaluations to be independent of the NLS parameters in force for the session. This feature may 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 ENAME FROM EMP WHERE HIREDATE > '1-JAN-91';
Such a query can be made independent of the current date language by using these statements:
SELECT ENAME FROM EMP WHERE HIREDATE > TO_DATE('1-JAN-91','DD-MON-YY', 'NLS_DATE_LANGUAGE = AMERICAN');
In this way, language-independent SQL statements can be defined where necessary. For example, such statements might be necessary when string literals appear in SQL statements in views, CHECK constraints, or triggers.
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 evaluating views and triggers, default values for NLS function parameters are taken from the values currently in force for the session. When evaluating CHECK constraints, default values are set by the NLS parameters that were specified at database creation.
The syntax that specifies NLS parameters in SQL functions is:
'parameter = value'
The following NLS parameters can be specified:
Only certain NLS parameters are valid for particular SQL functions, as shown in Table 7-1:
Examples of the use of NLS parameters are:
TO_DATE ('1-JAN-89', 'DD-MON-YY', 'nls_date_language = American') TO_CHAR (hiredate, 'DD/MON/YYYY', 'nls_date_language = French') TO_NUMBER ('13.000,00', '99G999D99', 'nls_numeric_characters = '',.''') TO_CHAR (sal, '9G999D99L', 'nls_numeric_characters = '',.'' nls_currency = '' Dfl''') TO_CHAR (sal, '9G999D99C', 'nls_numeric_characters = ''.,'' nls_iso_currency = Japan') NLS_UPPER (ename, 'nls_sort = Swiss') NLSSORT (ename, 'nls_sort = German')
Note that NLS_LANGUAGE and NLS_TERRITORY are not accepted as parameters in SQL functions, except for NLSSORT. Only NLS parameters that explicitly define the specific data items required for unambiguous interpretation of a format are accepted. NLS_DATE_FORMAT is also not accepted as a parameter for the reason described below.
If an NLS parameter is specified in TO_CHAR, TO_NUMBER, or TO_DATE, then a format mask must also be specified as the second parameter. For example, the following specification is legal:
TO_CHAR (hiredate, 'DD/MON/YYYY', 'nls_date_language = French')
The following specifications are illegal:
TO_CHAR (hiredate, 'nls_date_language = French') TO_CHAR (hiredate, 'nls_date_language = French', 'DD/MON/YY')
This restriction requires that a date format always be specified if an NLS parameter is in a TO_CHAR or TO_DATE function. As a result, NLS_DATE_FORMAT is not a valid NLS parameter for these functions.
The CONVERT SQL function allows conversion of character data between character sets.
The CONVERT function converts the binary representation of a character string in one character set to another. It uses exactly the same technique described previously for the conversion between database and client character sets. Hence, it uses replacement characters and has the same limitations.
The syntax for CONVERT is:
where 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 to the database character set.
In client/server environments using different character sets, use the TRANSLATE (...USING...) statement to perform conversions instead of CONVERT. The conversion to client character sets will then properly know the server character set of the result of the TRANSLATE statement.
|
See Also:
|
When using string manipulation functions, you can get different results depending upon the database character set. In particular, the INSTR, LENGTH, and SUBSTR functions can return incorrect results because of the difference between single and multibyte character sets. To guarantee correct results, you should use variants of these functions designed for multibyte character sets. These functions are variations of:
The instring functions search strings for the presence of a substring. In addition to the traditional INSTR, you can use INSTRB, INSTR2, INSTR4, and INSTRC. The functions allow you to return part of a string based on whether characters are calculated in bytes, UCS2 codepoints (where a surrogate pair is considered as two codepoints), or UCS4 codepoints (treats a surrogate pair as one codepoint) or complete Unicode characters (same as INSTR4 with additional support for treating composed Unicode characters as one code point).
The following examples highlights the differences between INSTR and INSTRB on a database where the database character set is UTF8.
This searches the string "Städte und Länder", beginning with the fifth character, for the character "d". It returns the character position in "Städte und Länder" at which the second occurrence of "d" begins.
SELECT INSTR('Städte und Länder','d', 5, 2) INSTR FROM DUAL; INSTR ---------- 15
The following example searches the string "Städte und Länder", beginning with the fifth byte, for the character "d". It returns the byte position in "Städte und Länder" at which the second occurrence of "d" begins.
SELECT INSTRB('Städte und Länder','d', 5, 2) INSTRB FROM DUAL; INSTRB ---------- 11
The length functions return the length of a string. In addition to the traditional LENGTH, you can use LENGTHB, LENGTH2, LENGTH4, and LENGTHC. The functions allow you to return part of a string based on whether characters are calculated in bytes, UCS2 codepoints (where a surrogate pair is considered as two codepoints), or UCS4 codepoints (treats a surrogate pair as one codepoint) or complete Unicode characters (same as LENGTH4 with additional support for treating composed Unicode characters as one code point).
The following examples highlight the differences between LENGTH and LENGTHB on a database where the database character set is UTF8.
SELECT LENGTH ('Télévision') LENGTH FROM DUAL; LENGTH ---------- 10 SELECT LENGTHB ('Télévision') LENGTHB FROM DUAL; LENGTHB ---------- 12
The substring functions return the requested portion of a substring. In addition to the traditional SUBSTR, you can use SUBSTRB, SUBSTR2, SUBSTR4 and SUBSTRC.The functions allow you to return part of a string based on whether characters are calculated in bytes, UCS2 codepoints (where a surrogate pair is considered as two codepoints), or UCS4 codepoints (treats a surrogate pair as one codepoint) or complete Unicode characters (same as SUBSTR4 with additional support for treating composed Unicode characters as one code point).
The following examples highlight the differences between SUBSTR and SUBSTRB on a database where the database character set is AL32UTF8.
SELECT SUBSTR ('Fußball', 2 , 4) SUBSTR FROM DUAL; SUBS ---- ußba SELECT SUBSTRB ('Fußball', 2 , 4) SUBSTRB FROM DUAL; SUB --- ußb
The LIKE conditions specify a test involving pattern matching. Whereas the equality operator (=) exactly matches one character value to another, the LIKE conditions match a portion of one character value to another by searching the first value for the pattern specified by the second. LIKE calculates strings using characters as defined by the input character set. LIKEC uses unicode complete characters. LIKE2 uses UCS2 codepoints. LIKE4 uses USC4 codepoints.
There is no LIKEB condition.
Two SQL functions, NLS_CHARSET_NAME and NLS_CHARSET_ID, are provided to convert between character set ID numbers and character set names. They are used by programs that need to determine character set ID numbers for binding variables through OCI.
The NLS_CHARSET_NAME(n) function returns the name of the character set corresponding to ID number n. The function returns NULL if n is not a recognized character set ID value.
NLS_CHARSET_ID(TEXT) returns the character set ID corresponding to the name specified by TEXT. TEXT is defined as a run-time VARCHAR2 quantity, a character set name. Values for TEXT can be NLSRTL names that resolve to sets other than the database character set or the national character set.
If the value CHAR_CS is entered for TEXT, then the function returns the ID of the server's database character set. If the value NCHAR_CS is entered for TEXT, then the function returns the ID of the server's national character set. The function returns NULL if TEXT is not a recognized name. The value for TEXT must be entered in all uppercase.
NLS_CHARSET_DECL_LEN(BYTECNT, CSID) returns the declaration length (in number of characters) for an NCHAR column. The BYTECNT argument is the byte length of the column. The CSID argument is the character set ID of the column.
The NLSSORT function replaces a character string with the equivalent sort string used by the linguistic sort mechanism. For a binary sort, the sort string is the same as the input string. The linguistic sort technique operates by replacing each character string with some other binary values, chosen so that sorting the resulting string produces the desired sorting sequence. When a linguistic sort is being used, NLSSORT returns the binary values that replace the original string.
The ORDER BY clause in a SQL statement is determined by the NLS_SORT session parameter, but it can be overridden by explicitly using the NLSSORT function, as the following example shows.
ALTER SESSION SET NLS_SORT = GERMAN; SELECT * FROM table1 ORDER BY col1;
The preceding example uses a German sort, but the following example uses a French one.
ALTER SESSION SET NLS_SORT = GERMAN; SELECT * FROM table1 ORDER BY NLSSORT(col1, 'NLS_SORT = FRENCH');
The WHERE clause normally uses binary comparison rather than linguistic comparison. But this can be overridden by the following methods.
NLSSORT function in the WHERE clause.
SELECT * FROM table1 WHERE NLSSORT(col1, 'NLS_SORT = FRENCH')>
NLSSORT(col2, 'NLS_SORT = FRENCH');
NLS_COMP to ANSI, in which case the NLS_SORT session parameter is used in the WHERE clause.
ALTER SESSION SET NLS_COMP = ANSI;
There are four ways to use NLSSORT:
NLSSORT(), which relies on the NLS_SORT parameter
NLSSORT(column1, 'NLS_SORT=xxxx')
NLSSORT(column1, 'NLS_LANG= xxxx')
NLSSORT(column1, 'NLS_LANGUAGE=xxxx')
The NLS_LANG parameter of the NLS_SORT function is not the same as the NLS_LANG client environment setting. In the NLSSORT function, NLS_LANG specifies the abbreviated language name, for example, US for American or PL for Polish. An example is:
SELECT * FROM emps ORDER BY NLSSORT(col1, 'NLS_LANG=PL');
NLSSORT allows applications to perform string matching that follows alphabetic conventions. Normally, character strings in a WHERE clause are compared using the characters' binary values. A character is "greater than" another if it has a higher binary value in the database character set. Because the sequence of characters based on their binary values might not match the alphabetic sequence for a language, such comparisons often do not follow alphabetic conventions. For example, if a column (col1) contains the values ABC, ABZ, BCD, and ÄBC in the ISO 88591 8-bit character set, the following query:
SELECT col1 FROM tab1 WHERE col1 > 'B';
returns both BCD and ÄBC because Ä has a higher numeric value than B. However, in German, Ä is sorted alphabetically before B. Such conventions are language-dependent even when the same character is used. In Swedish, Ä is sorted after Z. Linguistic comparisons can be made using NLSSORT in the WHERE clause, as follows:
WHERE NLSSORT(col) comparison_operator NLSSORT(comparison_string)
Note that NLSSORT has to be on both sides of the comparison operator. For example:
SELECT col1 FROM tab1 WHERE NLSSORT(col1) > NLSSORT('B')
If a German linguistic sort is being used, this does not return strings beginning with Ä because, in the German alphabet, Ä comes before B. If a Swedish linguistic sort is being used, such names are returned because, in the Swedish alphabet, Ä comes after Z.
Normally, comparison in the WHERE clause or PL/SQL blocks is binary. You can use the NLSSORT function for linguistic comparison. Sometimes this can be tedious, especially when the linguistic sort needed has already been specified in the NLS_SORT session parameter. You can use NLS_COMP in such cases to indicate that the comparisons must be linguistic according to the NLS_SORT session parameter. This is done by altering the session:
ALTER SESSION SET NLS_COMP = ANSI;
To specify that comparison in the WHERE clause is always binary, issue the following statement:
ALTER SESSION SET NLS_COMP = BINARY;
When NLS_COMP is set to ANSI, a linguistic index improves the performance of the linguistic comparison.To enable a linguistic index, use the syntax:
CREATE INDEX i ON t(NLSSORT(col, 'NLS_SORT=FRENCH'));
String comparison for partition VALUES LESS THAN collation for DDL and DML always follows BINARY order.
If a linguistic sorting sequence is in use, then NLSSORT is used implicitly on each character item in the ORDER BY clause. As a result, the sort mechanism (linguistic or binary) for an ORDER BY is transparent to the application. However, if the NLSSORT function is explicitly specified for a character item in an ORDER BY item, then the implicit NLSSORT is not done.
In other words, the NLSSORT linguistic replacement is only applied once, not twice. The NLSSORT function is generally not needed in an ORDER BY clause when the default sort mechanism is a linguistic sort. However, when the default sort mechanism is BINARY, then a query such as:
SELECT ename FROM emp ORDER BY ename
uses a binary sort. A German linguistic sort can be obtained using:
SELECT ename FROM emp ORDER BY NLSSORT(ename, 'NLS_SORT = GERMAN')
Several format masks are provided with the TO_CHAR, TO_DATE, and TO_NUMBER functions to format dates and numbers according to the relevant conventions.
A format element RM (Roman Month) returns a month as a Roman numeral. One can specify either uppercase or lowercase using RM or rm respectively. For example, for the date 7 Sep 1998, DD-rm-YYYY will return 07-ix-1998 and DD-RM-YYYY will return 07-IX-1998.
Note that the MON and DY format masks explicitly support month and day abbreviations that may not be three characters in length. For example, the abbreviations "Lu" and "Ma" can be specified for the French "Lundi" and "Mardi", respectively.
The week numbers returned by the WW format mask are calculated according to the algorithm int((day-ijan1)/7). This week number algorithm does not follow the ISO standard (2015, 1992-06-15).
To support the ISO standard, a format element IW is provided that returns the ISO week number. In addition, format elements I, IY, IYY, and IYYY, equivalent in behavior to the format elements Y, YY, YYY, and YYYY, return the year relating to the ISO week number.
In the ISO standard, the year relating to an ISO week number can be different from the calendar year. For example, 1st Jan 1988 is in ISO week number 53 of 1987. A week always starts on a Monday and ends on a Sunday.
For example, January 1, 1991, is a Tuesday, so Monday, December 31, 1990, to Sunday, January 6, 1991, is week 1. Thus, the ISO week number and year for December 31, 1990, is 1, 1991. To get the ISO week number, use the format mask IW for the week number and one of the IY formats for the year.
Several additional format elements are provided for formatting numbers:
D (decimal) returns the decimal point character.
G (group) returns the group separator.
L (local currency) returns the local currency symbol.
C (international currency) returns the ISO currency symbol.
RN (Roman numeral) returns the number as its Roman numeral equivalent.
For Roman numerals, one can specify either uppercase or lowercase, using RN or rn, respectively. The number being converted must be an integer in the range 1 to 3999.
The following topic is also important for SQL programming:
If the database character set replaces the vertical bar | with a national character, then all SQL statements that use the concatenation operator (ASCII 124) will fail. For example, creating a procedure will fail because it generates a recursive SQL statement that uses concatenation. When you use a 7-bit replacement character set such as D7DEC, F7DEC, or SF7ASCII for the database character set, then the national character which replaces the vertical bar is not allowed in object names because the vertical bar is interpreted as the concatenation operator.
On the user side, one can use a 7-bit replacement character set if the database character set is the same or compatible, that is, if both character sets replace the vertical bar with the same national character.
|
 Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|