Performing Linguistic Sorting as a Customization

Some users may require linguistic sorting in limited contexts, such as French sorting for a particular table or report. As shipped, PeopleTools supports binary sorting only. You can implement linguistic sorting but you may need to do index tuning and testing, and recheck these after any upgrades.

For Oracle databases only, when selecting a linguistic sort, specify the sort order by setting the NLS_SORT and NLS_LANGUAGE environment variables, and update the Sorting Option in PeopleSoft Options.

For example, to enable French linguistic sorting, set the environment variables to the following:

NLS_SORT=FRENCH
NLS_LANGUAGE=FRENCH

On PeopleSoft Options Page, change Sorting value from Binary to French.

For certain queries, you may need to create a linguistic index to avoid performance penalties as a result of using the linguistic sort.

The following is an example for setting French Linguistic Index:

ALTER SESSION SET NLS_SORT='FRENCH';
CREATE INDEX test_idx ON test4(NLSSORT(name, 'NLS_SORT=FRENCH'));
SELECT * FROM test4 ORDER BY col;
ALTER SESSION SET NLS_COMP=LINGUISTIC;
SELECT * FROM test4 WHERE name > 'Henri';

Please consult your Oracle database documentation for more information on linguistic indexes.