Tuesday, February 19, 2008

ORA-12731: invalid collation class in regular expression

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

This error can be seen when working with regular expressions and character lists containing collation classes.
ORA-12731: invalid collation class in regular expression
For a live simulation of this error, see Yocoya's Regular Expression Workbench.

In order to understand how to fix the problem in a generic situation, let me show you first how to fix it in the workbench, where you have to change the NLS_SORT option, displayed in the sidebar, to the value XSPANISH (extended Spanish).

Acceptable characters within a collation class are determined by the value of initialization parameter NLS_SORT, that can be altered at the session level with:
ALTER SESSION SET NLS_SORT=XSPANISH;
For an in-depth discussion of linguistic sorting, see the relevant chapter of the Database Globalization Support Guide (10g).

So, when you switch the linguistic option in the workbench picking a value from the drop down list, you are causing the program to alter the current session value of NLS_SORT.

However, if after changing NLS_SORT you are still experiencing the same the problem, it could be that you have specified an invalid multicharacter element, in which case you must determine what characters are allowed by Oracle for the desired linguistic sort option.
For instance, in extended Spanish only "ch" and "ll" are allowed as multicharacter elements in collation classes.

But what is the purpose of multicharacter collation elements anyway?

As you can see in workbench live example, i defined a character range within a list, where all characters between a and ch will be replaced by an asterisk. Being considered a single character, ch will be replaced by a single asterisk, not two.
The collation element allows you to define a range of characters according to linguistic rules, where the starting or ending element can be a multicharacter element.

See message translations for ORA-12731 and search additional resources

No comments:

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles