Oracle Database compares character strings in two steps for monolingual collation. The first step compares the major value of the entire string from a table of major values. Usually, letters with the same appearance have the same major value. The second step compares the minor value from a table of minor values. The major and minor values are defined by Oracle Database. Oracle Database defines letters with diacritic and case differences as having the same major value but different minor values.

Each major table entry contains the Unicode code point and major value for a character. The Unicode code point is a 16-bit binary value that represents a character.

The following table illustrates sample values for sorting a, A, ä, Ä, and b.

Oracle Database provides multilingual collation so that you can sort data in more than one language in one sort. This is useful for regions or languages that have complex sorting rules and for multilingual databases. Note that Oracle Database supports all of the collations defined in the previous releases.

For Asian language data or multilingual data, Oracle Database provides a sorting mechanism based on the ISO 14651 standard. For example, Chinese characters can be ordered by the number of strokes, PinYin, or radicals.

In addition, multilingual collation can handle canonical equivalence and supplementary characters. Canonical equivalence is a basic equivalence between characters or sequences of characters. For example, ç is equivalent to the combination of c and ,. Supplementary characters are user-defined characters or predefined characters in Unicode that require two code points within a specific code range. You can define up to 1.1 million code points in one multilingual sort.

For example, Oracle Database supports a monolingual French sort (FRENCH), but you can specify a multilingual French collation (FRENCH_M). _M represents the ISO 14651 standard for multilingual sorting. The sorting order is based on the GENERIC_M sorting order and can sort diacritical marks from right to left. Multilingual linguistic sort is usually used if the tables contain multilingual data. If the tables contain only French, then a monolingual French sort might have better performance because it uses less memory. It uses less memory because fewer characters are defined in a monolingual French sort than in a multilingual French sort. There is a trade-off between the scope and the performance of a sort.

Unicode Collation Algorithm (UCA) is a Unicode standard that is fully compatible with the international collation standard ISO 14651. UCA defines a Default Unicode Collation Element Table (DUCET) that provides a reasonable default ordering for all languages that are not tailored. To achieve the correct ordering for a particular language, DUCET can be tailored to meet the linguistic requirements for that language. There are tailorings of DUCET for various languages provided in the Unicode Common Locale Data Repository.

This Oracle Database release provides UCA collation that fully conforms to UCA 12.1. In addition to the collation based on DUCET, it provides tailored collations for a number of commonly used languages. For example, you can specify UCA collation UCA1210_SCHINESE to sort multilingual data containing Simplified Chinese. The collation will make Simplified Chinese data appear in the PinYin order.

For sorting multilingual data, Oracle recommends the latest supported version of UCA collations.

This section describes the following topics:

• UCA Comparison Levels

• UCA Collation Parameters

Base letters are defined in a base letter table, which maps each letter to its base letter. For example, a, A, ä, and Ä all map to a, which is the base letter. This concept is particularly relevant for working with Oracle Text.

In multilingual collation and UCA collation, certain characters may be treated as ignorable. Ignorable characters are skipped, that is, treated as non-existent, when two character values (strings) containing such characters are compared in a sorting or matching operation. There are three kinds of ignorable characters: primary, secondary, and tertiary.

• Primary Ignorable Characters

• Secondary Ignorable Characters

• Tertiary Ignorable Characters

There are characters defined with variable collation elements in the UCA. These characters are called variable characters and are comprised of white space characters, punctuations, and certain symbols.

Variable characters can be weighted differently in UCA collations to adjust the effect of these characters in a sorting or comparison, which is called variable weighting. The collation parameter, alternate, controls how it works. The following options on variable weighting are supported in UCA collations in this release:

• blanked

  Variable characters are treated as ignorable characters. For example, SPACE (U+0020) is ignored in comparison.

• non-ignorable

  Variable characters are treated as if they were not ignorable characters. For example, SPACE (U+0020) is not ignored in comparison at primary level.

• shifted

  Variable characters are treated as ignorable characters on the primary, secondary and tertiary levels. In addition, a new quaternary level is used for all characters. The quaternary weight of a character depends on if the character is a variable, ignorable, or other. For example, SPACE (U+0020) is assigned a quaternary weight differently from A (U+0041) because SPACE is a variable character while A is neither a variable nor an ignorable character.

blackbird
Blackbird
Black-bird
Black bird
BlackBird

blackbird
Blackbird
BlackBird

Black bird
Black-bird
blackbird
Blackbird
BlackBird

blackbird
Black bird
Black-bird
Blackbird
BlackBird

Collation elements usually consist of a single character, but in some locales, two or more characters in a character string must be considered as a single collation element during sorting. For example, in traditional Spanish, the string ch is composed of two characters. These characters are called contracting characters in multilingual collation and special combination letters in monolingual collation.

Do not confuse a composed character with a contracting character. A composed character like á can be decomposed into a and ', each with their own encoding. The difference between a composed character and a contracting character is that a composed character can be displayed as a single character on a terminal, while a contracting character is used only for sorting, and its component characters must be rendered separately.

In some locales, certain characters must be sorted as if they were character strings. An example is the German character ß (sharp s). It is sorted exactly the same as the string ss. Another example is that ö sorts as if it were oe, after od and before of. These characters are known as expanding characters in multilingual collation and special letters in monolingual collation. Just as with contracting characters, the replacement string for an expanding character is meaningful only for sorting.

In Japanese, a prolonged sound mark that resembles an em dash — represents a length mark that lengthens the vowel of the preceding character. The sort order depends on the vowel that precedes the length mark. This is called context-sensitive collation. For example, after the character ka, the — length mark indicates a long a and is treated the same as a, while after the character ki, the — length mark indicates a long i and is treated the same as i. Transliterating this to Latin characters, a sort might look like this:

kaa   
ka—   -- kaa and ka— are the same
kai   -- kai follows ka- because i is after a
kia   -- kia follows kai because i is after a
kii   -- kii follows kia because i is after a
ki—   -- kii and ki— are the same

Canonical equivalence is an attribute of a multilingual collation and describes how equivalent code point sequences are sorted. If canonical equivalence is applied in a particular multilingual collation, then canonically equivalent strings are treated as equal.

One Unicode code point can be equivalent to a sequence of base letter code points plus diacritic code points. This is called the Unicode canonical equivalence. For example, ä equals its base letter a and an umlaut. A linguistic flag, CANONICAL_EQUIVALENCE = TRUE, indicates that all canonical equivalence rules defined in Unicode need to be applied in a specific multilingual collation. Oracle Database-defined multilingual collations include the appropriate setting for the canonical equivalence flag. You can set the flag to FALSE to speed up the comparison and ordering functions if all the data is in its composed form.

For example, consider the following strings:

• äa (a umlaut followed by a)

• a¨b (a followed by umlaut followed by b)

• äc (a umlaut followed by c)

If CANONICAL_EQUIVALENCE=FALSE, then the sort order of the strings is:

a¨b
äa
äc

This occurs because a comes before ä if canonical equivalence is not applied.

If CANONICAL_EQUIVALENCE=TRUE, then the sort order of the strings is:

äa
a¨b
äc

This occurs because ä and a¨ are treated as canonically equivalent.

You can use Oracle Locale Builder to view the setting of the canonical equivalence flag in existing multilingual collations. When you create a customized multilingual collation with Oracle Locale Builder, you can set the canonical equivalence flag as desired.

In French, sorting strings of characters with diacritics first compares base letters from left to right, but compares characters with diacritics from right to left. For example, by default, a character with a diacritic is placed after its unmarked variant. Thus Èdit comes before Edít in a French sort. They are equal on the primary level, and the secondary order is determined by examining characters with diacritics from right to left. Individual locales can request that the characters with diacritics be sorted with the right-to-left rule. Set the REVERSE_SECONDARY linguistic flag to TRUE to enable reverse secondary sorting.

In Thai and Lao, some characters must first change places with the following character before sorting. Normally, these types of characters are symbols representing vowel sounds, and the next character is a consonant. Consonants and vowels must change places before sorting. Set the SWAP_WITH_NEXT linguistic flag for all characters that must change places before sorting.

Special letters is a term used in monolingual collation. They are called expanding characters in multilingual collation.

Special combination letters is the term used in monolingual collations. They are called contracting letters in multilingual collation.

One lowercase letter may map to multiple uppercase letters. For example, in traditional German, the uppercase letters for ß are SS.

These case conversions are handled by the NLS_UPPER, NLS_LOWER, and NLS_INITCAP SQL functions, according to the conventions established by the linguistic collations. The UPPER, LOWER, and INITCAP SQL functions cannot handle these special characters, because their casing operation is based on binary mapping defined for the underlying character set, which is not linguistic sensitive.

The NLS_UPPER SQL function returns its first argument string in which all lowercase letters have been mapped to their uppercase equivalents. The following example shows the result of the NLS_UPPER function when NLS_SORT is set to XGERMAN:

SELECT NLS_UPPER ('große') "Uppercase" FROM DUAL;

Upper
-----
GROSSE

Oracle Database supports special lowercase letters. One uppercase letter may map to multiple lowercase letters. An example is the Turkish uppercase I becoming a small, dotless i.

The following examples show:

• A collation that uses information about base letters, diacritics, punctuation, and case

• A case-insensitive collation

• An accent-insensitive collation

blackbird
black bird
black-bird
Blackbird
Black-bird
blackbîrd
bläckbird

black bird
black-bird
Black-bird
blackbird
Blackbird
blackbîrd
bläckbird

blackbird
bläckbird
blackbîrd
Blackbird
BlackBird
Black-bird
Black bird

Use the NLS_SORT session parameter to specify a case-insensitive or accent-insensitive collation:

• Append _CI to an Oracle Database collation name for a case-insensitive collation.

• Append _AI to an Oracle Database collation name for an accent-insensitive and case-insensitive collation.

For example, you can set NLS_SORT to the following types of values:

UCA1210_SPANISH_AI
FRENCH_M_AI
XGERMAN_CI

Binary collation can also be case-insensitive or accent-insensitive. When you specify BINARY_CI as a value for NLS_SORT, it designates a collation that is accent-sensitive and case-insensitive. BINARY_AI designates an accent-insensitive and case-insensitive binary collation. You may want to use a binary collation if the binary collation order of the character set is appropriate for the character set you are using.

For example, with the NLS_LANG environment variable set to AMERICAN_AMERICA.WE8ISO8859P1, create a table called test2 and populate it as follows:

SQL> CREATE TABLE test2 (letter VARCHAR2(10));
SQL> INSERT INTO test2 VALUES('ä');
SQL> INSERT INTO test2 VALUES('a');
SQL> INSERT INTO test2 VALUES('A');
SQL> INSERT INTO test2 VALUES('Z');
SQL> SELECT * FROM test2;

LETTER
-----------
ä 
a
A
Z

The default value of NLS_SORT is BINARY. Use the following statement to do a binary collation of the characters in table test2:

SELECT * FROM test2 ORDER BY letter;

To change the value of NLS_SORT, enter a statement similar to the following:

ALTER SESSION SET NLS_SORT=BINARY_CI;

The following table shows the collation orders that result from setting NLS_SORT to BINARY, BINARY_CI, and BINARY_AI.

When NLS_SORT=BINARY, uppercase letters come before lowercase letters. Letters with diacritics appear last.

When the collation considers diacritics but ignores case (BINARY_CI), the letters with diacritics appear last.

When both case and diacritics are ignored (BINARY_AI), ä is sorted with the other characters whose base letter is a. All the characters whose base letter is a occur before z.

You can use binary collation for better performance when the character set is US7ASCII or another character set that has the same collation order as the binary collation.

The following table shows the collation orders that result from German collation for the table.

A German collation places lowercase letters before uppercase letters, and ä occurs before Z. When the collation ignores both case and diacritics (GERMAN_AI), ä appears with the other characters whose base letter is a.

The examples in this section demonstrate a binary collation, a monolingual collation, and a UCA collation. To prepare for the examples, create and populate a table called test3. Enter the following statements:

SQL> CREATE TABLE test3 (name VARCHAR2(20));
SQL> INSERT INTO test3 VALUES('Diet');
SQL> INSERT INTO test3 VALUES('À voir');
SQL> INSERT INTO test3 VALUES('Freizeit');

SQL> SELECT * FROM test3 ORDER BY name;

Diet
Freizeit
À voir

SQL> SELECT * FROM test3 ORDER BY NLSSORT(name, 'NLS_SORT=german');

À voir
Diet
Freizeit

Example 5-9 Comparing a Monolingual German Collation to a UCA Collation

Insert the character string shown in the following figure into test. It is a D with a crossbar followed by ñ.

Figure 5-2 Example Character String

Description of "Figure 5-2 Example Character String"

Perform a monolingual German collation by using the NLSSORT function with the NLS_SORT parameter set to german.

SELECT * FROM test2 ORDER BY NLSSORT(name, 'NLS_SORT=german');

The output from the German collation shows the new character string last in the list of entries because the characters are not recognized in a German collation.

Perform a UCA collation by entering the following statement:

SELECT * FROM test2
ORDER BY NLSSORT(name, 'NLS_SORT=UCA1210_DUCET');

The output shows the new character string after Diet, following the UCA order.

When the comparison conditions =, !=, >, <, >=, <=, BETWEEN, NOT BETWEEN, IN, NOT IN, the query clauses ORDER BY or GROUP BY, or the aggregate function COUNT(DISTINCT) are evaluated according to linguistic rules, the compared argument values are first transformed to binary values called collation keys and then compared byte by byte, like RAW values.

If a monolingual collation is applied, collation keys contain concatenated major values for characters of the source value followed by concatenated minor values for those characters. If a multilingual collation is applied, collation keys contain concatenated primary, then secondary, and then tertiary values. If a UCA collation is applied, collation keys contain concatenated primary, secondary, tertiary, and possibly quaternary values. The case-insensitive and accent-insensitive multilingual and UCA collations may omit quaternary, tertiary, and secondary values.

The collation keys are the same values that are returned by the NLSSORT function. That is, activating the linguistic behavior of these SQL operations is equivalent to including their arguments into calls to the NLSSORT function.

As collation keys are values of the data type RAW and the maximum length of a RAW value depends on the value of the initialization parameter, MAX_STRING_SIZE, the maximum length of a collation key is controlled by the parameter as well.

When MAX_STRING_SIZE is set to STANDARD, the maximum length of a collation key is restricted to 2000 bytes. If a full source string yields a collation key longer than the maximum length, the collation key generated for this string is calculated for a maximum prefix (initial substring) of the value for which the calculated result does not exceed 2000 bytes.

For monolingual collation, the prefix is typically 1000 characters. For multilingual collation, the prefix is typically 500 characters. For UCA collations, the prefix is typically 300 characters. The exact length of the prefix may be higher or lower and depends on the particular collation and the particular characters contained in the source string. The implication of this method of collation key generation is that SQL operations using the collation keys to implement the linguistic behavior will return results that may ignore trailing parts of long arguments. For example, two strings starting with the same 1000 characters but differing somewhere after the 1000th character will be grouped together by the GROUP BY clause.

When MAX_STRING_SIZE is set to EXTENDED, the maximum length of a collation key is restricted to 32767 bytes. With this setting, collation key generation is switched to precise mode. If a full source string yields a collation key longer than the maximum length, the database raises the ORA-12742 error message instead of generating a truncated key.

In the precise mode, that is, when the initialization parameter MAX_STRING_SIZE is set to EXTENDED, generation of a collation key may fail with ORA-12742 error, if the buffer reserved for the collation key is too small. This can happen in any of the following two cases:

• The length of the generated key is longer than 32767 bytes

• The expansion ratio used to calculate the collation key length from the source string length is too low for a given combination of collation and source string

The first case may happen for long source strings in any linguistic collation because collation keys are mostly longer than the source strings for which they are created. To avoid ORA-12742 error in this case, make sure that lengths of the collated values are never longer than the following limits:

• 21844 bytes for the collation BINARY_CI

• 4094 bytes for a monolingual or multilingual collation

• 1560 bytes for a UCA collation

The second case may happen for strings of any length in all UCA0620 collations and in the collations UCA0700_DUCET, UCA0700_ROOT, UCA1210_DUCET, and UCA1210_ROOT. This case happens because the pessimistic expansion ratio for the listed UCA collations is very high. Using the pessimistic expansion ratio for calculation of the pessimistic collation key length would strongly reduce the maximum length of a linguistically indexable column. Therefore, a lower ratio is used for these collations, which works for all source strings except those containing one or more of the four specific rare compatibility characters - one Japanese, one Korean, and two Arabic. The presence of these specific characters in a string may cause the collation key generation for the string to fail with ORA-12742 error.

The UCA0700 collations other than UCA0700_DUCET and UCA0700_ROOT have been customized to never generate collation keys longer than the chosen expansion ratio. In particular, UCA0700_ORADUCET and UCA0700_ORAROOT collations are almost identical versions of the corresponding UCA0700_DUCET and UCA0700_ROOT collations, in which the collation weights for the four problematic characters have been shortened.

Similarly, the UCA1210 collations other than UCA1210_DUCET and UCA1210_ROOT have been customized to never generate collation keys longer than the chosen expansion ratio. In particular, UCA1210_ORADUCET and UCA1210_ORAROOT collations are almost identical versions of the corresponding UCA1210_DUCET and UCA1210_ROOT collations, in which the collation weights for the four problematic characters have been shortened.

When a character value for which a collation key cannot be generated for a certain collation is inserted into a column, any query comparing or sorting this character value using this collation fails with ORA-12742 error. In certain application configurations, this may cause a denial of service (DoS) attack vulnerability. It is therefore important to follow these guidelines:

• Collate only column values limited in length, not using the problematic UCA collations as described above or

• Dynamically verify that only safe values are inserted into a table or

• Assure that applications are designed in such a way that values entered by one user cannot break queries issued by another user

You can dynamically verify safety of values inserted into a column by creating a CHECK constraint on the column. For example, if you create a table as follows:

CREATE TABLE translation_string
(
   id NUMBER, 
   string VARCHAR2(32767), 
   CONSTRAINT check_string CHECK (VSIZE(NLSSORT(string COLLATE UCA1210_DUCET)) != -1) 
);

then any insert or update of a character value in the string column will trigger the collation key generation in the check constraint condition. Problematic values will cause the DML to fail with ORA-12742 error. However, once successfully inserted or updated, the value will never cause ORA-12742 error in a later query.

The check_string constraint in the above example performs a pessimistic check over all the collations. It may be over-pessimistic for many collations. If you know that one or two specific collations will be used with a column, you can modify the check constraint to force generation of collation keys only for those collations. However, in that case, you have to restrict the collations that can be used in your application.

The following examples illustrate behavior with different NLS_COMP settings.

SQL> ALTER SESSION SET NLS_COMP=BINARY;
SQL> ALTER SESSION SET NLS_SORT=BINARY;
SQL> SELECT ename FROM emp1;

ENAME
----------------------
Mc Calla
MCAfee
McCoye
Mccathye
McCafeé

5 rows selected

SQL> SELECT ename FROM emp1 WHERE ename LIKE 'McC%e';

ENAME
----------------------
McCoye

1 row selected

SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC;
SQL> ALTER SESSION SET NLS_SORT=BINARY_CI;
SQL> SELECT ename FROM emp1 WHERE ename LIKE 'McC%e';

ENAME
----------------------
McCoye
Mccathye

2 rows selected

SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC;
SQL> ALTER SESSION SET NLS_SORT=BINARY_AI;
SQL> SELECT ename FROM emp1 WHERE ename LIKE 'McC%e';

ENAME
----------------------
McCoye
Mccathye
McCafeé

3 rows selected

SQL> ALTER SESSION SET NLS_COMP=BINARY;
SQL> ALTER SESSION SET NLS_SORT=BINARY;
SQL> SELECT DISTINCT ename FROM emp2;

ENAME
----------------------
McAfee
Mcafee
McCoy

3 rows selected

However, with a case-insensitive setting, McAfee and Mcafee are the same:

SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC;
SQL> ALTER SESSION SET NLS_SORT=BINARY_CI;
SQL> SELECT DISTINCT ename FROM emp2;

ENAME
----------------------
McAfee
McCoy

2 rows selected

In this example, either McAfee or Mcafee could be returned from the DISTINCT operation. There is no guarantee exactly which one will be picked.

SQL> ALTER SESSION SET NLS_COMP=BINARY;
SQL> ALTER SESSION SET NLS_SORT=BINARY;
SQL> SELECT ename FROM emp3 WHERE ename LIKE 'C%';

ENAME
----------------------
Cindy
Chad
Clara

3 rows selected

In a database session with the linguistic rule set to traditional Spanish, XSPANISH, ch is treated as one character. So the letter c in Chad is different than the letter C in Cindy and Clara:

SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC;
SQL> ALTER SESSION SET NLS_SORT=XSPANISH;
SQL> SELECT ename FROM emp3 WHERE ename LIKE 'C%';

ENAME
----------------------
Cindy
Clara

2 rows selected

And the letter c in combination ch is different than the c standing by itself:

SQL> SELECT REPLACE ('character', 'c', 't') "Changes" FROM DUAL;

Changes
---------------------
charatter

SQL> ALTER SESSION SET NLS_COMP = LINGUISTIC;
SQL> ALTER SESSION SET NLS_SORT = UCA1210_TSPANISH;
SQL> SELECT ename FROM emp3 WHERE ename LIKE 'C%';

ENAME
--------------
Cindy
Clara

SQL> SELECT REPLACE ('character', 'c', 't') "Changes" FROM DUAL;

Changes
-----------
charatter

The following are requirements for using linguistic indexes:

• Set NLS_SORT Appropriately

• Specify NOT NULL in a WHERE Clause If the Column Was Not Declared NOT NULL

• Use a Tablespace with an Adequate Block Size

This section also includes:

• Example: Setting Up a French Linguistic Index

According to the POSIX standard, a range in a regular expression includes all collation elements between the start point and the end point of the range in the linguistic definition of the current locale. Therefore, ranges in regular expressions are meant to be linguistic ranges, not byte value ranges, because byte value ranges depend on the platform, and the end user should not be expected to know the ordering of the byte values of the characters. The semantics of the range expression must be independent of the character set. This implies that a range such as [a-d] may include all the letters between a and d plus all of those letters with diacritics, plus any special case collation element such as ch in Traditional Spanish that is sorted as one character.

Oracle Database interprets range expressions as specified by the NLS_SORT parameter to determine the collation elements covered by a given range. For example:

Expression:     [a-d]e
NLS_SORT:       BINARY
Does not match: cheremoya
NLS_SORT:       XSPANISH
Matches:        >>che<<remoya

This construct is introduced by the POSIX standard to separate collating elements. A collating element is a unit of collation and is equal to one character in most cases. However, the collation sequence in some languages may define two or more characters as a collating element. The historical regular expression syntax does not allow the user to define ranges involving multicharacter collation elements. For example, there was no way to define a range from a to ch because ch was interpreted as two separate characters.

By using the collating element delimiter [. .], you can separate a multicharacter collation element from other elements. For example, the range from a to ch can be written as [a-[.ch.]]. It can also be used to separate single-character collating elements. If you use [. .] to enclose a multicharacter sequence that is not a defined collating element, then it is considered as a semantic error in the regular expression. For example, [.ab.] is considered invalid if ab is not a defined multicharacter collating element.

In English regular expressions, the range expression can be used to indicate a character class. For example, [a-z] can be used to indicate any lowercase letter. However, in non-English regular expressions, this approach is not accurate unless a is the first lowercase letter and z is the last lowercase letter in the collation sequence of the language.

The POSIX standard introduces a new syntactical element to enable specifying explicit character classes in a portable way. The [: :] syntax denotes the set of characters belonging to a certain character class. The character class definition is based on the character set classification data.

Oracle Database also supports equivalence classes through the [= =] syntax as recommended by the POSIX standard. A base letter and all of the accented versions of the base constitute an equivalence class. For example, the equivalence class [=a=] matches ä as well as â. The current implementation does not support matching of Unicode composed and decomposed forms for performance reasons. For example, ä (a umlaut) does not match 'a followed by umlaut'.

The following examples show regular expression matches.

Expression: catalog(ue)?
NLS_SORT: GENERIC_M_CI
Matches: 

>>Catalog<<
>>catalogue<<
>>CATALOG<<

SQL> ALTER SESSION SET NLS_SORT='GENERIC_M_CI';
SQL> SELECT col FROM test WHERE REGEXP_LIKE(col,'catalog(ue)?');

Expression: catalog(ue)?
NLS_SORT: GENERIC_M_CI
Match option: 'c'
Matches:

>>catalogue<<

Does not match: 

Catalog
CATALOG

SQL> ALTER SESSION SET NLS_SORT='GENERIC_M_CI';
SQL> SELECT col FROM test WHERE REGEXP_LIKE(col,'catalog(ue)?','c');

Expression: [^-a-[.ch.]]+  /*with NLS_SORT set to xspanish*/
Matches: 

>>driver<<

Does not match:

cab

SQL> SELECT col FROM test WHERE REGEXP_LIKE(col,'[^-a-[.ch.]]+');

Expression: [[:lower:]]{6}
Database character set: WE8ISO8859P1
Matches:

>>maître<<
>>mòbile<<
>>pájaro<<
>>zurück<<

SQL> SELECT col FROM test WHERE REGEXP_LIKE(col,'[[:lower:]]{6}');

Expression: r[[=e=]]sum[[=e=]]
Matches:

>>resume<<
>>résumé<<
>>résume<<
>>resumé<<

SQL> SELECT col FROM test WHERE REGEXP_LIKE(col,'r[[=e=]]sum[[=e=]]');

In Oracle Database 12c Release 1 (12.1) and earlier releases, the two session parameters NLS_SORT and NLS_COMP determine the rules by which character type data is compared and matched. The collation specified using these two session parameters is called the session collation. The value of NLS_COMP decides which operations are controlled by the collation specified in the value of NLS_SORT and which operations use the BINARY collation. All collation-sensitive operations selected by the value of NLS_COMP in all SQL and PL/SQL statements executed in the session use the same collation.

Starting with Oracle Database 12c Release 2 (12.2), a new mechanism has been added to apply collations for SQL operations in a much more granular way. A collation is an attribute of data, similar to the data type. A collation can be declared for a character data container, such as table column, and is passed to all SQL operations that operate on that column. Each collation-sensitive operation combines declared collations of its arguments to determine the collation to use for the operation processing. Furthermore, an operation that returns a character value combines collations of its arguments to derive a collation for the result. The operator COLLATE allows overriding a collation in any place in an expression.

This type of collation, which is associated with a particular data, is called the data-bound collation . A data-bound collation can be applied only to the values of character data types — VARCHAR2, CHAR, LONG, NVARCHAR2, NCHAR, CLOB, and NCLOB.

There are two types of data-bound collations:

• Named Collation: This collation is a particular set of collating rules specified by a collation name. Named collations are the same collations that are specified as values for the NLS_SORT parameter. A named collation can be either a binary collation or a linguistic collation.

  • Examples of binary named collation are: BINARY, BINARY_CI (case-insensitive binary collation), and BINARY_AI (accent-insensitive and case-insensitive binary collation).

  • Examples of linguistic named collation are: GENERIC_M, GENERIC_M_AI, FRENCH, POLISH, UCA1210_CFRENCH, and so on.

• Pseudo-collation: This collation does not directly specify the collating rules for a character data type. Instead, it instructs collation-sensitive operations to check the values of the NLS_SORT and NLS_COMP session parameters for the actual named collation to use. Pseudo-collations are the bridge between the new declarative method of specifying collations and the old method that uses session parameters.

  The following are the supported pseudo-collations:

  • USING_NLS_COMP: Operations that use the USING_NLS_COMP pseudo-collation behave the same as in Oracle Database 12c (12.1) and earlier releases, that is, they use the session collation. The particular named collation applied by the SQL or PL/SQL operation is either BINARY or determined by the value of NLS_SORT, NLS_COMP, and the operation itself.

  • USING_NLS_SORT, USING_NLS_SORT_CI, USING_NLS_SORT_AI, and USING_NLS_SORT_CS: If one of these collations is determined as the collation to use for an operation, the operation applies the collation named by the value of NLS_SORT parameter without considering the value of NLS_COMP parameter. Additionally:

    • If the pseudo-collation is USING_NLS_SORT_CI and the value of NLS_SORT does not end in _CI or _AI, then the name of collation to apply is constructed by appending _CI to the value of NLS_SORT.

    • If the pseudo-collation is USING_NLS_SORT_AI and the value of NLS_SORT does not end in _CI or _AI, then the name of collation to apply is constructed by appending _AI to the value of NLS_SORT. If the value of NLS_SORT ends in _CI, then the suffix _CI is changed to _AI.

    • If the pseudo-collation is USING_NLS_SORT_CS and the value of NLS_SORT ends in _CI or _AI, then the name of collation to apply is constructed by stripping this suffix from the NLS_SORT value.

    • Otherwise, the name of collation to apply is the value of NLS_SORT.

  Note:

  • Suffix _CI stands for case insensitivity. Suffix _AI stands for case and accent insensitivity. Suffix _CS stands for case and accent sensitivity.

  • The pseudo-collation USING_NLS_SORT_CI forces the use of the case-insensitive version of the collation specified in the NLS_SORT parameter value.

  • The pseudo-collation USING_NLS_SORT_AI forces the use of the case-insensitive and accent-insensitive version of the collation specified in the NLS_SORT parameter value.

  • The pseudo-collation USING_NLS_SORT_CS forces the use of the case-sensitive and accent-sensitive version of the collation specified in the NLS_SORT parameter value.

  • The only collation supported by CLOB and NCLOB columns is the pseudo-collation USING_NLS_COMP.

Starting with Oracle Database 12c Release 2 (12.2), each table column with a character data type has a declared data-bound collation. If collation for a column is not specified explicitly in the DDL statement that creates the column (in the CREATE TABLE or ALTER TABLE ADD statement), then the containing table’s default collation is used for the column. If the DDL statement creating a table does not specify a default collation, then the default collation of the schema owning the table is used as the default collation for the table. Specify default collation for a schema in the CREATE USER statement that creates the owner of the schema. If the CREATE USER statement does not specify the default collation for a schema, then the collation USING_NLS_COMP is used.

Collations are inherited only when database objects are created. For example, changing the table default collation does not change the collations of existing character columns of a table. Only new columns added to the table after the change inherit the new default collation. Similarly, changing the schema default collation does not change the default collations of tables in a schema. Only new tables created in the schema after the change inherit the new default collation.

The session parameter DEFAULT_COLLATION overrides the schema default collation as described in the section "Effective Schema Default Collation".

To enable the data-bound collation feature, set the following database initialization parameter values:

• MAX_STRING_SIZE=EXTENDED

• COMPATIBLE>=12.2

A data-bound collation can be specified for:

• Table columns

• Cluster columns

• Tables

• Schemas through the owning user

• Views and materialized views

• PL/SQL units, such as procedures, functions, packages, types, and triggers

• SQL expressions

You can view the data-bound collation information for a database object or a column using the following data dictionary views:

Data dictionary views for viewing the default collation of an object

DBA|USER_USERS.DEFAULT_COLLATION

DBA|ALL|USER_TABLES.DEFAULT_COLLATION

DBA|ALL|USER_VIEWS.DEFAULT_COLLATION

DBA|ALL|USER_MVIEWS.DEFAULT_COLLATION

DBA|ALL|USER_OBJECTS.DEFAULT_COLLATION

Data dictionary views for viewing the collation of a table, a view, or a cluster column

{DBA|ALL|USER}_TAB_COLS.COLLATION

{DBA|ALL|USER}_TAB_COLUMNS.COLLATION

Data dictionary views to view the collation association between a virtual column and an original column

The data dictionary views contain the following columns that show the collation association between a virtual column and an original columns whose linguistic behavior the virtual column implements:

{DBA|ALL|USER}_TAB_COLS.COLLATED_COLUMN_ID

{DBA|ALL|USER}_PART_KEY_COLUMNS.COLLATED_COLUMN_ID

{DBA|ALL|USER}_SUBPART_KEY_COLUMNS.COLLATED_COLUMN_ID

Oracle Database supports case-insensitive collations, such as BINARY_CI, BINARY_AI, GENERIC_M_CI, GENERIC_M_AI, UCA1210_DUCET_CI, and UCA1210_DUCET_AI. By applying such collations to SQL operations, an application can perform string comparisons and matching in a case-insensitive way.

Starting with Oracle Database 12c Release 2 (12.2), you can declare a column to be always compared as case-insensitive by specifying a case-insensitive data-bound collation (collation having suffix _CI or _AI) in the column definition. The column collation, if not specified explicitly, is inherited from the table default collation, which in turn is inherited from the schema default collation. This way, you can easily declare all the character columns in a database as case-insensitive by default, and use explicit collation declarations only for columns that require a case-sensitive collation.

This section describes the affect on the following database objects, when they reference a column implementing a data-bound collation:

• Persistent Objects

• Standard Indexes

• Bitmap Join Indexes

• Primary and Unique Constraints

• Foreign Key Constraints

• Partitioning and Sharding

• Index-organized Tables (IOTs)

• Clusters

• Table Clustering and Zone Maps

• Oracle Text Indexes and Other Domain Indexes

• Other Specific Table Types

CREATE TABLE my_table
( 
  my_column VARCHAR2(100) COLLATE POLISH,
  ...
);

CREATE [UNIQUE|BITMAP] INDEX my_index ON my_table(my_column);

CREATE TABLE my_table
( 
  my_column VARCHAR2(100) COLLATE POLISH,
  ...
);

CREATE [UNIQUE|BITMAP] INDEX my_index ON my_table(NLSSORT(my_column,'NLS_SORT=POLISH'));

CREATE TABLE my_table
( 
  id          VARCHAR2(20)  COLLATE USING_NLS_COMP,
  my_column   VARCHAR2(100) COLLATE POLISH,
  ...
);

CREATE [UNIQUE|BITMAP] INDEX my_index ON my_table(id, my_column);

CREATE TABLE my_table
( 
  id         VARCHAR2(20)   COLLATE USING_NLS_COMP,
  my_column  VARCHAR2(100)  COLLATE POLISH,
  ...
);

CREATE [UNIQUE|BITMAP] INDEX my_index ON my_table(id, NLSSORT(my_column,'NLS_SORT=POLISH'));

Distributed queries and DML operations may involve one or more database nodes of different Oracle Database releases, such as, 12.2, 12.1, and earlier. Evaluation of different parts of a query may happen in different nodes and determination of particular nodes evaluating particular operators is subject to optimizer decisions. Moreover, a local node is generally aware only of nodes that it directly accesses through database links. Indirect or multi-hop nodes, which are remote nodes accessed through synonyms referenced in the query and defined in directly connected nodes, are not visible to a local node.

Considering the above scenario and the requirement that query results must be deterministic and cannot depend on optimizer decisions, Oracle defines the following behavior for queries and subqueries:

• If an Oracle Database 12.2 node with the data-bound collation feature enabled connects to another Oracle Database 12.2 node with the data-bound collation feature enabled, all data-bound collation related behavior is supported.

• If an Oracle Database 12.1 node or an earlier Oracle Database release node connects to an Oracle Database 12.2 node, the Oracle Database 12.2 node recognizes that the query is coming from an earlier Oracle Database release. If such a query references columns with a declared collation other than USING_NLS_COMP, an error is reported. However, if the remote Oracle Database 12.2 node receives a DML statement, the statement is evaluated, even if it references columns with a declared collation other than USING_NLS_COMP.

• If a local Oracle Database 12.2 node connects to a remote database node of earlier Oracle Database release, the local database node assumes that any character data coming from the remote database node has the declared collation of USING_NLS_COMP. The local database node makes sure that the new SQL operators, such as COLLATE and NLS_COLLATION_NAME, are not sent to the remote database node. If an SQL statement has to be executed on the remote node (for example, a DML operation on a remote table), and if it contains the new SQL operators or a reference to a local column with collation other than USING_NLS_COMP, then an error is reported.

Oracle Database provides limited data-bound collation support for PL/SQL types and user-defined types (UDTs). Only those features are provided in Oracle Database that are needed to maintain forward compatibility of PL/SQL code, with the possible future extension of data-bound collation architecture to PL/SQL without limiting the use of PL/SQL with database objects that use the data-bound collation feature.

The following features related to PL/SQL units and UDTs are provided in Oracle Database for data-bound collation support:

• A PL/SQL procedure, function, package, trigger, or UDT can be created as a valid object, only if the effective schema default collation at the time of its creation is USING_NLS_COMP, or its definition contains an explicit DEFAULT COLLATION USING_NLS_COMP clause. If the resulting default object collation is different from USING_NLS_COMP, the database object is created as invalid with a compilation error.

  See Also:

  "Effective Schema Default Collation"

• The new SQL operators COLLATE, COLLATION, NLS_COLLATION_ID, and NLS_COLLATION_NAME used in embedded SQL are accepted and passed to the SQL engine, but their functionality is not available in PL/SQL code.

• The database columns with declared collations other than USING_NLS_COMP can be referenced in embedded SQL, but not in PL/SQL expressions.

• The DML row-level triggers cannot reference fields of OLD, NEW, or PARENT pseudo-records, or correlation names that correspond to columns with declared collation other than USING_NLS_COMP.

• The PL/SQL variables referenced in embedded SQL statements have the pseudo-collation USING_NLS_COMP and the coercibility level 2.

• The %TYPE attribute is not allowed on character columns with a declared collation other than the pseudo-collation USING_NLS_COMP. Similarly, the %ROWTYPE attribute is not allowed on tables, views, cursors, or cursor variables with at least one character column with a declared collation other than USING_NLS_COMP. The columns with collations other than USING_NLS_COMP can be selected into INTO clause variables declared without those attributes. PL/SQL variables always have the default collation USING_NLS_COMP. Thus, whatever is the collation of the selected columns, it is always overridden with the pseudo-collation USING_NLS_COMP for PL/SQL processing.

• The cursor FOR LOOP statements are not allowed on cursors that return result set columns with collation other than the pseudo-collation USING_NLS_COMP.

• A relational column created to store an UDT attribute, whether of an object column or of an object table, inherits the attribute’s collation property. However, as all UDTs are created using the pseudo-collation USING_NLS_COMP, any relevant columns for UDT attributes are also created with the pseudo-collation USING_NLS_COMP.

• A WHEN condition in a trigger is evaluated by the SQL engine, and hence, it supports the data-bound collation feature. A WHEN condition can reference a column with declared collation other than USING_NLS_COMP, and can use the new operators and functions.

The XML Query standard XQuery defines features to specify collation for collation-sensitive operators in XML Query expressions. An XQuery collation can be specified for a particular operator, similar to how collation is specified in the second parameter of the Oracle SQL function NLS_UPPER, or as a default collation in the static context of an XQuery expression. XQuery does not provide any mechanism to declare collation for a data container or data source. Therefore, the declared collations of any relational database columns passed as arguments in the PASSING clause of the XMLQuery , XMLExists, or XMLTable operator are ignored by Oracle XML DB.