Sometimes we need to sort a list by rules of a national letter order, e.g. in Polish the national charactes are mostly place between original the Latin ones: … b, c, ć, d, e, ę, f … In other languages, it can be even more sophisticated, like in Spanish, where ll is located after lz, so a single character replacement wouldn’t work.
If we sort the following way:
SELECT * FROM TABLE (SYS.ODCIVARCHAR2LIST(‘cde’, ‘ća’, ‘dx’, ‘ca’)) ORDER BY COLUMN_VALUE;
We’ll get: ca, cde, dx, ća and that’s wrong. To sort the list correctly, we can specify a language rule: NLSSORT(COLUMN_VALUE,’NLS_LANG=pl’):
SELECT * FROM TABLE (SYS.ODCIVARCHAR2LIST(‘cde’, ‘ća’, ‘dx’, ‘ca’)) RDER BY NLSSORT(COLUMN_VALUE,’NLS_LANG=pl’);
Finally we’ll get the correct result: ca, cde, ća, dx.
